Adding Fields to the file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Adding Fields to the file
# 15  
Old 12-19-2012
Quote:
Originally Posted by pamu
Hi Don,

I agree about NF+1 that was my mistake.. My bad..

But for other two i just need to shift my code...Smilie and for getting desired output just need to add one more condition.. Smilie

Code:
awk -F\| '{$1=$1?$1:" ";$2=$2?$2:"ND";$3=$3?$3:"ND"; for(i=(NF+1);i<=20;i++){$i=" "}}1' OFS="|" file

Note that there is a difference between a field not being present and a field being present but empty. With an empty line as input, both of our scripts will produce:
Code:
 |ND|ND| | | | | | | | | | | | | | | | |

But with the input line:
Code:
|||

(four empty fields separated by three vertical bars) your script will produce:
Code:
 |ND|ND|| | | | | | | | | | | | | | | |

while my script will produce:
Code:
|ND|ND|| | | | | | | | | | | | | | | |

with the difference being the leading space produced by your script.
Code:
if(NF == 0) $1 = " "

in my script creates $1 as a field containing a space if there was no 1st field while
Code:
$1=$1?$1:" "

in your script changes an existing empty $1 to a space. Note that both of our scripts kept the existing empty $4 as an empty field.
# 16  
Old 12-19-2012
Thanks for the reply. I was actually looking for one solution s u said for both the issues. But your code is not working as when i use RS and ORS i get error for some columns as specified below:
Code:
awk: Input line
0000030075,IPA,4,AE cannot be longer than 3,000 bytes.
 The input line number is 44. The file is DCIA_C150_R01_PLN_SEG_DATA.TXT.
 The source line number is 1.

It seems i am using HP-UNIX that's why i cannot use the above code.
Do you have any alternative way so i could have one solution for both the issues.

---------- Post updated at 04:06 PM ---------- Previous update was at 03:50 PM ----------

Quote:
Originally Posted by Arun Mishra
Hi All,

I get a file on weekly basis from client. I need to write a script which make sure the file should have 20 columns after the first column. If not then the script should add the remaining columns and default them to space(except for 2nd and 3rd). and at the same time the script should check the second and the third column if null then default those to ND.
There is an addition to the requirement as Don Mentioned. The 4th field or any other existing field(apart from 2nd and 3rd which if null should be defaulted to ND) if null should also be defaulted to space along with the additional fields being added

Help as always appreciated. Smilie
# 17  
Old 12-19-2012
Try

Code:
$ cat file
plan_dk,imsmodelvar,paymt_type_az,mmars_plan,mmars_payer,mmars_pbm,account,botpbm,formulary_Crestor
0000000000,,3,MAIL ORDER,MAIL ORDER DATA,N/A,MAIL ORDER,N/A,
0000010001,CASH,1,CASH,CASH,N/A,CASH,N/A,
0000020002,FFS MED,2,AL MEDICAID,MEDICAID,N/A,AL MEDICAID,N/A,"Tier2,PA,NPF"

Code:
$ awk -F '"' 'NF>1{ for(i=1;i<=NF;i+=2){gsub(",","|",$i)}} NF==1{gsub(",","|")}1' OFS="" file| awk -F\| '$1==""{$1=" "} $2==""{$2="ND"} $3==""{$3="ND"} {if(NR>1){p=1} {for(i=(NF+1);i<=20;i++){$i=" "}}}p' OFS="|"
0000000000|ND|3|MAIL ORDER|MAIL ORDER DATA|N/A|MAIL ORDER|N/A|| | | | | | | | | | |
0000010001|CASH|1|CASH|CASH|N/A|CASH|N/A|| | | | | | | | | | |
0000020002|FFS MED|2|AL MEDICAID|MEDICAID|N/A|AL MEDICAID|N/A|Tier2,PA,NPF| | | | | | | | | | |

Hi Don,

As Per OP's Requirement it should have space.

Quote:
Originally Posted by Arun Mishra
I need to write a script which make sure the file should have 20 columns after the first column. If not then the script should add the remaining columns and default them to space(except for 2nd and 3rd).
# 18  
Old 12-19-2012
Thanks Pamu , that was awesome but there is a small change in the requirement i do not want any null field to be present. If there is an existing null field the script should default it to space along with the additional fields being added.
# 19  
Old 12-19-2012
Quote:
Originally Posted by Arun Mishra
i do not want any null field to be present. If there is an existing null field the script should default it to space along with the additional fields being added.

Try

Code:
awk -F '"' 'NF>1{ for(i=1;i<=NF;i+=2){gsub(",","|",$i)}} NF==1{gsub(",","|")}1' OFS="" file| awk -F\| '$1==""{$1=" "} $2==""{$2="ND"} $3==""{$3="ND"} {if(NR>1){p=1} {for(i=1;i<=20;i++){if($i==""){$i=" "}}}}p' OFS="|"

This User Gave Thanks to pamu For This Post:
# 20  
Old 12-19-2012
Quote:
Originally Posted by pamu
Try

Code:
awk -F '"' 'NF>1{ for(i=1;i<=NF;i+=2){gsub(",","|",$i)}} NF==1{gsub(",","|")}1' OFS="" file| awk -F\| '$1==""{$1=" "} $2==""{$2="ND"} $3==""{$3="ND"} {if(NR>1){p=1} {for(i=1;i<=20;i++){if($i==""){$i=" "}}}}p' OFS="|"

Hi pamu,
I believe this meets all of Arun's updated requirements.

