Change delimiter in a csv file

awkreplacetext processing

I have a input file delimited with commas (,). There are some fields enclosed in double quotes that are having a comma in them. Here is the sample row 

123,"ABC, DEV 23",345,534.202,NAME

I need to remove all the comma's that are not occuring within the double quotes by a ~
So the output should be like: 

123~"ABC, DEV 23"~345~534.202~NAME

I have tried this, but it gives me reverse output:

awk -F '"' -v OFS='' '{ for (i=0; i<= NF; ++i) gsub(",","~",$i) } 1' test.txt
123,ABC~ DEV 23,345,534.202,NAME

Best Answer

You basically have a CSV file that you would like to replace the delimiter in, from , to ~.

Using csvkit:

$ csvformat -D '~' file.csv >newfile.csv

$ cat newfile.csv
123~ABC, DEV 23~345~534.202~NAME

cvsformat removes the quotation marks that are not needed. To add quotation marks:

$ csvformat -U 1 -D '~' file.csv
"123"~"ABC, DEV 23"~"345"~"534.202"~"NAME"

See csvformat --help for usage info.

Related Question