8

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
1
  • You will very unlikely have an answer. Best pratice on stack exchange is to show some work and some of your try. You should consult unix.stackexchange.com/tour You can edit your question to show what you've try so far.
    – Kiwy
    Commented Feb 7, 2018 at 12:45

7 Answers 7

7

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.

1

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
1

You can try this awk

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

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

0

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'
0

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
0
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.

1
  • The code may provide answer to the question, but any explanations will be highly appretiated Commented Feb 23, 2018 at 7:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .