Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Adding Fields to the file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Adding Fields to the file
# 8  
Old 12-18-2012
What also should be taken into account is the last field on line 3 that contains a quoted field with a pipe-symbol inside that is not a field separator:

Code:
awk '{for(i=1;i<=NF;i+=2)gsub(/\|/,"§",$i)}1' FS=\" OFS=\" infile |
awk '$2==""{$2="ND"} $3==""{$3="ND"}{for(i=1; i<=20; i++) if($i=="")$i=" "}1' FS=§ OFS=\|

produces:
Code:
0000000000|ND|3|MAIL ORDER|MAIL ORDER DATA|N/A|MAIL ORDER|N/A| | | | | | | | | | | | 
0000010001|CASH|ND|CASH|CASH|N/A|CASH|N/A| | | | | | | | | | | | 
0000020002|FFS MED|2|AL MEDICAID|MEDICAID|N/A|AL MEDICAID|N/A|"Tier2,PA|NPF"| | | | | | | | | | |

Last edited by Scrutinizer; 12-19-2012 at 03:28 AM..
These 2 Users Gave Thanks to Scrutinizer For This Post:
Don Cragun Scott
# 9  
Old 12-18-2012
So this is a CSV file (except that it's using the pipe character as delimiters instead of commas). Whatever is used to process this file should have the ability to parse it. Therefore you should do it inside that tool. For example, if you load this file to Oracle table using sqlldr, it's very easy to create a load control file to populate the "ND" and " " values using NVLs.
gawk 4.0 has the ability to parse CSV files:

Code:
gawk4 '
BEGIN { 
  OFS = "|" 
  FPAT = "([^|]*)|(\"[^\"]+\")"
}
{
  for (i = 1; i < 20; ++i)
    if ($i == "") $i = (i < 9)? "ND" : " "
  if (NF < 20) $20 = ""
  print
}'

Last edited by binlib; 12-18-2012 at 11:08 PM..
# 10  
Old 12-19-2012
Quote:
Originally Posted by Don Cragun
I like concept of pamu's solution also, but it has a few problems:
  1. If there is an empty line or a line with only one field, fields 2 and 3 will be set to a space; not "ND".
  2. If there is a line with three fields in the input file, field 3 will be set to a space no matter what it originally contained.
  3. If a line has four to twenty fields (inclusive), the contents of the last field present will be replaced by a space.
I think the following does what was requested:
Code:
awk 'BEGIN{FS = OFS = "|"}
{       if(NF == 0) $1 = " "
        $2 = $2 ? $2 : "ND"
        $3 = $3 ? $3 : "ND"
        for(i = NF+1; i <= 20; i++) $i = " "
        print
}' file

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

Last edited by pamu; 12-19-2012 at 03:39 AM.. Reason: corrected...
# 11  
Old 12-19-2012
Thanks @Don Cragun, Scott Pamu and all who have replied to this thread. Just wanted to know from Pamu will the final awk statement provided by you solves the problem what ever was figured out by @Don Cragun?
# 12  
Old 12-19-2012
Quote:
Originally Posted by Arun Mishra
Thanks @Don Cragun, Scott Pamu and all who have replied to this thread. Just wanted to know from Pamu will the final awk statement provided by you solves the problem what ever was figured out by @Don Cragun?
Hi Arun,

I also Like Don's solution.Smilie

Yes all the problems were found out by Don was resolved in my latest version.
But the query raised by Scrutinizer, if you have "|" inside double quotes and you don't want to consider this as FS. then you should go for Scrutinizer's solution from post 8.

pamu
# 13  
Old 12-19-2012
Thanks Pamu but my original CSV file was:
HTML Code:
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"
where i had comma inside the qoutes. Then i converted it to pipeline separated file using:
Code:
awk -F '"' 'NF>1{ for(i=1;i<=NF;i+=2){gsub(",","|",$i)}} NF==1{gsub(",","|")}1' OFS="" ${Inpt_File} > temp_file.txt

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

Outputs:
HTML Code:
plan_dk,imsmodelvar,paymt_type_az,mmars_plan,mmars_payer,mmars_pbm,account,botpbm,formulary_Crestor|ND|ND| | | | | | | | | | | | | | | | |
0000000000,,3,MAIL ORDER,MAIL ORDER DATA,N/A,MAIL ORDER,N/A,|ND|ND| | | | | | | | | | | | | | | | |
0000010001,CASH,1,CASH,CASH,N/A,CASH,N/A,|ND|ND| | | | | | | | | | | | | | | | |
ND should be in 2nd or 3rd column instead it is populated smwhere else.

Can you specify how to do it? And Please if you can remove headers along with this.
# 14  
Old 12-19-2012
Hi Arun,

I think you are trying out one sollution for another problem..


Is this what you want..?

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"

$ awk 'NR%2{gsub(",","|")}1' RS='"' ORS= 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| | | | | | | | | | |
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