CSV file processing - remove quotes and replace comma delimiter with tab

Question

Need to convert delimited files with a shell command. There are two variations for the received input file, one with double quotes and another without quotes and both files have the comma as a delimiter. The requirement is to replace the comma with TAB and remove the quotes if the file has double quotes else just replace the comma. A file is sent with quotes if its fields also have comma that needs to be ignored while transforming. The single command should be effective in Awk 3.x on RHEL 6.x environment.

eg. File 1 with double quotes:

"Jhon","Carpenter","CA,TX,NJ"
"Mike","Painter","WA,GA,MI"

Post transformation it should be TAB separated:

Jhon   Carpenter   CA,TX,NJ 
Mike   Painter     WA,GA,MI 

eg. File 2 without double quotes:

EMP1,123456,CA 
EMP2,456789,TX 

Post transformation it should be TAB separated:

EMP1 123456   CA 
EMP2   456789   TX

Answer 1

Using csvkit:

$ csvformat -T file1.csv
Jhon    Carpenter       CA,TX,NJ
Mike    Painter WA,GA,MI

$ csvformat -T file2.csv
EMP1    123456  CA
EMP2    456789  TX

The output for file1.csv looks a bit off, but that's just because the tabs don't line up properly. There is a tab between all columns.

CSVKit is a Python-based toolbox of various CSV-related shell utilities. It does proper CSV parsing and can be used to query, format and convert CSV files.

If the first file, for example, had proper headers, then transforming this into JSON would be as easy as

$ csvjson file1.csv
[{"First": "Jhon", "Last": "Carpenter", "Stuff": "CA,TX,NJ"}, {"First": "Mike", "Last": "Painter", "Stuff": "WA,GA,MI"}]

Answer 2

This short sed script can process both types of files (or even mixed ones with lines of first and second type):

sed '/"/!s/,/\t/g;s/","/\t/g; s/"//g'

As it doesn't group expressions not loop it should be way faster than your script.

You seem to have GNU sed, so \t works, otherwise use the literal TAB instead.

Answer 3

Several approaches:

For the file1 (with double quotes):

-- awk approach:

awk -F'"' '{ r=""; for(i=1;i<NF;i++) 
     if ($i~/^[[:alnum:]]/) r=(r!="")? r OFS $i : $i; print r }' OFS='\t' file1

-- sed approach:

sed 's/","/\t/g; s/"//g;' file1

The output (for both approaches):

Jhon    Carpenter   CA,TX,NJ
Mike    Painter WA,GA,MI

----------

For the file2 (without double quotes) - it's enough to apply tr command:

tr ',' '\t' <file2

The output:

EMP1    123456  CA
EMP2    456789  TX

----------

Unified approach for the condition "same command should suffice both file type":

awk -v quoted=$(grep -cm1 '"' file1) 'BEGIN{ FS=(quoted)? "\"" : ","; }
     { r=""; for(i=1;i<=NF;i++) if(!quoted || $i~/^[[:alnum:]]/) r=(r!="")? r OFS $i : $i; 
             print r }' OFS='\t' file1

Answer 4

Well, this solution is not as elegant as Kusalananda's answer and I guess it wouldn't be as fast as you want, but it should work fine.

#!/usr/bin/env bash

for file; do

  while read -r line; do

    if <<< "${line}" grep -F -e '"' > '/dev/null' 2>&1; then
      <<< "${line}" \
      grep -P -o -e '(?<=")([^,].*?)(?=")' |
      tr $'\n' $'\t' | rev | cut -c 2- | rev
    else
      <<< "${line}" \
      tr ',' $'\t'
    fi

  done < "${file}"

done

Example:

$ cat file1
"Jhon","Carpenter","CA,TX,NJ"
"Mike","Painter","WA,GA,MI"

$ cat file2
EMP1,123456,CA
EMP2,456789,TX

$ script.sh file1 file2
Jhon    Carpenter   CA,TX,NJ
Mike    Painter WA,GA,MI
EMP1    123456  CA
EMP2    456789  TX

Answer 5

CSV data requires a proper CSV parser. I like Kusalananda's answer. You could also use a language like Ruby that comes with a csv module:

ruby -rcsv -e '
  out = CSV.new($stdout, {:col_sep => "\t"})
  CSV.foreach(ARGV.shift) {|row| out << row} 
' file1.csv

Answer 6

Using Raku (formerly known as Perl_6)

~$ raku -MText::CSV -e 'my @a = csv(in => $*IN, sep => ",");  \
                        csv(in => @a, out => $*OUT, sep => "\t");'  file

Above uses the Raku Programming Language with the Text::CSV module. The Perl(5) module Text::CSV_XS is well-regarded, and a longtime author/maintainer of that module has gone on to develop Raku's Text::CSV module (H. Merijn Brand, personal communication).

Raku's Text::CSV module has a high-level csv() function which will handle quoting/unquoting, etc. The OP's desired output pretty much matches the Text::CSV module's defaults, with the exception of a \t output separator (making the output a TSV file).

Sample Input 1:

"Jhon","Carpenter","CA,TX,NJ"
"Mike","Painter","WA,GA,MI"

Sample Output 1:

Jhon    Carpenter   CA,TX,NJ
Mike    Painter WA,GA,MI

Sample Input 2:

EMP1,123456,CA
EMP2,456789,TX

Sample Output 2:

EMP1    123456  CA
EMP2    456789  TX

https://unix.stackexchange.com/a/723486/227738
https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md
https://raku.org