Cfrtjryk

I have a comma-separated file which has numeric and string columns. String columns are quoted and can have comma in between the quotes. How do I identify the columns with FS =","?

Sample records

"prabhat,kumar",19,2000,"bangalore,India"

In AWK it should be

$1 = "prabhat,kumar"

$2 = 19

$3 = "2000"

$4 = "bangalore,india"

Setting FS="," is creating the problem.

Input is:

"prabhat,kumar",19,2000,"bangalore,India","ABC,DEF","GHI",123,"KLM","NOP,QRS"

"prabhat,kumar",19,2000,"bangalore,India","ABC,DEF","GHI",123,"KLM","NOP,QRS"

Output should be:

"prabhat,kumar"|19|2000|"bangalore,India"|"ABC,DEF"|"GHI"|123|"KLM"|"NOP,QRS"

"prabhat,kumar"|19|2000|"bangalore,India"|"ABC,DEF"|"GHI"|123|"KLM"|"NOP,QRS"

Code I am trying:

awk -F"," '{for(i=1;i<=NF;i++){if(i%NF==0){ORS="n"} {if($i ~ /^"/ || $i ~ /"$/) {a=a OFS $i;j++;{if(j%2==0){sub(/^,/,X,a); print a;j=0;a=""}}} else {print $i}}} {ORS="|"}}' ORS="|" OFS=, p.txt

First of all, you should use a proper CSV parser. For example, in Perl, you can use Text::CSV:

1. 
   

   Install cpanm (if you use Perl, you'll thank me later)

   
   
   

   $ sudo apt-get install cpanminus
   
   

   
   
   

   If you're not on a Debian based system, you should be able to install it using your distribution's package manager.

   
   


2. 
   

   Install the Text::CSV module

   
   
   

   $ sudo cpanm Text::CSV
   
   

   
   


3. 
   

   Parse your file

   
   
   

   $ perl -MText::CSV -le '
   
       $csv = Text::CSV->new({binary=>1}); 
   
       while ($row = $csv->getline(STDIN)){
   
       print "1:$row->[0], 2:$row->[1], 3:$row->[2], 4:$row->[3]"}' < file.csv 
   
   1:prabhat,kumar, 2:19, 3:2000, 4:bangalore,India
   
   

   
   
   

   As you can see above, the 1st field is $row->[0], the second $row->[1] etc.

   
   

That was the correct way. A simpler, but dirty, hack would be to replace any quoted commas with another character. Then, use awk normally and finally, switch them back to commas again. I am using ### here but you can use anything you are sure will never be in one of your fields.

$ sed -r 's/("[^",]+),([^",]+")/1###2/g' file.csv | 

    awk -F, '{print $1,$3}' | sed 's/###/,/g'

"prabhat,kumar" 2000

If you have GNU awk:

$ awk -vFPAT='[^,]*|"[^"]*"' '{ gsub("^"|"$","",$1); gsub("^"|"$","",$4); print $1 $4} '

prabhat,kumarbangalore,India

The output format is a little ugly as I've only printed $1 and $4 next to each other - I'm sure you can alter it to your taste.

If you need to retain the double quotes around the fields, remove both gsub(); functions.

Explanation:

Normally, awk separates fields in a record by the content of the FS (Field Separator) variable, which defaults to any whitespace (tab, space and newline). The separator tells awk where a record ends. In a csv file, a record ends at a comma (passed to awk as -vFS=,), but of course, in examples similar to yours, this is too simple and it breaks.

Alternatively, the FPAT (Field Pattern) defines the record within awk. Instead of telling awk where the record ends, you create a definition that encompasses a whole record. In the complex csv of your example, this is [^,]*|"[^"]*"

This breaks down as follows:-

• Characters that are not commas ([^,]) as many times as possible (*). Everything between two commas is a field.


• Or (|)


• A single double-quote (") followed by no double-quotes ([^"]) as many times as possible (*) followed by a single double-quote ("). Everything within double-quotes (including a comma) counts as one field.

Ruby is handy for CSV parsing:

ruby -rcsv -ne 'puts CSV.generate_line(CSV.parse_line($_), :col_sep=>"|")' file

prabhat,kumar|19|2000|bangalore,India|ABC,DEF|GHI|123|KLM|NOP,QRS

prabhat,kumar|19|2000|bangalore,India|ABC,DEF|GHI|123|KLM|NOP,QRS

Note that there are no quotes in the outputs. This is because none of the fields contain the field separator. If you need quotes, you can forcibly quote all fields (even integers):

ruby -rcsv -ne 'puts CSV.generate_line(CSV.parse_line($_), :col_sep=>"|",:force_quotes=>true)' file

"prabhat,kumar"|"19"|"2000"|"bangalore,India"|"ABC,DEF"|"GHI"|"123"|"KLM"|"NOP,QRS"

"prabhat,kumar"|"19"|"2000"|"bangalore,India"|"ABC,DEF"|"GHI"|"123"|"KLM"|"NOP,QRS"

You can use perl instead:

$ echo '"prabhat,kumar",19,2000,"bangalore,India"' |

  perl -F',(?![[:alpha:]])' -anle 'print "$F[0] $F[3]"'

"prabhat,kumar" "bangalore,India"

This worked for me:

$ echo '"prabhat,kumar",19,2000,"bangalore,India"' | 

  awk -F, '{print $1,$2,$3,$4,$5,$6}'| 

    awk -F" '{print $2,$3,$4}'|awk -F  '{print $1","$2,$3,$4,$5","$6}'`