Could you explain why you thought you needed the NF>1 and the NF==1 tests in the first invocation of awk?

I'm not a big fan of 1-liners when it makes the code hard to read. I much prefer:
Code:
awk -F '"' '
NR > 1{	for(i = 1; i <= NF; i += 2) gsub(/,/, "|", $i)
	print
}' OFS="" file | awk 'BEGIN{FS = OFS = "|"}
{	for(i = 1; i <= 20; i++)
		$i = $i != "" ? $i : $i = (i == 2 || i == 3) ? "ND" : " "
	print
}'

which is still slightly shorter than your script and produces the same results.

If I strip it down to a 1-liner, it is about 30% shorter, but it is much harder for me to read and understand:
Code:
awk -F '"' 'NR>1{for(i=1;i<=NF;i+=2)gsub(/,/,"|",$i)}NR>1' OFS="" file|awk 'BEGIN{FS=OFS="|"}{for(i=1;i<=20;i++)$i=$i!=""?$i:$i=(i>1&&i<4)?"ND":" "}1''

but it still produces the same output.
This User Gave Thanks to Don Cragun For This Post:
# 21  
Old 12-20-2012
Quote:
Originally Posted by Don Cragun
Could you explain why you thought you needed the NF>1 and the NF==1 tests in the first invocation of awk?
Hi Don,

I got one problem while answering to this thread . So i came with NF>1 and NF==1. After this i have not tested this again and just copy pasted the code.
Now from your code it looks like no need of this conditions.. My bad..

Quote:
Originally Posted by Don Cragun
Code:
awk -F '"' '
NR > 1{    for(i = 1; i <= NF; i += 2) gsub(/,/, "|", $i)
    print
}' OFS="" file | awk 'BEGIN{FS = OFS = "|"}
{    for(i = 1; i <= 20; i++)
        $i = $i != "" ? $i : $i = (i == 2 || i == 3) ? "ND" : " "
    print
}'

Thanks for above code.Smilie
That's brilliant piece of coding..(Specially highlighted part in red)

Thanks...Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Adding to an array in an external file, and adding elements to it.

I have an array in an external file, "array.txt", which contains: char *testarray={"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"};I want to be able to add an element to this array, and have that element display, whenever I call it, without having to recompile... (29 Replies)
Discussion started by: ignatius
29 Replies

2. Shell Programming and Scripting

Adding ' in particular fields

I have a file with 4 columns a|b|c|d I need to add single quotes around field 2 and 3 I need it to be like a|'b'|'c'|d (5 Replies)
Discussion started by: dsravanam
5 Replies

3. Shell Programming and Scripting

Awk: adding fields after matching $1

Dear AWK-experts! I did get stuck in the task of combining files after matching fields, so I'm still awkward with learning AWK. There are 2 files: one containing 3 columns with ID, coding status, and score for long noncoding RNAs: file1 (1.txt) (>5000 lines) ... (12 Replies)
Discussion started by: kben
12 Replies

4. Shell Programming and Scripting

Adding fields to a file

Hi All, I have a file(Pipe Delimited) where i need to add a blank field before the last field and a blank field after the last field. Please help. I have provided below the sample input records and desired output. Sample Input: A0010000|Abilene TX A 1|A0010957|Dallas TX|A0010998|West|US... (5 Replies)
Discussion started by: Arun Mishra
5 Replies

5. Shell Programming and Scripting

Adding fields to file

Hi All, I have a file(Pipe Delimited) where i need to add a blank field before the last field and a blank field after the last field. Please help. I have provided below the sample input records and desired output. Code: Sample Input: A0010000|Abilene TX A 1|A0010957|Dallas... (0 Replies)
Discussion started by: Arun Mishra
0 Replies

6. Homework & Coursework Questions

regarding adding fields to DSR protocol in ns2.34

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: want to add field to route cache and packet of DSR routing protocol in ns2.34, add field, DSR package in ns2.34... (2 Replies)
Discussion started by: khubalkar
2 Replies

7. Programming

regarding adding fields to DSR protocol in ns2.34

hi i am student doing project in ns2.34. i hav to add field in route cache and packet of DSR routing protocol. which files hv to be changed...pl help me (1 Reply)
Discussion started by: khubalkar
1 Replies

8. Shell Programming and Scripting

Adding new lines to a file + adding suffix to a pattern

I need some help with adding lines to file and substitute a pattern. Ok I have a file: #cat names.txt name: John Doe stationed: 1 name: Michael Sweets stationed: 41 . . . And would like to change it to: name: John Doe employed permanently stationed: 1-office (7 Replies)
Discussion started by: hemo21
7 Replies

9. Shell Programming and Scripting

Adding field to file and moving the last 2 fields

I have a file with 32 fields each separated by ‘|”. I need to add a file date exactly in the format “ "20100120" “ as the 32nd field moving the existing 32nd field to 33. so the field I added should be 32nd and the 33rd field is the last field before I added the file date. I know we can... (8 Replies)
Discussion started by: dsravan
8 Replies

10. Shell Programming and Scripting

Adding new fields to an existing layout

Hi Everybody, I have an layout file like below f1 1 char 10, f2 11 char 2, f3 13 char 1, lineend 14 char 1 Their I need to add a new field which would be like f5 char 3, f6 char 2 The o/p should be f1 1 char 10, f2 11 char 2, f3 13 char 1, f5 14 char 3, f6 17 char 2 (3 Replies)
Discussion started by: mr_manii
3 Replies
Login or Register to Ask a Question