ruby - How do I robustly parse malformed CSV?

Question

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.

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.

Properties:

1. Fields contain malformed UTF-8 (e.g. Foo xAB bar)
2. 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.
3. Any given line within a file might use quoted strings ("foo",123,"bar") or unquoted (foo,123,bar). I haven't yet encountered any where it's mixed within a given line (i.e. "foo",123,bar) but it's probably in there.
4. Strings may include internal newline, quote, and/or comma character(s).
5. Strings may include comma separated numbers.
6. Data files can be very large (millions of rows), so this needs to still be reasonably fast.

I'm using Ruby FasterCSV (known as just CSV in 1.9), but the question should be language-agnostic.

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 here but it doesn't work for everything I get.

How can I process this kind of dirty data robustly?

ETA: Here's a simplified example of what may be in single file:

"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"

Answer 1

It is possible to subclass Ruby's File to process each line of the the CSV file before it is passed to the Ruby's CSV parser. For example, here's how I used this trick to replace non-standard backslash-escaped quotes " with standard double-quotes ""

class MyFile < File
  def gets(*args)
    line = super
    if line != nil
      line.gsub!('"','""')  # fix the " that would otherwise cause a parse error
    end
    line
  end
end

infile = MyFile.open(filename)
incsv = CSV.new(infile)

while row = incsv.shift
  # process each row here
end

You could in principle do all sorts of additional processing, e.g. UTF-8 cleanups. The nice thing about this approach is you handle the file on a line by line basis, so you don't need to load it all into memory or create an intermediate file.