SQL Help

Transform data as you import it with LOAD DATA

Updated August 2026 · First published 2009 by David J. Lake

LOAD DATA is by far the fastest way to bulk-load a file into MySQL — and the part most people miss is that you can clean the data on the way in, instead of importing garbage and running UPDATEs afterwards. The trick is routing file columns through user variables.

The core pattern: capture, then SET

Suppose a CSV export uses UK-style dates MySQL won't accept into a DATETIME column:

17-01-2026,Widget,4.99
14-02-2026,Grommet,2.49
09-03-2026,Sprocket,7.00

CREATE TABLE purchases (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  bought_on  DATE,
  item       VARCHAR(50),
  price      DECIMAL(8,2)
);

LOAD DATA INFILE '/var/lib/mysql-files/purchases.csv'
INTO TABLE purchases
FIELDS TERMINATED BY ','
LINES  TERMINATED BY '\n'
(@bought_on, item, price)
SET bought_on = STR_TO_DATE(@bought_on, '%d-%m-%Y');

The column list (@bought_on, item, price) maps the file's columns in order. Plain names load directly; an @variable captures the raw value instead, and the SET clause computes the real column from it. Any expression works, on any number of columns:

(@bought_on, @item, @price)
SET bought_on = STR_TO_DATE(@bought_on, '%d-%m-%Y'),
    item      = TRIM(@item),
    price     = NULLIF(@price, '');   -- empty string → NULL, not 0.00

That last one matters more than it looks: CSVs represent "no value" as an empty string, which MySQL would otherwise coerce to 0 for numeric columns — see why real NULLs matter.

Columns in the wrong order? Just say so

The file's column order doesn't need to match the table's — the list describes the file, and names route values to the right table columns:

-- file:  price,item,date     table: (bought_on, item, price)
(@price, item, @bought_on)
SET price     = @price,
    bought_on = STR_TO_DATE(@bought_on, '%d-%m-%Y');

Skip a file column entirely by capturing it into a dummy variable and never using it. And if the file has a header line: IGNORE 1 LINES.

The two settings that block everyone

• secure_file_priv — server-side LOAD DATA INFILE only reads from the directory this variable names (check with SELECT @@secure_file_priv;). Files elsewhere → "The MySQL server is running with the --secure-file-priv option" error. Put the file in that directory, or…
• LOAD DATA LOCAL INFILE — reads the file from the client machine instead, ideal when you can't touch the server's disk (shared hosting, managed databases). It must be enabled on both ends: local_infile=1 on the server, and e.g. --local-infile=1 for the mysql client / allowLocalInfile in connectors.