Why quotation marks break a CSV import
Learn why quotation marks confuse CSV imports, how to spot unmatched or wrongly escaped quotes, and how to repair a row without damaging valid text.
A CSV import can put values in the wrong columns, combine several rows, or stop with an error because of quotation marks. The confusing part is that quotes are also a normal part of CSV: they protect commas and line breaks that belong inside a value.
Find out whether a quote is missing, misplaced, or simply valid CSV punctuation before changing anything. The CSV Editor can help you inspect, find, and edit values in a file in your browser.
Check the CSV file
- Open the CSV Editor and open your file. If it displays the rows and columns correctly, use its search and editing features to locate the value that fails to import.
- Note which row and column look wrong in the importing app. Look for a value shifted into a neighbor column, a row that contains text from the next line, or an error that mentions an unclosed quote.
- Inspect the problem row as plain text, not only as a table. A basic text editor can show the commas and quotation marks exactly as they appear in the file. Make a copy before changing the original.
- Check how the importer treats the file. If it offers CSV options, confirm the delimiter is a comma and the text qualifier is a double quote, unless the file was created with different settings.
A CSV file stores rows of values separated by a delimiter, often a comma. Quotes tell an importer that a comma or line break is part of a value, not a boundary between fields or records.
What the quotation marks mean
A pair of quotes surrounds a value
This is usually correct. In 17,"Doe, Jane",Active, the quotes keep the comma in Doe, Jane inside one field. Removing them would make the importer treat Jane as another column.
Two quote marks represent one quote inside a value
In many common CSV formats, a literal double quote inside a quoted value is written twice. For example, 17,"She said ""yes""",Active represents the value She said "yes". A single quote mark inside the field may confuse an importer or make it read the rest of the row incorrectly.
A quote opens a field but never closes
In 17,"Doe, Jane,Active, the opening quote has no matching closing quote. An importer may treat later commas as part of the same value, or it may report an invalid row. If a line break follows, it might even treat the next row as part of that value.
A quote appears in an unquoted value
A value such as She said "yes" may be read differently by different importers if it is not enclosed and escaped consistently. If the quotation marks are meant to be part of the text, write the field as "She said ""yes""".
The file looks right but the import still fails
The importer may expect a different delimiter or quote character, or it may handle line breaks inside quoted fields differently. A well-formed CSV can still be interpreted incorrectly when the import settings do not match the file.
How to fix the CSV
- Keep an untouched copy. Save a duplicate before editing. If the file contains important records, compare the edited copy with the original before importing it again.
- Fix an unmatched quote. Find the value that begins with a quote and add the missing closing quote only if the value is meant to be quoted. Check the entire row afterward, since the missing mark may have made several later fields appear to be part of one value.
- Escape quotes inside text. If a field contains a quote as ordinary text, enclose the whole field in double quotes and double each quote inside it. For example, change
She said "yes"to"She said ""yes""". - Remove only accidental quote marks. If a quote was typed into the wrong place and is not intended to be part of the value, remove that mark. Do not strip every quote from the file: valid quotes may be protecting commas, line breaks, or literal quote marks.
- Match the import options to the file. Select comma as the delimiter and double quote as the text qualifier when those are the characters the file uses. If the file uses a different separator, choose that instead.
- Test a small sample first. Import a few corrected rows and check that each value lands in the intended column. Then import the full file and review row counts and any warnings.
If the CSV Editor can parse the file, use it to find and correct a value in the table. If the rows are already split incorrectly or the file will not open, inspect and repair the raw text in a text editor instead. A table view cannot always show whether the original punctuation was well formed.
Why valid quotes can still cause trouble
CSV is a simple format, but importers do not always make the same assumptions. A line break inside a quoted field can be valid, for example, but an importer that expects one record per line may split it incorrectly. A semicolon-separated file can also be mistaken for a comma-separated one.
Check how the file was exported and use matching import settings. If you do not know its format, test a copy and compare the result with a few rows in the original. The CSV Editor runs in your browser, and your files stay on your device.
