Linux convert Comma delimited file to pipe

Question

I have file in linux with comma delimited and string fields in double quotations ", I need to convert them to pipe delimiter please share your inputs.

Example:

Input:

"2017-09-30","ACBD,TVF","01234",NULL,18,NULL,"686091802","BANK OF ABCD, LIMITED, THE",790456

Output:

2017-09-30|ACBD,TVF|01234|NULL|18|NULL|686091802|BANK OF ABCD, LIMITED, THE|790456

Thanks In advance...

Thanks,

Probably belongs on superuser. See "tr" https://ss64.com/bash/tr.html or https://stackoverflow.com/questions/8349771/unix-tr-find-and-replace — Martin Beckett, Nov 06 '17 at 19:12
Possible duplicate of [unix tr find and replace](https://stackoverflow.com/questions/8349771/unix-tr-find-and-replace) — Martin Beckett, Nov 06 '17 at 19:14
@MartinBeckett: This is not simple find & replace because OP wants to keep commas as is within quotes. — anubhava, Nov 06 '17 at 19:22
sed and tr will replace all "," but we have commas in between string eg: ,"BANK OF ABCD, LIMITED, THE", ..it should be |BANK OF ABCD, LIMITED, THE| .. Please check input and output stings — user2601350, Nov 06 '17 at 19:23
@user2601350: Why is your output not showing double quotes around some field values as in input? — anubhava, Nov 06 '17 at 19:23
@anubhava, probably because none of the fields now contain the separator, quotes are not strictly required. — glenn jackman, Nov 06 '17 at 20:02

anubhava · Answer 1 · 2017-11-06T20:41:06.050

2

Here is a solution using gnu awk using FPAT variable:

awk -v OFS='|' -v FPAT='"[^"]*"|[^,]*' '{
   for (h=1; h<=NF; h++) printf "%s%s", $h, (h < NF ? OFS : ORS)
}' file

"2017-09-30"|"ACBD,TVF"|"01234"|NULL|18|NULL|"686091802"|"BANK OF ABCD, LIMITED, THE"|790456

Working Code Demo

edited Nov 06 '17 at 20:41

answered Nov 06 '17 at 19:21

anubhava

713,503
59
514
593

Thanks for the details but I am getting following output – user2601350 Nov 06 '17 at 19:41
"2017-09-30","ACBD,TVF","01234",NULL,18,NULL,"686091802","BANK|OF|ABCD,|LIMITED,|THE",790456 – user2601350 Nov 06 '17 at 19:41
Are you sure you have gnu awk in your system? Check with `awk --version` command. – anubhava Nov 06 '17 at 20:04

score 1 · Answer 2 · answered Nov 06 '17 at 19:59

You would be well-served to use a proper CSV parser. Here's a Ruby example:

ruby -rcsv -e '
  f = ARGV.shift
  csv_out = CSV.new $stdout, :col_sep => "|"
  CSV.foreach(f) {|row| csv_out << row}
' file.csv

outputs

2017-09-30|ACBD,TVF|01234|NULL|18|NULL|686091802|BANK OF ABCD, LIMITED, THE|790456

score 0 · Answer 3 · answered Nov 06 '17 at 19:25

0

Easiest probably to use sed

PIPE_SEP_STR=$(echo $COMMA |sed -e 's/,/|/g')

where COMMA is the variable which contains your comma seperated string

answered Nov 06 '17 at 19:25

Jiss Janardhanan

168
2
9

1

that will substitute commas inside quoted strings, which is wrong. – glenn jackman Nov 06 '17 at 20:01

Claes Wikner · Answer 4 · 2017-11-07T01:00:34.520

0

awk '{sub(/"/,"")gsub(/",|,"|","/,"|")sub(/,18,/,"|18|")}1' file

2017-09-30|ACBD,TVF|01234|NULL|18|NULL|686091802|BANK OF ABCD, LIMITED, THE|790456

edited Nov 07 '17 at 01:00

answered Nov 07 '17 at 00:28

Claes Wikner

1,397
1
8
7

Linux convert Comma delimited file to pipe

4 Answers4