Fix unescaped double quotes in a CSV file
CSV files break when a quote inside a field is not doubled. Here is why, how to fix it by hand, and a tool that repairs it in your browser.
RFC 4180, the closest thing CSV has to a standard, says a field that contains a double quote must be wrapped in quotes, and each quote inside it must be doubled. So He said "hi" is written "He said ""hi""". Plenty of exporters and scripts get this wrong.
Why one quote breaks the whole row
A parser that meets "Bob "The Builder" Smith" sees a quoted field that ends after Bob , then junk it does not expect. Some parsers stop with an error, some carry on and push the remaining text into the next columns, and some treat everything up to the next quote, possibly many rows later, as one field.
Fixing it by hand
- Find the quotes that sit inside a field rather than at its edges, and double them.
- Make sure any field that now contains a quote, a comma or a line break is wrapped in quotes.
- In code, read the file with a lenient parser and write it back with a proper CSV writer (Python's
csvmodule withquoting=csv.QUOTE_MINIMALdoes the writing side correctly).
What the tool does
It looks at each quote and decides from its position whether it opens or closes a field or is part of the text, doubles the text ones, and re-writes the file with standard quoting. Valid files come back unchanged, including correctly doubled quotes and quoted fields with commas or line breaks inside. Ambiguous cases are a guess, so look over the cells it reports as repaired.