title: How do I robustly parse malformed CSV?
body: <p>I'm processing data from government sources (FEC, state voter databases, etc). It's inconsistently malformed, which breaks my CSV parser in all sorts of delightful ways.</p>
  
  <p>It's externally sourced and authoritative. I must parse it, and I cannot have it re-input, validated on input, or the like. It is what it is; I don't control the input.</p>
  
  <p>Properties:</p>
  
  <ol>
  <li>Fields contain malformed UTF-8 (e.g. <code>Foo \xAB bar</code>)</li>
  <li>The first field of a line specifies the record type from a known set. Knowing the record type, you know how many fields there are and their respective data types, but not until you do.</li>
  <li>Any given line within a file might use quoted strings (<code>"foo",123,"bar"</code>) or unquoted (<code>foo,123,bar</code>). I haven't yet encountered any where it's mixed within a given line (i.e. <code>"foo",123,bar</code>) but it's probably in there.</li>
  <li>Strings may include internal newline, quote, and/or comma character(s).</li>
  <li>Strings may include comma separated numbers.</li>
  <li>Data files can be very large (millions of rows), so this needs to still be reasonably fast.</li>
  </ol>
  
  <p>I'm using Ruby FasterCSV (known as just CSV in 1.9), but the question should be language-agnostic.</p>
  
  <p>My guess is that a solution will require preprocessing substitution with unambiguous record separator / quote characters (eg ASCII RS, STX). I've started a bit <a href="https://github.com/JEG2/faster_csv/issues/16#issuecomment-6940019" rel="noreferrer">here</a> but it doesn't work for everything I get.</p>
  
  <p>How can I process this kind of dirty data robustly?</p>
  
  <p>ETA: Here's a simplified example of what may be in single file:</p>
  
  <pre>
  "this","is",123,"a","normal","line"
  "line","with "an" internal","quote"
  "short line","with
  an
  "internal quote", 1 comma and
  linebreaks"
  un "quot" ed,text,with,1,2,3,numbers
  "quoted","number","series","1,2,3"
  "invalid \xAB utf-8"
  </pre>