-1

How to I parse the CSV file

file name : abc.csv (csv file) The above file containing data like

abv,sfs,,hju,',',jkk
wff,fst,,rgr,',',rgr
ere,edf,erg,',',rgr,rgr

I have a requirement like i have to extract different field and assign them into different variables.

My Code:

 cat $file | awk 'NR!=1' | while read -r line
  do
     a=`echo "$line" | awk -F',' '{print $1}'`
     b=`echo "$line" | awk -F',' '{print $2}'`
     c=`echo "$line" | awk -F',' '{print $3}'`
     d=`echo "$line" | awk -F',' '{print $4}'`
     e=`echo "$line" | awk -F',' '{print $5}'`
     f=`echo "$line" | awk -F',' '{print $6}'`
     echo "$e"
     echo "$f"
done

output:(it gave the output as single quote)

'
'
'
'
'
'

Required Output Should be like:(my 5th field having value "comma" )

   ,
   jkk
   ,
   rgr
   ,
   rgr
   rgr
  • 4
    Use a dedicated CSV parser. For the shell, there is csvkit. There are CSV parser modules for most types of scripting languages, like Python And Perl. – Kusalananda Feb 19 at 10:21
4

Using cvscut from the csvkit toolbox:

$ csvcut -q "'" -c 5,6 file.csv
",",jkk
",",rgr
rgr,rgr

The -q "'" is needed to tell csvcut that a non-standard quoting character (single quote) is used in the data. The -c 5,6 extracts columns five and six. The output would be a properly formatted CSV document.

To get only column five, without added quotes:

$ csvcut -q "'" -c 5 file.csv | csvformat -T
,
,
rgr

This passes the output of csvcut through csvformat and asks it to use tabs for output delimiter instead of commas. Since there is no tabs in the data, the data no longer has to be quoted.

This also obviously works for getting multiple columns in tab-delimited format:

$ csvcut -q "'" -c 5,6 file.csv | csvformat -T
,       jkk
,       rgr
rgr     rgr

And it also works for converting all of the comma-separated data to tab-separated data:

$ csvformat -q "'" -T file.csv
abv     sfs             hju     ,       jkk
wff     fst             rgr     ,       rgr
ere     edf     erg     ,       rgr     rgr

Having tab-separated data makes it easier to work on it using standard Unix tools:

$ csvformat -q "'" -T file.csv | cut -f 5,6
,        jkk
,       rgr
rgr     rgr

The following reproduces your expected output:

$ csvformat -q "'" -T file.csv | awk -F '\t' '{ print $5; print $6 }'
,
jkk
,
rgr
rgr
rgr

(Note that the expected output in the question has an unexpected comma, which I assume comes from column four on the last line. It should not be there.)


For more advanced parsing, do consider another scripting language like Python or Perl.

The Text::CVS module in Perl gives easy access to CSV data. The following just shows that it's fairly easy to read in the complete file into an array of arrays in Perl:

$ perl -MData::Dumper -MText::CSV=csv -e '$c = csv(in=>"file.csv",quote_char=>"\x27");print Dumper($c)'
$VAR1 = [
          [
            'abv',
            'sfs',
            '',
            'hju',
            ',',
            'jkk'
          ],
          [
            'wff',
            'fst',
            '',
            'rgr',
            ',',
            'rgr'
          ],
          [
            'ere',
            'edf',
            'erg',
            ',',
            'rgr',
            'rgr'
          ]
        ];
  • i don't have csvcut,csvformat , i want in awk or sed command – J.Jena Feb 19 at 12:10
  • 1
    @J.Jena Your comma-separated document has a comma as one of the values. That's not really a standard situation, so you would be hard-pressed to find a solution that does not involve a proper CSV parser. – Haxiel Feb 19 at 13:19
  • 1
    @J.Jena, please consider installing the csvkit toolbox. – sudodus Feb 19 at 13:52
  • 3
    @J.Jena CSV is a format with exact rules for quoting things like newlines and fields containing commas and double quotes. If you want to do this with awk, you would have to implement a CSV parser in awk. This is harder than using a ready-made library or tool. Of course, you can do something that works for your data today, but that would possibly break tomorrow, or when you try it on somebody else's data. And I'm not going to write something like that, sorry. – Kusalananda Feb 19 at 14:01
  • 1
    a little tidier: quote_char=>"\047" or quote_char=>"\x27" – glenn jackman Feb 19 at 14:41
0

With miller

<input sed "s/'/\"/g"  | mlr --c2x --implicit-csv-header cut -f 5,6 | \
grep -v "^$" | cut  -d " " -f 2

gives you

,
jkk
,
rgr
rgr
rgr

It's not awk, it's Miller a great tool for structured text (http://johnkerl.org/miller/doc/).

  • Just changing one single quotes to double quotes can be done with y/'/\"/ in sed, but is more commonly done with tr "'" '"'. – Kusalananda Feb 19 at 17:39
  • @Kusalananda thank you. However it's similar to my way :) – aborruso Feb 19 at 18:41

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.