CODEWITHSUNDEEP
bashcsv
Jay Killeen
Jay Killeen

Reputation: 2922

cat bash command to convert tab to comma delimited and wrap in double quotes

I have the following data (as an example) 

name    street    suburb
Todd Man    14 Streety Road    Townsville
Cheryl Lady 15 Roady Street    Villatown
Girl, Sara  16 Circus Circuit  Groundston

and using this command to convert from a tab seperated to comma seperated csv

cat customers.csv | tr '[\t]' '[,]' > customers_comma.csv

The output is then

name,street,suburb
Todd Man,14 Streety Road,Townsville
Cheryl Lady,15 Roady Street,Villatown
Girl, Sara,16 Circus Circuit,Groundston

But Girl, Sara is adding in an extra column. How can I make it so the output is

name,street,suburb Todd Man,14 Streety Road,Townsville Cheryl Lady,15 Roady Street,Villatown "Girl, Sara",16 Circus Circuit,Groundston

Seems simple enough but have Googled the hell out of this and can't find a solution.

I don't have to use cat but would like if there was a one line bash command to do it.

Upvotes: 0

Views: 276

Answers (2)

rici
rici

Reputation: 241731

Here's a little awk solution:

awk 'BEGIN{FS="\t";OFS=",";Q="\""}
          {for (i=1;i<=NF;++i)
             if ($i ~ /[",]/)
               $i = Q gensub(/"/,Q Q,"g",$i) Q
          }
          {$1 = $1}
     1' file.txt

It also doubles quotes if one is found in a field.

Upvotes: 1

Avinash Raj
Avinash Raj

Reputation: 174706

You may try the below sed command.

$ sed 's/\([^[:space:],]\+, *[^[:space:]]\+\)/"\1"/g;s/\t/,/g' file
name,street,suburb
Todd Man,14 Streety Road,Townsville
Cheryl Lady,15 Roady Street,Villatown
"Girl, Sara",16 Circus Circuit,Groundston

where [^[:space:],]\+ matches any character but not of a space or comma one or more times.

Upvotes: 0

Related Questions