The other day I had to split a csv field into two fields. In a large .csv file, with many fields, I wanted to split a “ZIP code and City” field (1012 AA Amsterdam for example) into a ZIP code (1012 AA) and City (Amsterdam) field. Read on…
The “ZIP code and City” field was field 5, which needed to become fields 5 and 6. The solution I came up with is, in Vim:
%s/v"([0-9]{4} [A-Z]{2}) (.*)"/1","2/g
Afterwards you need to update the field information in the head of your comma separated values (CSV) file.
Print or delete csv fields with AWK/Gawk
You can print or delete csv fields with AWK/Gawk. For example:
print csv fields
awk -F "," '{ print $5 }' file.csv
This prints the fifth field ($5), the parameter -F tells awk to use a comma as separator. You can either use -F "," or just -F,
delete csv fields
(found somewhere on Stack Overflow but lost the URL)
awk -F, '{for(i=1;i<=NF;i++)if(i!=x)f=f?f FS $i:$i;print f;f=""}' x=3 file.csv >> file2.csv
This deletes the third field (x=3) and redirects output to file2.csv.
If you have better, faster solutions, please share as a comment!