Fun with CSV files: secondary field separators

This short article is about dealing with so-called secondary field separators. Normally, a csv has exactly one field separator, the comma, as the very name implies. However, sometimes, a field itself might contain multiple values, that’s expected to be unraveled elsewhere.

For example,

1,2,"a;b;c",6,1000,0,0,"free form text"

The semi-colon ; would be a secondary field separator, as the third field is clearly intended to contain a simple list of values. This is all fairly simple when working with one source, and the standard is fixed. (If instead of a simple list of values, the inner-FS field is a multiline quoted field with inner quotes and more inner-FS’s, you’re looking at attempting to create something that amounts to complete madness, so we will stick to a simple inner-FS here)

But what happens if we work with multiple sources? How can we know that the first quoted field occurs at the column we expect? Does that field even contain the suspected secondary separator?

Detecting the secondary FS

A simple counting heuristic

Now there can be many approaches, the simplest one being a simple count of what you find in your file, just count up all the things you expect to be an FS or a secondary FS, and rank them:

{
    a[","] += gsub(/,/, ",");
    a[";"] += gsub(/;/, ",");
}
END{
    print ",", "appears", a[","]+0, "times";
    print ";", "appears", a[";"]+0, "times";
}

And for a simple csv this might work perfectly well:

root@linuxpc:/opt# for ((i=0;i<1001;i++)) do INNER=$(shuf -r -n $((RANDOM % 3 + 2)) -e {a..z} | awk '{ORS=";"}1' | sed 's/;$//'); echo
"1,2,\"$INNER\",6,1,1000,0,0,\"free form text\"" >> /tmp/example.csv; done
root@linuxpc:/opt# awk -f rank.awk /tmp/example.csv
, appears 8008 times
; appears 2058 times

However, if the free-form text is actually something that also includes the secondary FS in reasonably large numbers, it may throw off this sort of simple heuristic entirely!

root@linuxpc:/opt# for ((i=0;i<1001;i++)) do INNER=$(shuf -r -n $((RANDOM % 3 + 2)) -e {a..z} | awk '{ORS=";"}1' | sed 's/;$//'); INNER2=$(shuf -r -n $((RANDOM % 10 + 5)) -e {a..z} | awk '{ORS=";"}1' | sed 's/;$//');  echo "1,2,\"$INNER\",6,1,1000,0,0,\"free form text $INNER2\" >> /tmp/example2.csv; done
root@linuxpc:/opt# awk -f rank.awk /tmp/example2.csv
, appears 8008 times
; appears 10483 times

Now that “;” appears in sufficiently great numbers in a free form text column, the heuristic is completely thrown off and anything else that depends on this result would produce garbled output based on this wrong guess.

The other deficiency of this solution, is of course, the secondary separator being also ,. Our simple heuristic can’t tell the difference between “no secondary FS” and “secondary FS=FS”. In that case we would obviously need a different approach.

So what can we do?

Looking inside the quoted field

As we have seen, gsub() returns the number of substitutions made, so if we substitute , with ,, we do not change the text, but we do get a count back. However, we need to find a way to lock in, and count only inside the quoted field, not outside of it.

In our case, we can assume that the input format has a useful constraint: the column types and locations are locked in and static, so we can always be sure that the first quoted field is expected to be the column under investigation.

The trick is to split on quotes, not the commas, it will pull apart the csv line exactly in a way that the above gsub() check will no longer be misleading:

root@linuxpc:/opt# echo '1,2,"p;l;h",6,1,1000,0,0,"free form text"' | awk 'BEGIN{FS="\"}{print $1}'
1,2,
root@linuxpc:/opt# echo '1,2,"p;l;h",6,1,1000,0,0,"free form text"' | awk 'BEGIN{FS="\"}{print $2}'
p;l;h
root@linuxpc:/opt# echo '1,2,"p;l;h",6,1,1000,0,0,"free form text"' | awk 'BEGIN{FS="\"}{print $3}'
,6,1,1000,0,0,

As you can see above, this allows us to extract the field of interest cleanly.

A line with no quotes at all will still parse, since in that case $1 = the entire line, i.e. NF == 1, and the SECOND quoted column later in a file is absolutely no problem, they simply get parsed as $4 = between quotes text, $5 = second quoted field, etc. either way $1/$2/$3 are not affected by it.

This also handles the case where secondary FS=FS:

root@linuxpc:/opt# echo '1,2,"p,l,h",6,1,1000,0,0,"free form text"' | awk 'BEGIN{FS="\"}{print $1}'
1,2,
root@linuxpc:/opt# echo '1,2,"p,l,h",6,1,1000,0,0,"free form text"' | awk 'BEGIN{FS="\"}{print $2}'
p,l,h
root@linuxpc:/opt# echo '1,2,"p,l,h",6,1,1000,0,0,"free form text"' | awk 'BEGIN{FS="\"}{print $3}'
,6,1,1000,0,0,

This is extremely useful, because this way we can cleanly say that

commas_before_second_fs = gsub(/,/, ",", $1);

