4.7 Defining Fields by Content ¶

NOTE: This whole section needs rewriting now that gawk has built-in CSV parsing. Sigh.

This section discusses an advanced feature of gawk. If you are a novice awk user, you might want to skip it on the first reading.

Normally, when using FS, gawk defines the fields as the parts of the record that occur in between each field separator. In other words, FS defines what a field is not, instead of what a field is. However, there are times when you really want to define the fields by what they are, and not by what they are not.

The most notorious such case is comma-separated values (CSV) data. Many spreadsheet programs, for example, can export their data into text files, where each record is terminated with a newline, and fields are separated by commas. If commas only separated the data, there wouldn’t be an issue. The problem comes when one of the fields contains an embedded comma. In such cases, most programs embed the field in double quotes.²⁶ So, we might have data like this:

Robbins,Arnold,"1234 A Pretty Street, NE",MyTown,MyState,12345-6789,USA

The FPAT variable offers a solution for cases like this. The value of FPAT should be a string that provides a regular expression. This regular expression describes the contents of each field.

In the case of CSV data as presented here, each field is either “anything that is not a comma,” or “a double quote, anything that is not a double quote, and a closing double quote.” (There are more complicated definitions of CSV data, treated shortly.) If written as a regular expression constant (see Regular Expressions), we would have /([^,]+)|("[^"]+")/. Writing this as a string requires us to escape the double quotes, leading to:

FPAT = "([^,]+)|(\"[^\"]+\")"

Putting this to use, here is a simple program to parse the data:

BEGIN {
    FPAT = "([^,]+)|(\"[^\"]+\")"
}

{
    print "NF = ", NF
    for (i = 1; i <= NF; i++) {
        printf("$%d = <%s>\n", i, $i)
    }
}

When run, we get the following:

$ gawk -f simple-csv.awk addresses.csv
NF =  7
$1 = <Robbins>
$2 = <Arnold>
$3 = <"1234 A Pretty Street, NE">
$4 = <MyTown>
$5 = <MyState>
$6 = <12345-6789>
$7 = <USA>

Note the embedded comma in the value of $3.

A straightforward improvement when processing CSV data of this sort would be to remove the double quotes when they occur, with something like this:

if (substr($i, 1, 1) == "\"") {
    len = length($i)
    $i = substr($i, 2, len - 2)    # Get text within the two double quotes
}

NOTE: Some programs export CSV data that contains embedded newlines between the double quotes. gawk provides no way to deal with this. Even though a formal specification for CSV data exists, there isn’t much more to be done; the FPAT mechanism provides an elegant solution for the majority of cases, and the gawk developers are satisfied with that.

As written, the regexp used for FPAT requires that each field contain at least one character. A straightforward modification (changing the first ‘+’ to ‘*’) allows fields to be empty:

FPAT = "([^,]*)|(\"[^\"]+\")"

As with FS, the IGNORECASE variable (see Built-in Variables That Control awk) affects field splitting with FPAT.

Assigning a value to FPAT overrides field splitting with FS and with FIELDWIDTHS.

Finally, the patsplit() function makes the same functionality available for splitting regular strings (see String-Manipulation Functions).

NOTE: Given that gawk now has built-in CSV parsing (see Working With Comma Separated Value Files), the examples presented here are obsolete, since you can use the --csv option (in which case FPAT field parsing doesn’t take effect). Nonetheless, it remains useful as an example of what FPAT-based field parsing can do, or if you must use a version of gawk prior to 5.3.

• More on CSV Files
• FS Versus FPAT: A Subtle Difference