Change delimiter in a CSV file

Question

I have an input file in CSV format. There are some fields enclosed in double quotes that have a comma in them. Here is the sample row

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

I need to remove all the commas that are not occurring within the double quotes by a ~, so the output should be like this:

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

I have tried this, but it gives me the 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

Answer 1

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.

---

Using Miller (mlr) instead:

$ mlr --csv -N --ofs '~' cat file.csv
123~ABC, DEV 23~345~534.202~NAME

Retaining the original quotes even though they are no longer needed:

$ mlr --csv -N --ofs '~' --quote-original cat file.csv
123~"ABC, DEV 23"~345~534.202~NAME

In both of these commands, the -N option is use to signal the fact that the input has no header and that the output shouldn't have one either. The --ofs option sets the output field separator.

Answer 2

GNU awk solution:

awk -v FPAT='[^,]+|"[^"]+"' '{ for(i=1;i<=NF;i++) printf "%s%s",$i,(i<NF? "~" : ORS) }' file

• FPAT='[^,]+|"[^"]+"' - regex pattern describing each field is either “anything that is not a comma,” or “a double quote, anything that is not a double quote, and a closing double quote.”

The output:

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

Answer 3

You can try this awk

awk 'NR%2==1{gsub(",","~")}1' RS='"' ORS='"' infile

Answer 4

Using Raku (formerly known as Perl_6)

~$ raku -MText::CSV -e 'csv(in => csv(in => $*IN), out => $*OUT, sep => "~");' < Adhya.csv

Sample Input:

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

Sample Output:

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

This answer uses the Raku Programming Language. The Perl(5) module Text::CSV_XS is well-regarded, and a longtime author/maintainer of that module has gone on to develop Raku's Text::CSV module (H. Merijn Brand, personal communication).

Briefly, the high-level csv(...) command is used to take csv input via $*IN (stdin). Alternative separators can be specified here with sep => "\t" or similar.

Then, the output is send via $*OUT (stdout), changing the separator to ~ tilde with the parameter sep => "~".

https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md
https://raku.org

Answer 5

I have done by below 2 methods for above example . Tested

Method1

for (( i=1;i<6;i++)); do awk -F "," -v i="$i" '$i ~ /"/{gsub(" ",",",$2);print }' inputfile;done| tail -1| sed 's/,/~/3g'| sed 's/,/~/1'

output

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

Method2

 sed "s/,/~/3g" inputfile| sed 's/,/~/1'

Output

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

Answer 6

In case you don't want to install a special package, you can probably the csv parser of a preinstalled ruby:

ruby -e 'require "csv"; CSV.filter(output_col_sep: "~") {}'

Examples:

1,"2,3"

results in

1~2,3

and

1,"
2,3
",4

results in

1~"
2,3
"~4

Answer 7

awk '{sub(/,/,"~")gsub(/,/,"~",$3)}1' file

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

The very first comma is replaced by sub and the rest by gsub in third field.