aman grewal aman grewal - 1 year ago 47
Bash Question

How to split a csv file using comma(,) if file attributes can also contain comma using shell/awk/perl

I Need to split a CSV file using comma and output specific fields to some other CSV file. But Input CSV file has a few attributes that can contain comma itself.
Input CSV looks like below.

Input CSV


Desired output is as below.



i am using a following awk command

awk 'BEGIN{FS=",";OFS=","} { print $2 , $4;}' input.csv > ouput.csv

But it is giving me following output


i thought about using ", as a value to IFS but then that can also be part of input csv as it is user defined file and there are no specific checks to stop user from doing so. Another problem i have is the server where i am going to execute is Solaris and it has only standard awk. is there a way around here to solve this problem?

Answer Source

Try to use following perl oneliner

perl -ne '@ar = (split /(?<=\w"),/)[1,3]; print @ar,"\n"; ' input.csv

-n switch use to through the while loop for a file

-e switch for make the command to execute.

Then I used the positive look behind for split the comma, where comma is followed by word character (\w) and the double quotes ".

(?<=) For look ahead

\w Match the word character

Then I store the element 1 and 3 in an array.