What's the most robust way to efficiently parse CSV using awk?

user_1745001 0
Viewed 13066

The intent of this question is to provide a canonical answer.

Given a CSV as might be generated by Excel or other tools with embedded newlines and/or double quotes and/or commas in fields, and empty fields like:

$ cat file.csv
"rec1, fld1",,"rec1"",""fld3.1
"",
fld3.2","rec1
fld4"
"rec2, fld1.1

fld1.2","rec2 fld2.1""fld2.2""fld2.3","",rec2 fld4
"""""","""rec3,fld2""",

What's the most robust way efficiently using awk to identify the separate records and fields:

Record 1:
    $1=<rec1, fld1>
    $2=<>
    $3=<rec1","fld3.1
",
fld3.2>
    $4=<rec1
fld4>
----
Record 2:
    $1=<rec2, fld1.1

fld1.2>
    $2=<rec2 fld2.1"fld2.2"fld2.3>
    $3=<>
    $4=<rec2 fld4>
----
Record 3:
    $1=<"">
    $2=<"rec3,fld2">
    $3=<>
----

so it can be used as those records and fields internally by the rest of the awk script.

A valid CSV would be one that conforms to RFC 4180 or can be generated by MS-Excel.

The solution must tolerate the end of record just being LF (\n) as is typical for UNIX files rather than CRLF (\r\n) as that standard requires and Excel or other Windows tools would generate. It will also tolerate unquoted fields mixed with quoted fields. It will specifically not need to tolerate escaping "s with a preceding backslash (i.e. \" instead of "") as some other CSV formats allow - if you have that then adding a gsub(/\\"/,"\"\"") up front would handle it and trying to handle both escaping mechanisms automatically in one script would make the script unnecessarily fragile and complicated.

csv awk
5 Answers

An improvement upon @EdMorton's FPAT solution, which should be able to handle double-quotes(") escaped by doubling ("" -- as allowed by the CSV standard).

gawk -v FPAT='[^,]*|("[^"]*")+' ...

This STILL

  1. isn't able to handle newlines inside quoted fields, which are perfectly legit in standard CSV files.

  2. assumes GNU awk (gawk), a standard awk won't do.

Example:

$ echo 'a,,"","y""ck","""x,y,z"," ",12' |
gawk -v OFS='|' -v FPAT='[^,]*|("[^"]*")+' '{$1=$1}1'
a||""|"y""ck"|"""x,y,z"|" "|12

$ echo 'a,,"","y""ck","""x,y,z"," ",12' |
gawk -v FPAT='[^,]*|("[^"]*")+' '{
  for(i=1; i<=NF;i++){
    if($i~/"/){ $i = substr($i, 2, length($i)-2); gsub(/""/,"\"", $i) }
    print "<"$i">"
  }
}'
<a>
<>
<>
<y"ck>
<"x,y,z>
< >
<12>
user_103065030

This is exactly what csvquote is for - it makes things simple for awk and other command line data processing tools.

Some things are difficult to express in awk. Instead of running a single awk command and trying to get awk to handle the quoted fields with embedded commas and newlines, the data gets prepared for awk by csvquote, so that awk can always interpret the commas and newlines it finds as field separators and record separators. This makes the awk part of the pipeline simpler. Once awk is done with the data, it goes back through csvquote -u to restore the embedded commas and newlines inside quoted fields.

csvquote file.csv | awk -f my_awk_script | csvquote -u

EDIT:

For a complete description on csvquote, see: How it works. this also explains the `` characters which are shown in places where there was a carriage return.

csvquote file.csv | awk -f decsv.awk | csvquote -u

(for the source of decsv.awk see answer from Ed Morton ) outut:

Record 1:
    $1=<rec1 fld1>
    $2=<>
    $3=<rec1","fld3.1",
fld3.2>
    $4=<rec1
fld4>
----
Record 2:
    $1=<rec2, fld1.1

fld1.2>
    $2=<rec2 fld2.1"fld2.2"fld2.3>
    $3=<>
    $4=<rec2 fld4>
----
Record 3:
    $1=<"">
    $2=<"rec3fld2">
    $3=<>
----
user_23506560

I have found csvkit a really useful toolkit to handle with csv files in command line.

line='test,t2,t3,"t5,"'
echo $line | csvcut -c 4
"t5,"

echo 'foo,"field,""with"",commas",bar'  | csvcut -c 3
bar

It also contains csvstat, csvstack etc. tools which are also very handy.

cat file.csv
"rec1, fld1",,"rec1"",""fld3.1
"",
fld3.2","rec1
fld4"
"rec2, fld1.1

fld1.2","rec2 fld2.1""fld2.2""fld2.3","",rec2 fld4
"""""","""rec3,fld2""",


csvcut -c 1 file.csv

"rec1, fld1"
"rec2, fld1.1

fld1.2"
""""""

csvcut -c 3 file.csv
"rec1"",""fld3.1
"",
fld3.2"
""
""
user_25675120

Awk (gawk) actually provides extensions, one of which being csv processing, which is the most robust way to do so with gawk in my opinion. The extension takes care of many gotchas and parses the csv for you.

Assuming that extension is installed, you can use awk to show all lines where a specific csv field matches 123.

Assuming test.csv contains the following:

Name,Phone
"Woo, John",425-555-1212
"James T. Kirk",123

The following will print all lines where the Phone (aka the second field) is equal to 123:

gawk -l csv 'csvsplit($0,a) && a[2] == 123 {print a[1]}'

The output is:

James T. Kirk

How does it work?

  • -l csv asks gawk to load the csv extension by looking for it in $AWKLIBPATH;
  • csvsplit($0, a) splits the current line, and stores each field into a new array named a
  • && a[2] == 123 checks that the second field is 123
  • if both conditions are true, it { print a[1] }, aka prints first csv field of the line.
user_14434180
Related