awk...sed problem

Shell Programming and Scripting
1

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!!

2

Try this

#!/usr/bin/ksh

tmp=file.$$

cat <<EOT >$tmp
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,
EOT

awk -F',' '{ printf "%s,%s,%s,%s%s,%s,%s,%s,%s\n", $1, $2, $4, $5, $6, $7, $8, $9, $10}'  $tmp > outfile

cat outfile

rm $tmp

exit 0