3

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
  • 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 Feb 7 '18 at 12:45
5

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.

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

You can try this awk

awk 'NR%2==1{gsub(",","~")}1' RS='"' ORS='"' infile
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

This is opposite of this question that I had answer there too, it's can be done by starting loop step value from 1.

awk -F\" '{for (i=1;i<=NF;i+=2)gsub(",","~",$i)}1' OFS=\" infile

Output is:

123~"ABC, DEV 23"~345~534.202~NAME
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.

  • The code may provide answer to the question, but any explanations will be highly appretiated – Romeo Ninov Feb 23 '18 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.