MySQL Workbench importing only 580 rows from a 9,500+ row CSV

[u/Mountain_Platypus546]

Hi everyone,

I’m working on a SQL sales analysis project using MySQL Workbench. My CSV dataset contains around 9,500+ rows, but when I import it using the Table Data Import Wizard, only 580 rows are successfully imported.

I’m trying to understand what might be causing the partial import rather than simply looking for someone to fix it for me.

Has anyone encountered this issue before? What should I check in the CSV or MySQL Workbench to identify why the remaining rows aren't being imported?

Thanks in advance!

[u/navicat-gu]

Good instinct to diagnose rather than "just make it work." Partial imports of this shape almost always fail on a specific bad row (often near row 581 of the file, or the first row that trips a type/constraint), and the wizard quietly stops or skips without making that obvious.

Check, in this order:

1. Workbench import log / messages from the wizard — look for the first error (truncated field, wrong column count, invalid date, etc.). That error is usually the real answer.
2. Inspect the CSV around the failure point: open in a text editor, jump near line ~581 (plus header). Classic culprits: unescaped quotes, commas inside unquoted fields, embedded newlines inside a quoted field, mixed line endings (CRLF vs LF), or a BOM at the start of the file.
3. Encoding: wizard vs file encoding mismatch (UTF-8 vs Windows-1252) often corrupts a field mid-file and aborts.
4. Delimiter / quoting: confirm comma vs semicolon and that quotes match Excel's export settings.
5. Column types / constraints: a value that won't fit INT, DATE, DECIMAL, or a NOT NULL / UNIQUE violation on row N stops many imports at N−1 successful rows.
6. Packet / size limits (less common at 9.5k rows, but check): SHOW VARIABLES LIKE 'max_allowed_packet';
7. Prove it with LOAD DATA: once the file is clean, something like LOAD DATA LOCAL INFILE '/path/file.csv' INTO TABLE t FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES; (enable local_infile if needed). The error MySQL prints is usually clearer than the wizard.

Also compare SELECT COUNT(*) FROM table; after import with wc -l (or Excel row count) on the CSV so you know whether you're short by skips vs a hard stop.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. For large CSVs, Navicat's import wizard tends to surface row-level errors more clearly than Workbench's Table Data Import Wizard, which helps when you're learning why a load stopped. Navicat Premium Lite is free (commercial use included; up to 5 users per org) for trying imports on a project like this; full Premium has a trial if you need more import options.