Start with the Error Class
Import errors look chaotic until you sort them: file shape, encoding, types, or constraints. Fix the class, not just the line number.
| Class | Typical message | First move |
|---|---|---|
| Shape | extra columns / missing data | Check delimiter & quotes |
| Encoding | invalid byte sequence | Re-save UTF-8 |
| Types | invalid input syntax | Stage as text, inspect |
| Constraints | duplicate key / FK violation | Clean dupes / order tables |
| Permissions | secure_file_priv / permission denied | Use \copy or LOCAL |
Delimiter and Quoting Failures
The classic "everything landed in one column" or "column count mismatch."
- Semicolon CSV loaded with comma settings (or the reverse).
- Unescaped quotes breaking field boundaries.
- Embedded newlines in quoted fields confusing line counts.
- Tab-separated file saved as .csv.
Fix
Open the file in the Online CSV Editor, confirm the delimiter, re-export cleanly, and align LOAD DATA / COPY options (DELIMITER, QUOTE, ESCAPE).
Header Problems
Headers cause silent corruption when ignored.
- Header row loaded as data because HEADER/IGNORE 1 LINES was omitted.
- Title rows or blank lines above the real header.
- Column order differs from the column list in the load command.
- BOM on the first header name (\uFEFForder_id).
-- Postgres: enable header + BOM-safe workflows
COPY staging FROM 'file.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');Encoding Errors
PostgreSQL often says "invalid byte sequence for encoding UTF8." MySQL may insert replacement characters or reject rows depending on settings.
- Re-export from Excel as UTF-8 CSV.
- Set ENCODING 'UTF8' on COPY.
- Ensure connection character_set matches for MySQL.
- Strip or avoid BOMs unless required.
Type Conversion Errors
"Invalid input syntax for type date/numeric/boolean" means the CSV value does not match the column type.
| Bad CSV value | Typed column | Fix |
|---|---|---|
| 07/25/26 | date | Convert to YYYY-MM-DD |
| $1,200.00 | numeric | Strip $ and commas |
| Yes / No | boolean | Map to true/false |
| NOT NULL int | Provide default or reject row | |
| 00123 | integer | Use text column for IDs |
Stage as text
Load into text columns first, SELECT the offenders, clean, then cast into production types.
Constraint and Key Errors
The file parsed—but the database refused the write.
- Duplicate primary or unique keys in the CSV.
- Foreign key orphans (child before parent, or missing parents).
- CHECK constraints failing on enums/status codes.
- NOT NULL violations on empty fields.
Engine-Specific Gotchas
Know your loader’s personality.
MySQL LOAD DATA
secure_file_priv blocks server paths; use LOCAL INFILE if enabled. Windows vs Unix line endings matter. Escape rules differ from Postgres.
PostgreSQL COPY
Server COPY needs filesystem access; prefer \copy from clients. NULL vs empty string is controlled by NULL option. Force quote handling with FORCE_QUOTE when exporting.
Diagnostic Workflow
A repeatable path to green loads.
- Preview the CSV (delimiter, header, sample rows).
- Load into a text staging table.
- Run validation queries for types, dupes, nulls.
- Fix the file or mapping; do not only patch SQL.
- Promote and compare COUNT(*) to the source file line count (minus header).
Real-World Examples
Fixes that unblock teams quickly.
"Column count mismatch at line 4821"
An address field contained an unquoted comma. Re-export with proper quoting fixed the rest of the file.
"Duplicate entry for key PRIMARY"
Merged regional CSVs without de-duplicating. Deduped on order_id in staging before promote.
Dates became 0000-00-00
MySQL zero dates from unparsable strings. Normalized to ISO in CSV, then reloaded.
Common Mistakes
Habits that prolong outages.
- Retrying the same bad file into production repeatedly.
- Disabling FK checks permanently to "make it load."
- Editing CSV in Excel mid-debug and introducing new type changes.
- Ignoring warnings when SQL mode allows partial loads.
- No archive of the exact file that failed.
Best Practices
Prevention beats forensics.
- Always stage first.
- Keep a rejected-rows output for quarantine.
- Version load scripts with explicit delimiter/encoding options.
- Fail jobs on warning thresholds.
- Clean with Online CSV Editor / Excel to CSV before reload.
Why Use Convert CSV Online?
Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Inspect broken imports, fix delimiter and header issues, convert Excel sources, or generate SQL for smaller retries. Client-side workflows work on Windows, macOS, and Linux browsers.
See the bad rows
Tables make quoting and type problems obvious long before COPY prints line 50,000.
Conclusion
CSV database errors are mostly shape, encoding, types, or constraints. Classify the failure, stage as text, fix the file or map, then promote with verified counts.
FAQ
Why does my CSV import say column count mismatch?
Usually a wrong delimiter, an unescaped quote, or an embedded newline. Confirm quoting and delimiter, then reload into staging.
How do I fix invalid byte sequence UTF8 errors?
Re-save the CSV as UTF-8 and set ENCODING 'UTF8' on Postgres COPY. Check MySQL connection character sets too.
What causes invalid input syntax for type date?
Non-ISO date strings or Excel-serialized values. Normalize to YYYY-MM-DD before loading into date columns.
How do I handle duplicate key errors on import?
Find duplicates in staging with GROUP BY, dedupe or upsert according to business rules, then promote.
Why does LOAD DATA fail with permission errors?
Server path access may be blocked by secure_file_priv. Use LOAD DATA LOCAL INFILE or another client-side load path.
Should I load bad rows into production to save time?
No. Stage, quarantine rejects, fix the source, and promote only clean data.
References
Convert your CSV in the browser
Preview, clean, and convert CSV files free with Convert CSV Online—no installation and no account required for everyday conversions.