Linux convert Comma delimited file to pipe

Shell Programming and Scripting
1

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,

2

Hi, try:

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

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

--
On Solaris use /usr/xpg4/bin/awk

3

Or with sed

sed '
  s/$/,/
  s/"\{0,1\}\([^"]*\)"\{0,1\},/\1|/g
  s/|$//
' file
4

Using python csv module:-

import csv

i_file = open('input.csv', 'rb')
o_file = open('output.csv', 'wb')

reader = csv.reader(i_file)

for row in reader:
        writer = csv.writer(o_file, delimiter='|')
        writer.writerow(row)

i_file.close()
o_file.close()
5

Hi.

If you are going to be dealing with DSV (delimiter-separated-value data, including CSV and TSV), you may be interested in a suite of tools from eBay.

The one that is most applicable here is:

csv2tsv Convert Comma-separated file to tab-separated file (tsv). (doc)
Path    : ~/executable/csv2tsv
Version : v1.1.14
Type    : ELF 64-bit LSB executable, x86-64, version 1 (GNU ...)
Home    : https://github.com/eBay/tsv-utils-dlang

The tools are available in statically-linked executables as well as source, but the source is D, not all that common in my experience.

So for data on file z9:

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

this code:

$ csv2tsv -t '|' z9

produces:

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

On a system like:

OS, ker|rel, machine: Linux, 3.16.0-4-amd64, x86_64
Distribution        : Debian 8.9 (jessie) 
bash GNU bash 4.3.30

Tools in the suite are:

csv2tsv       Convert Comma-separated file to tab-separated file (tsv).
keep-header   Execute a command in a header aware fashion (tsv).
tsv-append    Concatenates multiple TSV files.
tsv-filter    Test fields for relationship, regular expression match, etc.
tsv-join      Match input lines against lines from a 'filter' file.
tsv-pretty    Output TSV data in a aligned format, cf. align.
tsv-sample    Randomize, sample input lines.
tsv-select    Re-order fields, cf. cut.
tsv-summarize Display results of aggregation operations on fields.
tsv-uniq      Filter out duplicate lines using fields (or line) as a key.

Best wishes ... cheers, drl

1 Like