How to Import a CSV File Into MySQL

Three reliable ways to import a CSV file into MySQL: LOAD DATA INFILE (server-side, fastest, requires the FILE privilege), LOAD DATA LOCAL INFILE (client-side, works from any machine you can run mysql on), and mysqlimport (a shell wrapper around LOAD DATA INFILE with conventions for the table name). The right one depends on where the file lives and which privileges you have. I'll walk all three plus the four CSV-format gotchas that make 90% of "the import failed" tickets, including the MySQL Shell util.importTable() parallel loader that has become the fastest option on MySQL 8.0 and 8.4 since it landed.

The walkthrough uses a products table, the kind of import that comes up when migrating an e-commerce catalogue between systems or loading a vendor's stock feed.

CREATE TABLE products (
  sku           VARCHAR(40)  PRIMARY KEY,
  name          VARCHAR(200) NOT NULL,
  price_cents   INT UNSIGNED NOT NULL,
  in_stock      TINYINT      NOT NULL DEFAULT 1,
  category      VARCHAR(60)  NOT NULL,
  description   TEXT,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Jump to:

• How do I import a CSV into MySQL?
• Method 1: LOAD DATA INFILE (server-side, fastest)
• Method 2: LOAD DATA LOCAL INFILE (client-side, no FILE privilege)
• Method 3: mysqlimport (CLI wrapper)
• Method 4: MySQL Shell util.importTable (parallel, 8.0+)
• Skipping the header row
• Handling character encoding
• When the CSV does not match the table
• FAQ

How do I import a CSV into MySQL?

The shortest correct path on MySQL 8.0 or 8.4 is LOAD DATA LOCAL INFILE from the mysql client with --local-infile=1, because it does not need the server-side FILE privilege and works against managed databases (RDS, Cloud SQL, Aurora). Connect with mysql --local-infile=1 -u user -p mydb, then run LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;. The OPTIONALLY ENCLOSED BY clause is what makes commas inside quoted fields import correctly; IGNORE 1 LINES skips the header. If local_infile is off on the server (the default since MySQL 8.0), enable it with SET GLOBAL local_infile = 1;. For repeated large imports on MySQL 8.0+, MySQL Shell's util.importTable() parallel loader is faster than any of the SQL methods.

Method 1: LOAD DATA INFILE (server-side, fastest)

The fastest way to load a CSV. The server reads the file directly from its own disk; no data streams through the network or the client. Requires the FILE privilege on the user account and the file path inside secure_file_priv.

LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"'
     ESCAPED BY '\\'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES;

Reading the syntax left to right:

• FIELDS TERMINATED BY ',' — the column separator. Use '\t' for tab-separated, ';' for European-style CSV.
• OPTIONALLY ENCLOSED BY '"' — strips surrounding double-quotes from fields and preserves any commas inside them. This is what makes "Hello, World" import as a single field rather than two.
• LINES TERMINATED BY '\n' — record separator. On Windows-generated CSVs, use '\r\n' instead, or strip the carriage returns with tr -d '\r' < input.csv > clean.csv first.
• IGNORE 1 LINES — skips the header row. Drop this clause if your CSV has no header.

On managed databases (RDS, Cloud SQL, Aurora) the FILE privilege is not granted, so Method 1 fails immediately. Use Method 2 instead.

Method 2: LOAD DATA LOCAL INFILE (client-side, no FILE privilege)

LOAD DATA LOCAL INFILE reads the file from your local machine and streams it to the server. Slower than Method 1 because the data crosses the network, but it does not need the FILE privilege.

LOAD DATA LOCAL INFILE '/Users/me/Downloads/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES;

Two server-side flags have to be on for this to work:

1. local_infile=ON in the server config (SHOW VARIABLES LIKE 'local_infile'; to check). Default is OFF on every MySQL 8.0, 8.4, and 9.x release. This is the single most common cause of "The used command is not allowed with this MySQL version" on a fresh server.
2. --local-infile=1 on the mysql client when connecting:

mysql --local-infile=1 -u root -p mydb

Both have to be on. If either is missing you get "The used command is not allowed with this MySQL version". Most managed databases enable local_infile by default and the client-side flag is the only thing you need to set.

Method 3: mysqlimport (CLI wrapper)

mysqlimport is a shell command that wraps LOAD DATA INFILE with one convention: the table name comes from the filename. A file called products.csv imports into a table called products.

mysqlimport -u root -p \
--fields-terminated-by=',' \
--fields-optionally-enclosed-by='"' \
--lines-terminated-by='\n' \
--ignore-lines=1 \
--local \
myshop /path/to/products.csv

--local makes it use the LOCAL variant (Method 2 mechanics), so no FILE privilege is needed and the file is read from the client side.

When this shines: scripted nightly imports of several tables. Drop a directory full of *.csv files, pass them all to mysqlimport in one invocation, and each file lands in its own correspondingly-named table.

Method 4: MySQL Shell util.importTable (parallel, 8.0+)

MySQL Shell (mysqlsh) ships a Python/JavaScript-mode utility called util.importTable() that loads CSV in parallel across multiple connections. On MySQL 8.0, 8.4, and 9.0, this is the fastest way to load a large CSV into a single table — typically 3 to 8 times faster than LOAD DATA LOCAL INFILE for files over a few hundred megabytes.

mysqlsh --uri root@localhost --sql -e "SET GLOBAL local_infile = 1;"

mysqlsh root@localhost -- util import-table \
'/Users/me/Downloads/products.csv' \
--schema=myshop \
--table=products \
--dialect=csv-unix \
--skipRows=1 \
--threads=8

Read the flags:

• --dialect=csv-unix picks the right combination of FIELDS TERMINATED BY, enclosure, and line terminator for a standard Unix CSV. Other presets include csv (RFC 4180), tsv, and json. Override individual settings with --fieldsTerminatedBy, --fieldsOptionallyEnclosedBy, etc.
• --skipRows=1 is the equivalent of IGNORE 1 LINES.
• --threads=8 controls parallelism. The utility splits the file into chunks and loads each chunk on its own connection. Eight is a reasonable default for a developer laptop; tune up to the number of cores on a dedicated import host.

util.importTable() also accepts a directory or wildcard, so the "directory of CSVs" pattern from mysqlimport works here too, with parallelism on top.

Comparison: which import method to pick

Skipping the header row

Almost every CSV has a header row with column names, and LOAD DATA will happily try to insert it as data unless you skip it. The IGNORE 1 LINES clause is what does this.

For multi-line headers (rare but it happens with vendor feeds that include a license blurb at the top), bump the number: IGNORE 4 LINES skips the first four lines.

Handling character encoding

If your CSV is in UTF-8 (with or without a byte-order-mark), tell MySQL explicitly:

LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES;

If your CSV came from Excel on Windows, it might be in latin1 (Windows-1252) instead of UTF-8. Set CHARACTER SET latin1 and MySQL transcodes to whatever the table's column charset is.

If you see ? characters where European or Asian characters should be, the source file's encoding is being read wrong. Check the file with file -I products.csv on macOS or chardetect products.csv on Linux to see what it actually is.

When the CSV does not match the table

CSV column order rarely matches the table's column order on real-world imports. The fix is a column list in the LOAD DATA statement that maps CSV columns to table columns.

LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES
(name, sku, category, @price_dollars, description, @stock_flag)
SET price_cents = ROUND(@price_dollars * 100),
  in_stock    = IF(@stock_flag = 'YES', 1, 0),
  updated_at  = NOW();

What this does:

• The column list maps CSV columns (in the order they appear in the file) to table columns. Skip a CSV column with a placeholder name like @unused.
• Variables prefixed with @ capture raw CSV values so you can transform them. @price_dollars and @stock_flag are user variables, not columns.
• The SET clause computes derived columns. Here it converts dollar prices to cents (real-world price columns are usually stored as integers in the smallest unit), maps a Yes/No flag to a tinyint boolean, and sets updated_at to the current timestamp.

This pattern handles most "the CSV is in the wrong shape" problems without a separate pre-processing step.

What to do next

Once the data is loaded, the natural next step is verifying nothing duplicated, especially if the CSV came from a system that may have re-exported existing records. The same identification pattern from How to Find Duplicate Rows in MySQL catches duplicate SKUs introduced by re-imports; if any show up, How to Delete Duplicate Rows in MySQL walks through removing them with safety checks.

If the CSV came from a partner system whose row set overlaps your existing data, you usually want to find the new rows only. The anti-join patterns in How to Find Rows in One MySQL Table Not in Another make that a one-query operation. To stitch the imported rows back to a reference table during loading, see How to JOIN Two MySQL Tables.

If you also need to write the data out again (e.g., to push to a partner or to a different database), How to Export a MySQL Table to CSV covers the four export methods that mirror the four import methods here. For a full backup of the database after a bulk import lands, How to Export or Back Up All MySQL Databases covers the mysqldump --all-databases --single-transaction --routines --events pattern.

When the CSV introduces columns the table does not yet have, How to Add a Column to a MySQL Table covers the ALGORITHM=INSTANT path; when the new data widens a column past its declared size, How to Change a MySQL Column Type is the matching writeup. Picking the right type up front saves a future migration: MySQL Field Types and Sizes is the lookup table for that.

For application-layer import scripts, How to Update Node.js Version keeps the MySQL2 client current, and How to Increase PHP Memory Limit is the fix when a PHP-driven importer chokes on a multi-gigabyte file. For shell-driven nightly imports, Bash for loop and Bash while loop cover the wrapper patterns.

For the full reference covering LOAD DATA options, character sets, and the rest of the bulk-data syntax, see the MySQL Cheat Sheet.

External reference: official documentation on LOAD DATA INFILE syntax, mysqlimport options, and MySQL Shell util.importTable().

FAQ