1

I need to change the delimiter of a csv file from comma(,) to Pipe(|) Symbol.

Note: Here file separator is also coming inside data string. I need to skip that value i tried one command But it is changing insider comma also awk '$1=$1' FS="," OFS=":" Sample.csv (I tried this but not giving expected output)

Input > Sample.csv

Col1, Col2, Col3 , Col4
101, XYZ, 2020 , "Hello, World"
102, PQR, 2021, "Help, Me"

Output > Sample_Out.csv should be like.

Col1| Col2| Col3|Col4
101 | XYZ | 2020 | "Hello, World"
102 | PQR |2021 | "Help, Me"
dibery
  • 2,760
  • 4
  • 16
  • 25
Vishal Vats
  • 21
  • 2

3 Answers3

1

Try this, special handling of double quotes is needed, so split the fields with it, process alternate field and replace the comma to pipe.

awk '{for(i=1; i<=NF; i+=2) gsub(",","|",$i)}1' FS=\" OFS=\" Sample.csv

Output:

Col1| Col2| Col3 | Col4
101| XYZ| 2020 | "Hello, World"
102| PQR| 2021| "Help, Me"
Vijay
  • 778
  • 4
  • 9
0

awk is for raw text processing and therefore cannot handle commas in quotes. Instead, you should use tools designed for csv like csvtool:

csvtool -u \| -t , cat Sample.csv > Sample_Out.csv

From csvtool doc:

-t Input separator char.

-u Output separator char.

cat

This concatenates the input files together and writes them to the output. You can use this to change the separator character.

 Example: csvtool -t TAB -u COMMA cat input.tsv > output.csv

Otherwise you may follow this question by using ruby. You can also use python or other programming languages that handles csv files.

dibery
  • 2,760
  • 4
  • 16
  • 25
0

You have to do some actions to take care of these quoted commas. You can start changing them into something else :

awk -F'"' -v OFS='' '{ for (i=2; i<=NF; i+=2) gsub(",", "§", $i) } 1' Sample.csv

Then you can change the remaining commas into whatever you want :

awk '$1=$1' FS="," OFS=":" Sample.csv

And you change the § into the commas before it

awk '$1=$1' FS="§" OFS="," Sample.csv

I think you can make a awk file and put all the steps into it.

Funkynene
  • 63
  • 8