Shell Programming and Scripting

Hi,

I need to make some changes in a csv file using awk or perl. Unfortunately, all my attempts have led to nothing so I hope you guys can help me.

I have the following example input file including header(original file has 35 fields):

Code:

ABC: DE
Time: 2012/09/07
,"Notional","Name","POS","TEST;Field1","TEST;Field2"
"TestField1",0.0000 XYZ,NameField,PosField,"0.00 XYZ","0.00 XYZ"
"TestField2",0.0000 XYZ,NameField,PosField,"0.00 XYZ","0.00 XYZ"

The first column is called "PosId", but the name is missing in the header.

I need to format this to have the following output:

Code:

"Date","PosId","Name","POS","ScenSet","TEST_Field1","TEST_Field2"
2012/09/07,TestField1,NameField,PosField,DE,0.00 XYZ,0.00 XYZ
2012/09/07,TestField2,NameField,PosField,DE,0.00 XYZ,0.00 XYZ

So basically, I need to do the following:
- Take the second field of the first line (DE) and put it in the 5th column (ScenSet)
- Take the second field of the second line (2012/09/07) and put it in the first column (Date)
- Remove the Notional field
- Replace ";" by "_" in the header for the fields (TEST;Fieldx)

Thanks

did it in hurry u can cut corners in this command to make it look small

Code:

 
awk -F"[,:]" 'NR==1{var1=$2};NR==2{var2=$2};NR==3{gsub(/;/,"_");$1="\"Date\"";$2="\"PosId\"";OFS=",";print};NR>3{$5=var1;gsub(/"/,"");OFS=",";print var2","$0}' filename

Thanks for the solution vidyadhar85, it works great in this case.
You use both ",:" as a field separator, but what if the ":" character is in any of the 3rd-end lines? They get an extra field.

Is it possible to tell awk to use a specific separator for the first 2 fields, then change the separator to an other character?

EDIT: Nvm, found it. Just use FS="" inline instead of -F""

There is a problem with the code. The first 5 columns get overwritten with the new columns. The new columns need to be inserted before the original columns.

Do you have any idea of how to do this?