CSV looks correct in Excel, then MySQL shows Café as Café, the first id lands as 0, or emoji becomes ?. That is almost always a mismatch between the file bytes, the importer charset, and the table charset. In this guide we walk through how to import a CSV into MySQL from a desktop GUI without wrecking encodings.
Why a good CSV still corrupts
Three layers have to agree. The spreadsheet export has an encoding (often Windows-1252 or UTF-8 with a BOM). The client that sends INSERT or LOAD DATA must declare that encoding. The column must be able to store the result, which on MySQL 8.4 and 9.x should be utf8mb4, not latin1 and not the old utf8 alias (utf8mb3).
Excel on Windows still trips people. "CSV UTF-8" frequently writes bytes EF BB BF at the start of the file. European locales use semicolon as the separator. The older "CSV (Comma delimited)" path is Windows-1252 even when the sheet looks like Unicode on screen.
Graphical importers still run SQL. HeidiSQL, DBeaver Community, TablePlus, and TabulaSQL show the same broken characters the mysql CLI would when the charset clause or table definition is wrong.
| What you see | Likely cause | Fix |
|---|---|---|
First id is 0, warning 1366 |
UTF-8 BOM on byte 0 | Re-save as UTF-8 without BOM |
Café instead of Café |
UTF-8 file decoded as latin1 | File UTF-8, CHARACTER SET utf8mb4, utf8mb4 table |
Emoji becomes ? |
Column is utf8mb3 (utf8) |
utf8mb4 column |
| One field swallows the rest of the line | Wrong delimiter or quotes | Match comma vs semicolon, enclosure |
Inspect the file before you connect
Look at the bytes, not the spreadsheet preview.
file -I customers.csv
xxd customers.csv | head
UTF-8 for an accented e is C3 A9 after Caf. Windows-1252 for the same letter is E9. A UTF-8 BOM is EF BB BF on the first line. Convert a Windows-1252 export before any import:
iconv -f WINDOWS-1252 -t UTF-8 customers.csv > customers-utf8.csv
Re-save from LibreOffice Calc or Excel as UTF-8 CSV, then open the file in an editor that can drop the BOM (UTF-8, not UTF-8-BOM). Confirm the delimiter: comma, semicolon, or tab. Quoted fields with embedded commas need OPTIONALLY ENCLOSED BY '"'.
The header string id sitting in the first data row makes an INT column throw warning 1366. Keep that header when the importer maps by name, or skip it explicitly.
Put the table on utf8mb4 first
Connect to 127.0.0.1 port 3306 (or the published Docker / Laravel Herd port). Run:
SHOW CREATE TABLE customers;
SHOW VARIABLES LIKE 'character_set%';
Skip importing when CREATE TABLE still says latin1 or CHARSET=utf8 (mb3). Create a scratch database with charset utf8mb4 and collation utf8mb4_unicode_ci, then a matching table. TabulaSQL's create-database dialog offers those charset and collation pairs. ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 is for latin1 data that was stored correctly, not for bytes that are already wrong.
The MySQL driver in TabulaSQL uses utf8mb4 on the connection. That helps INSERTs from the grid. It does not change a latin1 column.
Import from the data grid
For a few hundred or a few thousand rows, paste is safer than LOAD DATA because you see column mapping before the table fills up.
- Open the MySQL or MariaDB connection and click the target table so the data grid loads.
- Confirm the table is editable. Without a primary key the grid stays read-only and Import clipboard stays hidden.
- Click Import clipboard on the grid toolbar.
- Paste CSV, TSV, or JSON into the dialog. When the first line contains column names that match the table, those names drive the mapping. Otherwise columns line up left to right.
- Click Import. TabulaSQL inserts rows immediately. The Messages panel reports how many landed and how many failed.
- Filter a column that should hold an accented letter or an emoji. Doubled characters or
?means truncate the scratch table and fix the file. Do not keep stacking rows.
Clipboard import is the CSV path in TabulaSQL. The SQL file importer is for .sql dumps, which is a different workflow: How to Import a SQL Dump into MySQL. HeidiSQL and DBeaver Community include file-based CSV wizards with an encoding dropdown. Pick UTF-8 there, and still strip the BOM. Auto plus Excel's signature is how the first integer column dies.
TabulaSQL is MIT-licensed and free for MySQL, MariaDB, PostgreSQL, and SQLite. Download it from https://tabulasql.com/download if you want that clipboard flow on macOS, Windows, or Linux. It is one option, not the only importer that exists.
LOAD DATA when paste is the wrong size
Paste does not replace a bulk loader. For large files, run LOAD DATA from the SQL editor (LOCAL requires local_infile=1) or from the mysql CLI.
LOAD DATA LOCAL INFILE '/tmp/customers-utf8.csv'
INTO TABLE customers
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, email, city);
CHARACTER SET is the encoding of the file. LINES TERMINATED BY must match the file: \r\n from Excel on Windows, \n from most Unix exports. A UTF-8 BOM still prefixes the first field. MySQL does not skip it. Warning 1366 on row 1 plus id = 0 is the usual fingerprint.
When LOCAL is disabled on the server, import on a throwaway local instance, then copy tables. Do not turn on local_infile in production just to land a laptop CSV. For a local Docker target, Connect a MySQL GUI to Docker covers host and port.
Read HEX, then stop
SELECT id, name, HEX(LEFT(name, 8)) FROM customers LIMIT 5;
An accented e in utf8mb4 is C3A9 in HEX. Row counts versus wc -l minus the header catch a truncated run. Grid editing, Safe Lock, and SQL import are listed on /features.