Quotes and commas inside CSV cells: find and fix

Values land in the wrong columns, and text from one cell spills into the next one. A single product row splits into several rows, or the row count looks wrong after import. Some rows look fine while others are shifted to the right. The example rows in this post are made up; they are not taken from a real file or from any real shop.

If one product turns into two rows and you see a line break in a description, start with "Line breaks inside a CSV cell: fix split rows" instead; that is a different cause with the same symptom.

Why it happens. A CSV file uses commas to separate columns. Two things confuse the reader. A comma inside a cell that is not wrapped in double quotes is treated as a column break. A double quote inside a cell that is not doubled can make the reader end the cell early or reject the row, so the rest of the text is read in the wrong place. A semicolon separator or a decimal comma in a price can shift columns in a similar way; to rule that out, see "Decimal comma or column separator in CSV prices?".

Find and fix. 1. Open the file in a plain text editor. A spreadsheet view can hide the raw commas and quotes. 2. Find cells that contain a comma (or your file's separator, such as a semicolon) and wrap the whole cell in double quotes. 3. Find cells that contain a double quote. Inside the cell, write each double quote as a pair of double quotes, then wrap the whole cell in double quotes. 4. If a cell has both a comma and a quote, do both: double the inner quotes and wrap the cell. The examples below show each case.

5. Check that every opening quote has a closing quote. A single missing closing quote can shift every row that follows it, and a quote that swallows the separators around it can leave cells looking empty; for that symptom, see "Empty cells in your product CSV: why and how to fix". 6. Save the file as CSV again, keeping the same encoding and separator you used before. Some import programs read quotes and separators more strictly or more loosely than others, so check your target system's own import rules too.

Recheck. Open the saved file as text again and compare a few rows with the original. Then run the free CSV check. It reads quotes the standard way: a comma or a line break inside double quotes stays in one cell, and a doubled quote counts as one quote character. It takes the column separator (comma, semicolon or tab) from the header row. It does not count the columns in each row, so it does not name a stray comma as the problem, and a quote in the middle of an unquoted cell is kept as ordinary text. If a quote is never closed, it stops with the message "A quoted value is not closed". If an unquoted comma pushes text into the price column, that row is flagged as "Price is not a plain number". A shift that happens only after the price column, a stray quote inside an already quoted cell, or two stray quotes that join rows together are not reported; the only hint of joined rows is that fewer rows were checked than you expected. It looks at the first 20 data rows only.

If the report still lists problems after these fixes, the header row is a separate cause to look at: see "CSV column headers that don't match the template".