MySQL Import Data From EXCEL (csv file)

Importing a spreadsheet into MySQL is a two-command job once you know the LOAD DATA incantation — but the details (line endings, character sets, the local-file security switch) are exactly where this trips people up. Here is the command-line workflow, updated for current MySQL.

1. Save the Excel sheet as CSV

In Excel: File → Save As → CSV (Comma delimited) (*.csv). If the sheet has non-ASCII text (Greek names, anyone?), choose CSV UTF-8 instead — plain CSV is saved in the legacy Windows codepage and your accents will come out mangled.

2. Create a matching table first

LOAD DATA does not create the table. Define it with the same columns, in the same order, as the CSV:

CREATE TABLE test_table (
  id INT,
  name VARCHAR(100),
  email VARCHAR(200)
) CHARACTER SET utf8mb4;

3. Run LOAD DATA

mysql -u root -p

USE yourdbname;

LOAD DATA LOCAL INFILE '/path/to/yourCSVfilename.csv'
INTO TABLE test_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(field1, field2, field3);

What each line does and why it’s there:

• CHARACTER SET utf8mb4 — interprets the file as UTF-8. The old advice of mixing greek_unicode_ci collations is obsolete: utf8mb4 has been the default character set since MySQL 8.0 and handles Greek (and everything else) correctly.
• OPTIONALLY ENCLOSED BY '"' — Excel quotes fields containing commas; without this clause those commas split your rows.
• LINES TERMINATED BY '\n' — on a CSV produced in Windows, try '\r\n' if rows come out with a phantom character in the last column.
• IGNORE 1 LINES — skips the header row the Excel export includes.

The local_infile gotcha

LOAD DATA LOCAL reads the file from your machine and sends it to the server — and it’s disabled by default on both ends since MySQL 8.0, because a malicious server can read arbitrary client files via this path. You must enable it on both:

• Client: connect with mysql --local-infile=1 -u root -p
• Server: local_infile=ON (set it at runtime with SET GLOBAL local_infile = ON;, or in mysqld.cnf to persist)

If instead the file lives on the server itself, drop the LOCAL keyword — then the file must be in the server’s secure_file_priv directory and readable by the MySQL process. See the LOAD DATA reference and the LOCAL security notes in the manual.

The 2009 advice that no longer applies

The original version of this post recommended SET character_set_database = utf8, saving the CSV as “UTF8”, and creating tables with greek_unicode_ci. Two of those three are outdated: utf8 is now an alias for the broken 3-byte utf8mb3 — always use utf8mb4 — and there is no reason to force the database character set for one import when you can declare it right in the LOAD DATA statement. The table’s collation (e.g. utf8mb4_unicode_ci) affects comparison and sorting only, not how the import reads bytes.

One last tip: for a one-off migration, checking the result with SELECT COUNT(*) against the spreadsheet’s row count (minus header) catches encoding or line-ending mistakes in seconds.