3

when I'm working with csv, unwanted commas(',') is misleading my csv file, in result it gives the inconsistency.

please find in details below.

My sample csv file:

1|a,b|4
1|c,d|4
1|e,f|4
1|g,h|4
1|i,j|4

I want the end result As:

1|"a,b"|4
1|"c,d"|4
1|"e,f"|4
1|"g,h"|4
1|"i,j"|4

After adding the quotes I will replace "|" with "," so that my csv will work as I expected.

I used below commnd but its not giving as exprected.

sed -e 's/,/"&"/' file1.txt
3
  • 3
    But your file is not comma-separated ! Given what you want to quote, it is bar (pipe, "|") separated. If you quote it as shown, it makes invalid CSV (because quotes in fields need to be quoted). If your problem is importing it, any decent package will allow "|" as an alternative separator. Jun 5 '21 at 7:48
  • Hi @Paul_Pedant sorry for misleading you and everyone after i add quotes i will replace "|" with "," so that my csv will work perfectly.
    – G.kalyan
    Jun 5 '21 at 8:00
  • I think you are suffering from an xy problem. Kusalananda's answer solves your actual problem.
    – Vincent
    Jun 5 '21 at 20:08
14

Using csvformat from csvkit, and assuming that the end result should be a CSV file with comma as delimiter (as described in the text of the question):

$ csvformat -d '|' file
1,"a,b",4
1,"c,d",4
1,"e,f",4
1,"g,h",4
1,"i,j",4

This reformats the CSV file from having |-characters as delimiter to having the default comma as delimiter. In doing so, it properly quotes the fields that need quoting.

This also properly handles fields with embedded newlines:

$ cat file
1|a,b|4
1|c,d|4
1|e,f|4
1|g,h|4
1|i,j|4
2|"line 1,
line2"|5
$ csvformat -d '|' file
1,"a,b",4
1,"c,d",4
1,"e,f",4
1,"g,h",4
1,"i,j",4
2,"line 1,
line2",5

If you have a document in some structured document format, such as CSV, JSON, XML, YAML, TOML, etc., there is no reason not to use a parser for that document format to parse that document.

0
3

You could do:

awk -F'[|]' -v OFS=',' -v q='"' '{ for(i=1; i<=NF; i++) $i=q $i q }1' infile

with -F'[|]' we defined input field separator.
with -v OFS=',' we defined output filed separator.
NF identifies how many fields are there in each line/record based on the FS (input field separator), so we loop over the number of fields and add double quotes for each of them and print the final update on the line with awk's 1 idiom for the print.

note that all the fields are getting quoted with this command which obviously that is not an issue for having a valid CSV file.

0
3

With sed:

$ sed 's/[^|]*,[^|]*/"&"/g; y/|/,/' ip.txt
1,"a,b",4
1,"c,d",4
1,"e,f",4
1,"g,h",4
1,"i,j",4
  • s/[^|]*,[^|]*/"&"/g add double quotes to all fields containing ,
  • y/|/,/ change all | characters to ,

With perl:

perl -F'\|' -lane 'print join ",", map {/,/ ? qq("$_") : $_} @F'

This uses | as input field separator. Then map will add double quotes for all fields containing ,. Finally, join is used to combine the fields with , character.

0
2

Using awk:

awk 'BEGIN{FS="|"; OFS=","; dq="\042"}
{for(i=1;i<=NF;i++) if ($i ~ /,/)$i = dq $i dq;}1' file                                                                                                          

First of all, field separator(FS) and output field separator(OFS) are set to | and , respectively. A new variable dq is set to double quotes(\042) in BEGIN rule.

If comma is found in main block, then that field is changed with double quotes around the field.

If you want to put quotes around everything but numbers, then the following would do.

awk 'BEGIN{FS="|"; OFS=","; dq="\042"}
{for(i=1;i<=NF;i++) if ($i+0 != $i)$i = dq $i dq;}1' file
4
  • 1
    Hey @Prabhjot Singh Thank you so much it is working fine
    – G.kalyan
    Jun 5 '21 at 8:50
  • @G.kalyan I am glad to hear this. Jun 5 '21 at 8:59
  • 2
    About gsub(/^.*$/, dq $i dq, $i): a) you should be using sub() here instead of gsub() because you can't do multiple substitutions using a regexp that matches the whole field so using gsub() is misleading at best and may also be inefficient. b) you shouldn't be using any *sub() function at all because it's not necessary to do more than a simple string assignment and having $i in the replacement text means it'll fail when $i contains a replacement metachar such as &. All you need here is a simple $i = dq $i dq. Always be wary of using input data in a regexp or replacement text
    – Ed Morton
    Jun 5 '21 at 13:53
  • Please elaborate Always be wary of using input data in a regexp or replacement text. Jun 5 '21 at 15:19
0

Another sed way:

  sed 's;\([^|]*\)|\([^|]*\)|\(.*\)$;\1,"\2",\3;' data

Or if your sed support the loading of ERE, such as GNU sed, you can avoid all the escaping job:

  sed -E 's;([^|]+)\|([^|]+)\|(.+)$;\1,"\2",\3;' data

You can exploit the fact that only the middle group is delitmited by a | at each boundaries and make sed even more shorter:

sed 's;|\([^|]*\)|;,"\1",;' data

Of course also here, if your sed support -E you can load the ERE and avoid a tedious escaping job

0

SImilar idea to @Kusalananda's: use a specialized CSV parsing tool. This is a ruby example:

ruby -rcsv -e 'CSV.foreach(ARGV.shift, col_sep: "|") {|row| puts CSV.generate_line row}' file
1,"a,b",4
1,"c,d",4
1,"e,f",4
1,"g,h",4
1,"i,j",4
0
awk -F "|" 'OFS="|" {print $1,"\""$2"\"",$3}' filename

output

1|"a,b"|4
1|"c,d"|4
1|"e,f"|4
1|"g,h"|4
1|"i,j"|4

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.