Shopify CSV "Illegal quoting on line": How to Fix It

Shopify's product importer stops with one of these two messages when it can't work out where a cell starts and ends:

• Illegal quoting on line 12
• Missing or stray quote on line 12

Both point at a double quote, or a character Shopify can't read, on the line the message names.

Why does Shopify say "Illegal quoting on line"?

A CSV uses double quotes to wrap a cell that contains a comma or a line break. A cell that holds a quote of its own has to double it, so the description 55" screen is written "55"" screen" in a correct CSV file. When Shopify's importer meets a quote that breaks this rule, it can't tell which characters belong to which cell, so it stops rather than guessing.

Shopify's common import issues page gives three causes:

1. The file isn't UTF-8. "Illegal quoting" can come from an illegal character, and Shopify's fix is to make sure the CSV is UTF-8 encoded. Files saved from Excel on Windows are often in Windows-1252 instead.
2. Curly quotes. Excel and other spreadsheet programs can turn straight quotes (") into curly ones (“ ”), and Shopify's help says they need to be changed back to straight quotes.
3. A stray quote. An extra quote somewhere in a field. In product files this is often an inch mark in a title or description.

How do I fix "Missing or stray quote on line"?

1. Open the file in a text editor, not Excel. Excel hides the quotes you need to see. VS Code, Notepad++ or TextEdit in plain-text mode all work.
2. Go to the line in the error. Most editors show line numbers on the left. If nothing looks wrong there, check the line above: a quote left open on one line makes the next line look broken.
3. Replace curly quotes with straight ones. Use find and replace: “ to ", then ” to ". Shopify's help gives this exact fix.
4. Remove or double any stray quote. If a cell is wrapped in quotes, every quote inside it must be written twice: "Monitor 27"" IPS". If the cell isn't wrapped, either wrap it and double the inner quote, or write the measurement another way (27 in).
5. Save as UTF-8. In VS Code, click the encoding in the bottom bar and choose Save with Encoding, then UTF-8. In Notepad++, use Encoding, then Convert to UTF-8, and save.
6. Import again. If Shopify names a different line, repeat steps 2 to 4 for that line.

A file with hundreds of inch marks and quoted product names can take several rounds of this. When the file comes from a supplier, you'll be doing it again for their next file.

How does Csvio handle curly and stray quotes?

Csvio is a Shopify app that imports supplier files into your store. Its CSV reader is more forgiving about quotes than Shopify's importer:

• Curly quotes are kept as text. “Soft” cotton imports as “Soft” cotton. You don't need to replace them.
• A quote inside a cell is kept as text. A title like TV 55" screen comes through with its inch mark, whether or not the cell is wrapped in quotes.
• Windows-1252 files are converted as they're read. Csvio turns bytes that aren't valid UTF-8 into the Windows-1252 characters they stand for, so accents, curly quotes and inch marks from an old Excel export come through instead of turning into garbage characters. A file that is already UTF-8 is read unchanged.
• You can skip CSV altogether. Csvio also imports .xlsx workbooks and PDF price lists. A workbook stores each cell separately, so quoting can't break it. If your supplier sends Excel, upload the workbook instead of converting it to CSV. (Older .xls files need to be saved as .xlsx first.)

One case still needs a fix by hand. If a cell opens with a quote that is never closed, everything after it in the file runs into that one cell. Csvio's import summary then shows fewer products than your file has, or, when that cell grows past 64 KB, a message saying a cell holds more than 64 KB of text. Either way, find the line where the products stop and close or remove the quote, as in step 4 above.

The other formatting problems Shopify's importer is strict about are covered in CSV formatting issues and supported file formats.

Can I try Csvio on the file Shopify rejected?

Csvio's free plan imports 30 products a month across up to 3 imports, which is enough to check whether it reads the file Shopify rejected. Upload the file as it is, check the preview, and nothing reaches your store until you confirm.