4

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
  • The command should be effective in Awk 3.x on RHEL 6.x environment. – Arijit Choudhury Aug 31 '17 at 21:58
  • 2
    What did you try? – pfnuesel Aug 31 '17 at 21:59
  • We tried this command which works fine on the higher environment. sed -e '/^\s*$/d' -e '$d' tpt.csv | awk 'BEGIN { FPAT="([^,]+)|(\"[^\"]+\")"; OFS="\t" } $1=$1' | sed -e 's/"//g' > tpt.tsv – Arijit Choudhury Aug 31 '17 at 22:01
  • Do you strictly need it to be a single command in awk? I made a script but it doesn't use awk and neither sed. – nxnev Sep 1 '17 at 2:20
  • Hi Nexnev, a script should be fine too, as long as it is able to process both the mentioned scenarios i.e. file with quotes and without quotes. Any combination of command would also be fine like we did try to mix and match SED and AWK to get the results. Even we tried a single SED command which actually gave the desired result but takes a lot of time to process, approx it took 8~10 Hours. The requirement is to process files of size 6 to 7 GB in less than 1 hour. – Arijit Choudhury Sep 1 '17 at 6:43
1

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.

3

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"}]
2

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
  • thanks for quick reply, however the requirement is same command should suffice both file type i.e. with quotes and without quotes. We did get a SED command however it takes 8 hours to process a 4GB file. Here is the link for that command etltechsolutions.wordpress.com/2016/05/23/… – Arijit Choudhury Aug 31 '17 at 23:33
  • @ArijitChoudhury, ok, see my unified approach – RomanPerekhrest Sep 1 '17 at 7:29
0

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
  • 1
    bash is actually extremely slow, so I would not recommend this for a huge file. – glenn jackman Sep 1 '17 at 10:23
0

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

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.