The Missing Quote Issue

One of the most common parsing problems in CSV and other tabular data files is a missing text qualifier, such as a double quote in a quoted field. This issue can occur in any delimited text format that supports text-qualified fields, including CSV, TSV, pipe-delimited, semicolon-delimited, and custom-delimited files. The most common causes of missing quotes are truncated or improperly merged files, copy-paste or editing mistakes, and data imported from external systems.


The problem becomes especially tricky when the parser allows multiline fields, because the parser will continue reading additional lines (rows) while searching for the missing closing quote. Just one missing quote can cause havoc, including merged rows, incorrect column counts, shifted data, and incorrect parsing of the remaining file contents.


For example, consider the following CSV with double quotes as text qualifiers:

 Name,Address,Notes
 John,"123 Main St, Apt. 4B,Abc
 Jane,98 High Rd,Xyz

In this case, John's address field starts with a double quote but does not end with one. A parser that supports multiline fields may incorrectly treat 'Jane,98 High Rd,Xyz' as part of the same field instead of a new row. To fix this one would need to edit the file and insert the missing quote at the right place.


Corrected version:

 Name,Address,Notes
 John,"123 Main St, Apt. 4B",Abc
 Jane,98 High Rd,Xyz


How to Fix It with Delimit

When Delimit detects a missing quote it will stop parsing the file and show an error message. The message will also indicate on which row the error likely exists. You need to inspect that row (potentially neighboring rows also) and make sure every opening quote has a matching closing quote.


Step-by-step instructions:

  1. Close the error message
  2. From the top menu select 'Set Delimiter'
  3. Set Type to: 'Not delimited (show file as is)', and select OK
  4. From the top menu select go to: 'Cell' and enter/paste the row number in question
  5. Find which field has the missing quote and insert it at the right place, then save the file
  6. From the top menu select 'Set Delimiter' again
  7. Set Type to either: 'Auto assign' or 'Character delimited' and specify which delimiter and text qualifier to use,
    then select OK