title: What's the most robust way to efficiently parse CSV using awk?
body: <p>The intent of this question is to provide a canonical answer.</p>
  <p>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:</p>
  <pre><code>$ cat file.csv
  &quot;rec1, fld1&quot;,,&quot;rec1&quot;&quot;,&quot;&quot;fld3.1
  &quot;&quot;,
  fld3.2&quot;,&quot;rec1
  fld4&quot;
  &quot;rec2, fld1.1
  
  fld1.2&quot;,&quot;rec2 fld2.1&quot;&quot;fld2.2&quot;&quot;fld2.3&quot;,&quot;&quot;,rec2 fld4
  &quot;&quot;&quot;&quot;&quot;&quot;,&quot;&quot;&quot;rec3,fld2&quot;&quot;&quot;,
  </code></pre>
  <p>What's the most robust way efficiently using awk to identify the separate records and fields:</p>
  <pre><code>Record 1:
      $1=&lt;rec1, fld1&gt;
      $2=&lt;&gt;
      $3=&lt;rec1&quot;,&quot;fld3.1
  &quot;,
  fld3.2&gt;
      $4=&lt;rec1
  fld4&gt;
  ----
  Record 2:
      $1=&lt;rec2, fld1.1
  
  fld1.2&gt;
      $2=&lt;rec2 fld2.1&quot;fld2.2&quot;fld2.3&gt;
      $3=&lt;&gt;
      $4=&lt;rec2 fld4&gt;
  ----
  Record 3:
      $1=&lt;&quot;&quot;&gt;
      $2=&lt;&quot;rec3,fld2&quot;&gt;
      $3=&lt;&gt;
  ----
  </code></pre>
  <p>so it can be used as those records and fields internally by the rest of the awk script.</p>
  <p>A valid CSV would be one that conforms to <a href="https://www.rfc-editor.org/rfc/rfc4180" rel="nofollow noreferrer">RFC 4180</a> or can be generated by MS-Excel.</p>
  <p>The solution must tolerate the end of record just being LF (<code>\n</code>) as is typical for UNIX files rather than CRLF (<code>\r\n</code>) 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 <code>&quot;</code>s with a preceding backslash (i.e. <code>\&quot;</code> instead of <code>&quot;&quot;</code>) as some other CSV formats allow - if you have that then adding a <code>gsub(/\\&quot;/,&quot;\&quot;\&quot;&quot;)</code> up front would handle it and trying to handle both escaping mechanisms automatically in one script would make the script unnecessarily fragile and complicated.</p>