Mapping CSV Columns to Table Columns With LOAD DATA

A CSV rarely has the same columns as the table you want. It has more, in a different order, with dates in the wrong format and empty strings where you want NULL. LOAD DATA can sort all of that out in one statement, which is faster and clearer than loading into a staging table and moving the data. It does it with three features, a column list, user variables and a SET clause, and with one hazard that gives no warning, which is the order of the list. This article uses all of them on a 2,000,000 row file. The context is in the MySQL import guide.

The mechanism

After the table name, in parentheses, LOAD DATA takes a list that describes the file's fields in order. Each item is either a column of the table, in which case the field is stored there, or a user variable, which starts with @, in which case the field is read into the variable and dropped unless you use it. After the list comes an optional SET clause that assigns columns from expressions, which can use the variables. The statement without a list assumes the file's fields are the table's columns in table order, which is a fine default for a file that you generated and a risky one for a file you received.

The LOAD DATA reference calls this the column list and the SET clause, and it is dense, so here is a worked example of each use.

Skipping fields and computing others

The orders file has eight fields: order_id, customer_id, zip, amount, created_at, status, country, note. I want only three of them, plus a column of cents that the file does not contain and a load timestamp that the table fills itself:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
DROP TABLE IF EXISTS slim;
CREATE TABLE slim (order_id INT, amount DECIMAL(10,2), country CHAR(2), loaded_at DATETIME DEFAULT CURRENT_TIMESTAMP, amount_cents INT);
LOAD DATA LOCAL INFILE 'orders-2m.csv' INTO TABLE slim
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES
  (order_id, @customer, @zip, amount, @created, @status, country, @note)
  SET amount_cents = ROUND(amount * 100);
SELECT COUNT(*) AS rows_loaded, MIN(order_id), MAX(order_id), SUM(amount) AS total, SUM(amount_cents) AS cents FROM slim;
SELECT order_id, amount, country, amount_cents, loaded_at IS NOT NULL AS has_loaded_at FROM slim LIMIT 3;
SQL

rows_loaded	MIN(order_id)	MAX(order_id)	total	cents
2000000	1	2000000	160174646.36	16017464636
order_id	amount	country	amount_cents	has_loaded_at
1	103.10	GB	10310	1
2	31.59	BR	3159	1
3	89.98	JP	8998	1

The list (order_id, @customer, @zip, amount, @created, @status, country, @note) maps field 1 to order_id, field 4 to amount and field 7 to country, and parks the other five in variables that nothing reads. SET amount_cents = ROUND(amount * 100) computes a column from one that was just read. The table's loaded_at column has DEFAULT CURRENT_TIMESTAMP and is not mentioned, so it took its default for every row, as has_loaded_at shows.

The load put all 2,000,000 rows in, and the sums agree: 160174646.36 in amount and 16017464636 in amount_cents, which is the same figure times a hundred. That cross check, a computed column against its source, is one I add whenever I use SET, since it catches a typo in the expression. It is also the way to confirm the load against the file: the total of 160174646.36 is the figure that the PostgreSQL load and DuckDB computed from the same file.

Converting values on the way in

The SET clause takes any expression, which makes it the place for the fixes that a CSV always needs. These two are the most common: converting an empty string to NULL, and parsing a non-ISO date. The file has US style dates, 01/15/2025, and empty balances:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'customers-us-dates.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
  (id, name, @e, @d, @b)
  SET email = NULLIF(@e, ''), signup_date = STR_TO_DATE(@d, '%m/%d/%Y'), balance = NULLIF(@b, '');
SELECT id, name, signup_date, balance FROM customers ORDER BY id;
SQL

id	name	signup_date	balance
1	Ana Silva	2025-01-15	120.50
2	Smith, Jane	2025-02-03	0.00
3	Bo Chen	2025-02-20	NULL
4	Cy Dubois	2025-03-01	15.75

STR_TO_DATE(@d, '%m/%d/%Y') parsed the dates into real DATE values, so row 1 is 2025-01-15, and NULLIF(@b, '') made Bo Chen's balance NULL instead of 0.00 with a warning. The format string uses %m for month, %d for day and %Y for a four digit year, as the date format specifiers in the manual list. If a value does not match the format, STR_TO_DATE returns NULL and issues a warning, which is a reason to compare the count of non-null dates with the count of non-empty input afterwards. The full set of date failures is in fixing incorrect date value errors.

Other conversions follow the same shape. Trim padding with TRIM(@name), normalize case with UPPER(@code), strip a currency symbol and thousands separator with REPLACE(REPLACE(@amt, '$', ''), ',', ''), and turn a text flag into a number with CASE @flag WHEN 'yes' THEN 1 ELSE 0 END. Everything runs per row on the server, which adds time proportional to the work, so measure before you pile expressions on a very large file.

The silent shift

The hazard is that the list is checked against nothing. MySQL does not know what the file's fields mean and accepts any list of the right syntax. If the list is shorter than the file, the remaining fields are ignored, and the fields you listed take values from the first positions. I loaded the orders file into the three column table with the list (order_id, amount, country), which looks right and is not:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
TRUNCATE slim;
LOAD DATA LOCAL INFILE 'orders-2m.csv' INTO TABLE slim
  FIELDS TERMINATED BY ',' IGNORE 1 LINES (order_id, amount, country) ;
SHOW WARNINGS LIMIT 3;
SELECT order_id, amount, country FROM slim LIMIT 2;
SQL

Level	Code	Message
Warning	1265	Data truncated for column 'country' at row 1
Warning	1262	Row 1 was truncated; it contained more data than there were input columns
Warning	1265	Data truncated for column 'country' at row 2
order_id	amount	country
1	22313.00	99
2	193489.00	34

Field 2 of the file is customer_id, so amount received 22313.00 where the true amount is 103.10. Field 3 is zip, so country received 99 from the ZIP code 99154, clipped to two characters. The statement printed warnings, Data truncated for column 'country' at row 1 and Row 1 was truncated; it contained more data than there were input columns, and carried on, since a local load downgrades them. Everything in the table is wrong and the load "succeeded".

Three defenses. Always list every field of the file, with variables for the ones you do not want, so the list's length is the file's width. Check the number of fields with a strict CSV tool before loading, qsv headers or the width check from the PostgreSQL error article. And look at the warnings, treating Row 1 was truncated as a hard failure.

The header does not map anything

Nothing in this machinery uses the header's names. IGNORE 1 LINES skips it, as in the header article, and the order of the list is the whole mapping. That is a contrast with systems that load by header name, such as pandas and DuckDB, and it is the reason a source that reorders its columns breaks a MySQL load without any error. I compare the file's header with the expected list in the shell before the load. A file whose header differs should not be loaded, and the comparison costs one line.

How to tell it worked

Compare the loaded row count with qsv count on the file, and the sum of a numeric column with the sum from the file computed by another tool; here both gave 160174646.36. Select the first few rows and compare them with the first lines of the file by eye, which is what catches the shift above. Check the computed columns against their sources. And read SHOW WARNINGS after the load in the same session.

What column mapping does not protect you from

It does not reject out of range values in a local load, so a conversion that overflows still becomes a boundary value with a warning. It does not validate that @d is a date before STR_TO_DATE runs, and a bad one yields NULL. It cannot reorder by name. And it cannot do anything that needs more than one row, such as deduplication or looking up a code in another table; do those after loading, in SQL. If the transformation grows beyond a few expressions, move it out of the statement: load the raw text into a VARCHAR staging table and transform with INSERT ... SELECT, where each step can be run and checked separately.