csvawksedcsvkit

How to insert a column at a certain position in a CSV file using AWK and dealing with quotes?

msg_type,mmsi,timestamp,imo,name,ship_and_cargo_type,length,width,draught,eta_date,destination

24,510041000,2016-07-05 12:49:16 UTC,,,30,29,6,,,

5,371952000,2016-07-16 07:30:40 UTC,9687112,SPRING

LEGEND,90,190,32,11.7,2016-08-08 00:00:00 UTC,"ONAHAMA,JAPAN"

5,412331087,2016-07-24 11:14:02 UTC,0,LU HUANG YUAN YU

117,30,0,0,0,,"" 5,775994600,2016-07-02 07:43:55 UTC,9318814,ELIZABETH

A MCCALL,60,44,9,3.5,2016-11-16 06:05:00 UTC,GUIRIA

I'm trying to insert an empty column in this table in the penultimate field in this table. So for example the header would look like this:

msg_type,mmsi,timestamp,imo,name,ship_and_cargo_type,length,width,draught,eta_date,,destination

I am using a AWK command, but it is not dealing correctly with quotes such as for "ONAHAMA,JAPAN".

Is there a better approach, how can I overcome this? Here is my attempt.

Thanks

awk -F, -v OFS="," '{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,","$11}' old_table > new_table
Solution

  • This particular case can be solved with sed, but check out perl, python etc which have csv modules

    $ sed -E 's/"[^"]+"$|[^,]*$/,&/' ip.txt
msg_type,mmsi,timestamp,imo,name,ship_and_cargo_type,length,width,draught,eta_date,,destination
24,510041000,2016-07-05 12:49:16 UTC,,,30,29,6,,,,
5,371952000,2016-07-16 07:30:40 UTC,9687112,,SPRING
LEGEND,90,190,32,11.7,2016-08-08 00:00:00 UTC,,"ONAHAMA,JAPAN"
5,412331087,2016-07-24 11:14:02 UTC,0,,LU HUANG YUAN YU
117,30,0,0,0,,"" 5,775994600,2016-07-02 07:43:55 UTC,9318814,,ELIZABETH
A MCCALL,60,44,9,3.5,2016-11-16 06:05:00 UTC,,GUIRIA