awk...sed problem
Hi guys,
I am tryingto delete or ignore columns in a large dataset using 'sed'. For example, I have the following dataset: -
20060714,X.XX,1,043004,Q,T,24.0000,1,25.5000,4,
20060714,X.XX,1,081209,Q,T,24.0000,1,25.5000,5,
As you can see, there are 10 columns here and the table that I am inserting into has 8 columns.
I want to delete the 3rd column (i.e. the 1's) and I want to delete the comma between Q and T. Finally I want to delete the comma at the end so the dataset looks as follows: -
20060714,X.XX,043004,QT,24.0000,1,25.5000,4
20060714,X.XX,081209,QT,24.0000,1,25.5000,5
I got a suggestion from another user to run the following command:
awk -F, '{ OFS=","; $3=""; print }' FILENAME | sed -e 's/,,//g' -e 's/,//3' -e s/.$//
However, the result i get is:
20060714,X.XX043004,QT,24.0000,1,25.5000,4
20060714,X.XX081209,QT,24.0000,1,25.5000,5
Its almost there but this time it has deleted the comma between X.XX & 043004.
Can any1 here help rectify this problem? Also, id like the output to go into another file....
Many thanks for your time!!