3

I have a text file with fields delimited by |. I want to convert it to , like a typical CSV. I have tried to use this:

sed 's/|/,/g' test.txt > test.csv

but some of the fields already have commas in them. For example:

var1|var2|var3
Potter, Harry|2|3

What should I do?

2

Try this

sed 's/|/","/g; s/^/"/; s/$/"/' file 

This will produce output like this:

"var1","var2","var3"
"Potter, Harry","2","3"

Explanation:

You need to enclose each of your column using quotes "" .

  • First s/|/","/g replaces all | with "," comma enclosed in quotes Output : var1","var2","var3 Potter, Harry","2","3

  • Second s/^/"/ replaces the beginning of line with quotes where ^ means beginning of line

  • Third s/$/"/ replaces the end of line with quotes where $ means end of line

You can also escape the commas in field using backslash before each comma something like this

Potter\, Harry,2,3

You can read more about csv formatting here: https://www.csvreader.com/csv_format.php

  • Yes. It works. Thanks. Can you please explain it a bit. I didn't get the part after the first column. – JXU May 22 '18 at 3:24
  • I have edited the answer , let me know if any more explanation is needed – Arushix May 22 '18 at 3:31
3

Use the right csv parser tool:

csvtool -t '|' -u ',' cat infile > outputfile
var1,var2,var3
"Potter, Harry",2,3

from csvtool --help:

-t   Input separator char.  Use -t TAB for tab separated input.
-u   Output separator char.  Use -u TAB for tab separated output.
cat
    This concatenates the input files together and writes them to
    the output.  You can use this to change the separator character.  
3

To quote only the values that contain commas:

$ sed 's/[^|]*,[^|]*/"&"/; y/|/,/' <infile
var1,var2,var3
"Potter, Harry",2,3

If the input already contained double quote characters, and they were not being used for CSV formatting (like for Riddle, Tom "Voldemort"|4|5), then for most csv formats, you escape them with "":

$ sed 's/"/""/g; s/[^|]*[,"][^|]*/"&"/; y/|/,/' <infile
"Riddle, Tom ""Voldemort""",4,5
-1

You can modify your regex to include \w, witch matches any alphanumeric and _ followed by a |

 #sed 's/\w|/,/g' example.csv 
 var1|var2|var3
 Potter, Harry|2|3
  • 1
    It doesn't work. I got output like this: Porter, Harr, ,3 – JXU May 22 '18 at 3:13

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.