🗄️ File Operations · 10 min read

CSV to MySQL: Why Rows Go Missing on Import (2026)

Start with an explicit FIELDS clause. LOAD DATA defaults to tab-separated input, so a comma-separated file loaded without one puts the whole line into the first column. If the error mentions local data, the server variable local_infile is off by default in current releases and the client needs to opt in too. After that, most remaining problems are type widths, encodings and warnings that nobody read. The four routes are below, followed by the failure list that accounts for nearly every half-loaded table.

The complaints in r/SQL and r/dataanalysis tend to be about a partial load rather than a refusal. One thread on importing CSV into MySQL contains a line that sums up the experience: “MySQL Workbench not importing all rows from csv”, followed by the equally common “best way to import csv file into Mysql”, where the accepted answer is that mysqlimport cuts the data and the wizard behaves differently again. The file is rarely the problem — the statement usually is.

The default that catches almost everyone

LOAD DATA is an old statement with old defaults, and the manual says so in as many words: with no FIELDS or LINES clause, the behaviour is the same as FIELDS TERMINATED BY '\t', ENCLOSED BY '', ESCAPED BY '\\' and LINES TERMINATED BY '\n'. Tab separated. So this fails:

-- Loads every line, intact, into the first column
LOAD DATA LOCAL INFILE '/tmp/customers.csv'
INTO TABLE customers;

And this works, because the file now describes itself to the parser:

LOAD DATA LOCAL INFILE '/tmp/customers.csv'
INTO TABLE customers
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@id, @name, @signup)
SET id     = NULLIF(@id, ''),
    name   = TRIM(@name),
    signup = STR_TO_DATE(@signup, '%Y-%m-%d');

Five of those clauses are doing real work. CHARACTER SET utf8mb4 stops the connection default from mangling accented names. OPTIONALLY ENCLOSED BY makes RFC 4180 quoting work, including a doubled quote inside a quoted field and a line break inside one. LINES TERMINATED BY '\r\n' matches a file exported on Windows, where a bare newline can leave a carriage return glued to the last column. IGNORE 1 LINES skips the header. The trailing SET block reads each field into a user variable first, so you can turn an empty string into a real NULL and parse the date explicitly instead of hoping MySQL guesses the format.

Why local loading is refused

LOAD DATA without LOCAL means the server opens the file, which requires the FILE privilege and a directory allowed by secure_file_priv. LOAD DATA LOCAL INFILE means your client reads the file and streams it, which needs neither, and is consequently the variant people reach for.

The manual states that local_infile is disabled by default and notes that this changed from earlier versions, so a server upgrade turns it off on its own. Both sides must agree: enabling the server variable while the client still refuses gives the same error. The client opt-in is what --local-infile=1 does when you start the mysql client, and MySQL Workbench 8.0 tightened the same behaviour, which is one reason the wizard started failing for people who had used it for years.

The reason for the caution is documented as well: with LOCAL, a server you do not control can request files from your machine, not just the one you named. On a shared or third-party database that is a real consideration rather than a theoretical one. On the database you run yourself, enabling it for the duration of the import and turning it off afterwards is a reasonable habit.

The four routes, and what each one is for

Two things are worth knowing about the parallel utility, since it is the least familiar option. It splits one file into chunks and loads them concurrently with LOAD DATA LOCAL INFILE underneath, so it is not a different engine, just several of the same one at once. It also means a file with a syntax error inside a quoted field can fail in a chunk rather than at the top, and the error message points at the chunk boundary rather than the bad line.

Create the table first, and read the header twice

LOAD DATA does not create tables for you, and this is where a lot of imports go wrong. A wizard that infers types from the first fifty rows will call a postcode a number and a money column a float. Declare the types deliberately:

CREATE TABLE customers (
  id        VARCHAR(20)  NOT NULL PRIMARY KEY,  -- text: keeps leading zeros
  name      VARCHAR(120) NOT NULL,
  signup    DATE         NULL,
  revenue   DECIMAL(12,2) NULL                 -- never FLOAT for money
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Identifiers are text even when they look numeric. A customer number of 0001234567 becomes 1234567 in an INT column, and an 18-digit reference number loses its last digits in a float. Money is DECIMAL, not FLOAT or DOUBLE, because binary floating point cannot represent 0.10 exactly and the rounding error accumulates over a column of thousands of rows. Declare the table with the column widths matching the longest value in the file, since a VARCHAR that is too short is a truncation warning today and a rejected row once strict mode is on.

If the CSV contains accented characters or anything outside the Latin-1 range, the table and the connection both need utf8mb4. That is four bytes per character, and it covers characters outside the basic multilingual plane — the older three-byte utf8 in MySQL could not store those. Picking the three-byte form today is how an import half-succeeds with question marks in the text and no error at all.

When rows go missing

The third row of that table is the expensive one. A row with fewer fields than the table has columns does not stop a LOAD DATA statement; it produces a warning and the row is skipped. Because the statement itself returns successfully, a script that checks only for an exception reports success. Compare SELECT COUNT(*) against the number of lines in the file, minus the header, after every import. When the two disagree, the warnings hold the reason.

The duplicate key case deserves a word too, because it interacts with the route you chose. A file reloaded after a failed attempt usually contains rows that did arrive the first time, so the second run hits the primary key. Loading into a staging table with no constraints and then inserting with a join is slower to write and much easier to re-run, which matters more than the extra minute when a partner sends a corrected file at 5pm.

Making a big import quicker

Three changes cover most of it. Load into a table without secondary indexes and add them afterwards, because every index has to be updated for every row. Use LOAD DATA rather than INSERT statements, since a statement that carries thousands of rows beats thousands of statements, and the client-side packet limit caps how many rows fit in one anyway. And if you must insert row by row, wrap the batch in one transaction instead of committing each row, which removes a disk flush per row.

Once the data is in, the questions usually change from how to load it to what it says. If the CSV was a one-off that will not be reloaded, it is often quicker to ask the question of the file directly with the CSV analyzer than to build a schema for it. Related ground on this site: CSV to SQL covers turning the file into INSERT statements for a database you cannot reach from the command line, querying a CSV with SQL skips MySQL entirely by using DuckDB or SQLite, and changing the delimiter is the fix when the separator is something other than a comma in the first place. For SQLite rather than MySQL, the .import route has no privileges to configure at all.