CSV import failing: encoding and delimiter checklist

TL;DR

CSV failures are never about the CSV being "broken," they are about your parser expecting one format and the file being another. The big three are the delimiter, the encoding, and the quoting, and you can diagnose all three from the first few lines of the file. Ask for a small sample with headers, not the whole 50 MB file.

The query

CSV import failing: encoding and delimiter checklist

Use this when

• An import errors out or imports zero rows
• Columns land in the wrong fields
• Special characters show as gibberish
• The file works in Excel but fails in your importer
• You are writing the import-troubleshooting macro

Not for

• API-based imports
• Files rejected for size limits
• Field mapping after a successful parse
• Export problems, which are the reverse path

Steps

1. Get a small sample with headers

Ask for the first 5 rows including the header row, pasted as text or as a tiny file. Never debug from the full file. The sample shows you the delimiter, the quoting, and the encoding problems all at once.

Expected output: a 5-row sample in hand.

2. Identify the actual delimiter

Look at the raw sample: commas, semicolons, tabs, or pipes. European exports use semicolons constantly because their decimals are commas. If your importer assumes commas and the file uses semicolons, every row collapses into one column.

Expected output: the real delimiter named, matched to the importer setting.

3. Check the encoding, starting with the BOM

If accented characters or symbols show as gibberish, the file is probably saved in a legacy encoding instead of UTF-8. Have them re-save as UTF-8 from their spreadsheet app, and make sure the importer reads the byte-order mark correctly instead of treating it as part of the first header name.

Expected output: clean characters after a UTF-8 re-save.

4. Check quoting around tricky fields

Fields containing the delimiter, line breaks, or quotes must be wrapped in double quotes, and inner quotes doubled. Addresses and notes fields break this constantly. One unquoted comma in a notes field shifts every column after it.

Expected output: the misquoted field found and fixed.

5. Validate row by row on the failing line

Importers usually report the failing row number. Look at that exact row in the sample: a stray line break inside a field, a missing column, or an extra delimiter. Fix that row and re-import. If rows fail randomly, suspect inconsistent quoting across the file.

Expected output: the bad row identified and corrected.

Ready-to-use message

CSV imports fail for boring reasons, so let's find yours.
Can you send me the first 5 rows of the file, including the
header row?

Three quick things to check on your end:
1. What separates the columns: commas, semicolons, or tabs?
   (European exports often use semicolons)
2. Re-save the file as UTF-8 from your spreadsheet app
   before uploading
3. If any cell contains a comma or a line break, make sure
   that cell is wrapped in double quotes

Send me the sample and I'll tell you exactly which one it is.

Variant phrasings

csv upload not working all columns in one

Step 2 on its own. That symptom is the delimiter, every time.

special characters garbled after csv import

Step 3. Encoding mismatch, fixable with a UTF-8 re-save.

csv import error on row 500

Step 5. Go straight to the reported row and look for quoting or column-count problems.

Why it happens

CSV is not really a standard, it is a family of lookalike formats, and every spreadsheet app exports a slightly different dialect. Your importer speaks one dialect, the user's file speaks another, and neither side complains clearly. The delimiter, encoding, and quoting are the three dials, and a mismatch on any one of them corrupts the whole import.

Edge cases

• File opens fine in Excel but fails import: Excel is lenient and your importer is strict. The file is still malformed, Excel just hides it.
• Mac versus Windows line endings: old Mac files use carriage returns alone, which some parsers read as one giant row. Re-saving normalizes this.
• Huge files that fail silently: chunk the file into smaller pieces to find whether it is size or a bad row hiding deep inside.
• Numbers losing leading zeros: zip codes and phone codes get mangled when a spreadsheet treats them as numbers before export. Format those columns as text first.
• Header names with the BOM glued on: the first column imports as something like "[BOM]name" and mapping fails. Strip it or re-save cleanly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_j7gC6E1JFwwm-iYktfScyw