Since $1 is “everything before the first quote”, the count is exactly the number of real, unquoted field separator commas that came before it, i.e. which column number the quote is about to open at.

With this, knowledge, we can build a little function, to verify that whatever we guessed as the secondary FS really exists in $2:

function has_our_fs_guess() {

    COL_IDX=$1
    FIELD_SEP=$2
    CSV_FILE=$3

    awk -F'"' -v col=$COL_IDX -v fs=$FIELD_SEP '
        NR == 1 { next }
        {
            commas_before_second_fs = gsub(/,/, ",", $1);
            if (NF >= 3 && commas_before_second_fs == col - 1 && index($2, fs) > 0) {
                print "yes";
                exit;
            }
         }' $CSV_FILE
}

-> NF >= 3 : There is a quoted segment on this line at all, as an all unquoted line would produce $1, so NF == 1 would mean that there is nothing to check.
-> commas_before_second_fs == col - 1 : the quote we found opens exactly at the column of interest, e.g. commas_before_second_fs = 2 means we found two real commas before the column with inner FS. This is what really proves that the column we found here is the column of interest. (E.g. this col/$COL_IDX could come from reading the csv headers and regex matching the header text against an expected value)
-> index($2, fs) > 0 : does the actual extracted column of interest contain our guessed secondary field separator at all? This is the real question the function wants to answer. One yes is enough, then bail.

root@linuxpc:/opt# has_our_fs_guess 3 ";" /tmp/example.csv
yes

FS = Secondary FS Case

Detection is one thing, but how do we deal with the case where FS and secondary FS are really both the same character, e.g.

root@linuxpc:/opt# for ((i=0;i<1001;i++)) do INNER=$(shuf -r -n $((RANDOM % 3 + 2)) -e {a..z} | awk '{ORS=","}1' | sed 's/,$//'); echo "1,2,\"$INNER\",6,1,1000,0,0,\"free form text\" >> /tmp/fs_example.csv; done
# (normally the first line should be a real header, but I was a bit lazy for making one, so any non-transformation of line one is deliberate, if we _imagine_ that it is a real header)

The good news is that the above function holds up as expected, so detection is verifiably not a problem:

root@linuxpc:/opt# has_our_fs_guess 3 ";" /tmp/fs_example.csv
root@linuxpc:/opt# has_our_fs_guess 3 "," /tmp/fs_example.csv
yes

However, many scripts and applications do not handle the data extraction cleanly here, so we need to find a way to introduce a real secondary FS. The neat thing is that a lot of the logic from detection can be reused directly for a fix

function apply_fs_fix() {
    COL_IDX=$1
    NEW_FIELD_SEP=$2
    CSV_FILE=$3

    awk -F'"' -v col=$COL_IDX -v nfs=$NEW_FIELD_SEP '
        NR == 1 { print $0; next }
        {
            commas_before_second_fs = gsub(/,/, ",", $1);
            fixed=0;
            if (NF >= 3 && commas_before_second_fs == col - 1) {
                gsub(",", nfs, $2);
                fixed = 1;
            }
            out = $1;
            for (i=2; i <= NF; i++) {
                if(fixed && ( i == 2 || i == 3 )) {
                   out = out $i;
                } else {
                   out = out "\"" $i;
                }
            }
            print out;
         }' $CSV_FILE
}

Verifying:

root@linuxpc:/opt# sed 5q /tmp/fs_example.csv
1,2,"a,m,q,k",6,1,1000,0,0,"free form text"
1,2,"y,z,a,g",6,1,1000,0,0,"free form text"
1,2,"a,p",6,1,1000,0,0,"free form text"
1,2,"w,h,d,r",6,1,1000,0,0,"free form text"
1,2,"v,h,c",6,1,1000,0,0,"free form text"
root@linuxpc:/opt# apply_fs_fix 3 ";" /tmp/fs_example.csv | sed 5q
1,2,"a,m,q,k",6,1,1000,0,0,"free form text"
1,2,y;z;a;g,6,1,1000,0,0,"free form text"
1,2,a;p,6,1,1000,0,0,"free form text"
1,2,w;h;d;r,6,1,1000,0,0,"free form text"
1,2,v;h;c,6,1,1000,0,0,"free form text"

There are two reasons why this is useful:

-> now splitting on the original FS will no longer cause other functions later on in the script/pipeline to treat the inner FS the same as if it was the real FS, making further parsing much easier
-> the quotes are now no longer needed, so if there are more similar fields hypothetically, a similar check on them can be done too

Conclusion

CSV is one of the simplest file formats, but that’s also something quite deceptive about it. Once sources get creative with file-separators and secondary file-separators, nothing can be guaranteed and anything that may have started as a simple script can find itself ballooning to hundreds of lines of validation code. Here we haven’t even encountered fun and valid situations like multiline quoted fields, or quoted fields containing more quotes, so in essence this can get more or less infinitely complicated. The main takeaway is just to never assume CSV = simple, unless you also control the CSV production.