Parsing CSV with AWK to Produce HTML Output

awkcsvhtml

I have a data file with rows of comma-separated fields like this:

United Kingdom, GB, +44

and I want to produce the following output for each line in the file:

<option value="GB">United Kingdom +44</option>

I got as far as follows with awk, but after adding the angle brackets I am getting mangled output:

BEGIN{FS=",";}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
    print "<option value=\"" $2 "\">";                                                                                                                                                                                                                         

}

Best Answer

awk's printf function can be easier to use when it comes to embedded quotes.

awk -F, '{printf("<option value=\"%s\">%s %s</option>\n", $2, $1, $3)}'

The problem with that is that you also have whitespace between each field. We can use the gsub function to trim each field.

awk -F, '{gsub(/^ +| +$/,"", $2); printf("<option value=\"%s\">%s %s</option>\n", $2, $1, $3)}'

Or what's easier is to change our field separator: awk -F' *, *' '{printf("%s %s\n", $2, $1, $3)}'

If you need to trim multiple fields then it might be better to use a loop or a function (depending on the situation). See https://stackoverflow.com/questions/9985528/how-can-i-trim-white-space-from-a-variable-in-awk for more info.

Related Solutions

Bash – Ignore delimiter present inside quotes

You are doing a couple of things wrong here:

You are using the shell to parse text.

While this is possible, it is very inefficient. It is slow, hard to write, hard to read and very hard to do properly. The shell just isn't designed for this sort of thing.
You are trying to parse a csv file without a csv parser.

CSV is not a simple format. You can have fields that contain the delimiter as you do here. You can also have fields spanning multiple lines. Attempting to parse arbitrary CSV data with simple pattern matching is very, very complicated and extremely hard to get right.

The bad, hacky solution is to do something like this:

$ sed 's/","/"|"/g' file.csv | 
    while IFS='|' read -r Key Name Address1 Address2 City \
     State Country SwiftCode Nid Chips Aba IsSwitching; do 
        echo "From Key : $Key"; echo "From Name : $Name"; 
    done
From Key : "ID0054XX"
From Name : "PT. SUMUT"
From Key : "ID00037687"
From Name : "PAN INDONESIA, PT."

That will replace all "," with "|" and then use | as the delimiter. Of course, that will fail if any of your fields can contain |.

The good, clean approach is to use a proper scripting language, not the shell, and a csv parser. For example, in Perl¹:

$ cat file.csv | perl -MText::CSV -le '
    $csv = Text::CSV->new({binary=>1}); 
    while ($row = $csv->getline(STDIN)){ my ($Key, $Name, $Address1, $Address2, $City, $State, $Country, $SwiftCode, $Nid, $Chips, $Aba, $IsSwitching) = @$row;
print "From Key: $Key\nFrom Name: $Name";}' 
From Key: ID0054XX
From Name: PT. SUMUT
From Key: ID00037687
From Name: PAN INDONESIA, PT.

Or, as a script:

#!/usr/bin/perl -l
use strict;
use warnings;
use Text::CSV;

open(my $fh, "file.csv");
my $csv = Text::CSV->new({binary=>1}); 
while (my $row = $csv->getline($fh)){
    my (
            $Key, $Name, $Address1, $Address2, $City,
            $State, $Country, $SwiftCode, $Nid, $Chips,
            $Aba, $IsSwitching
         ) = @$row;
    print "From Key: $Key\nFrom Name: $Name";
}

Note that you'll have to install the Text::CSV module first (cpanm Text::CSV) and you might want to install cpanm (package cpanminus on most distributions)

Alternatively, in Python 3:

#!/usr/bin/env python3

import csv
with open('file.csv', newline='') as csvfile:
    linereader = csv.reader(csvfile, delimiter=',', quotechar='"')
    for row in linereader:
        print("From Key: %s\nFrom Name: %s" % (row[0], row[1]))

Saving the Python code above as a script and executing it on your file will print:

$ foo.py
From Key: ID0054XX
From Name: PT. SUMUT
From Key: ID00037687
From Name: PAN INDONESIA, PT.

¹_{Yes, I am aware that that is a UUoC, but it's simpler to write as a one liner this way.}

Best Answer

Related Solutions

Bash – Ignore delimiter present inside quotes

Related Question