Importing Excel Into MySQL With Workbench and Scripts

Search for how to import Excel into MySQL and you will find screenshots of an Excel wizard in MySQL Workbench. That wizard does not exist in the current Workbench: the Table Data Import Wizard accepts CSV and JSON files, and Excel files have to be converted first. This post sets out the routes that work, with the checks that stop each from damaging the data. It belongs to the cluster on getting Excel data into a database.

Step one: get a CSV you can trust

Open the workbook in Excel, choose Save As and pick "CSV UTF-8 (Comma delimited)". UTF-8 matters, because MySQL's default character set for new tables is utf8mb4 and a file in a legacy code page turns accents into garbage. Only the active sheet is saved, so repeat for each sheet. Before you save, make sure identifier columns are formatted as text and that dates are what you expect, since Excel writes what it displays (saving an Excel sheet as CSV).

Know the traps of the conversion. Excel writes numbers as displayed, so a column formatted to show two decimals becomes 19.99 and a percentage becomes 45%. In some locales it writes a semicolon as the delimiter and a comma as the decimal mark. Dates come out in the display format of the machine that saved the file. Open the CSV in a text editor, not in Excel, and look at the first lines. If that is more steps than you want, a script that reads the XLSX directly avoids the conversion (see the last section).

The wizard

In Workbench, right-click the target schema and choose Table Data Import Wizard. Pick the CSV file, choose a new or existing table, and review the column types it proposes. The wizard samples the file and guesses: integers become BIGINT or INT, decimals DOUBLE, and everything else TEXT. That guess is where the trouble is. It converts a zip code column to integers and drops the leading zeros, it picks DOUBLE for money, and it reads a date as TEXT or a DATETIME depending on the format.

Use the wizard for exploration. When you use it for real work, create the table yourself, with the types you want, and import into the existing table so the wizard maps columns and does not choose types. The wizard is also slow for large files, because it inserts row by row.

The bulk route: LOAD DATA

For anything above a few thousand rows, use LOAD DATA. Create a text-only staging table first:

CREATE TABLE stage_orders (
    batch_id    INT NOT NULL,
    source_row  INT NOT NULL,
    order_no    VARCHAR(50),
    customer    VARCHAR(200),
    amount_eur  VARCHAR(50),
    ordered     VARCHAR(50)
) CHARACTER SET utf8mb4;

Then load the CSV. The provenance columns are not in the file, so set them in the statement:

SET @row := 1;
LOAD DATA LOCAL INFILE 'orders.csv'
INTO TABLE stage_orders
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(order_no, customer, amount_eur, ordered)
SET batch_id = 42, source_row = (@row := @row + 1);

The user variable @row counts rows so each carries its sheet row number: the first data row gets 2, since the header is row 1. The SET clause assigns the columns that are not in the file. IGNORE 1 LINES skips the header. Check the line terminator: a file saved on Windows ends lines with \r\n, and one saved on Linux or Mac with \n. Getting it wrong leaves a stray carriage return at the end of the last column, which breaks every comparison on it. When in doubt, load a few rows and select HEX(ordered) to see.

LOCAL reads the file from the client machine and sends it to the server. It must be allowed in two places. On the server, local_infile must be on (SET GLOBAL local_infile = 1; requires privileges). On the client, the connection must opt in, for example mysql --local-infile=1, or the equivalent option in Workbench's connection settings under Advanced. The error "The used command is not allowed with this MySQL version" or error 3948 means one side is off. Without LOCAL the file is read by the server from its own disk, restricted by secure_file_priv, which is a good reason to prefer LOCAL for ad hoc work. Both settings are security-relevant, and LOCAL has been the subject of vulnerabilities when a client connects to an untrusted server, so enable it only for connections you trust.

Convert from the stage

With the data staged, check the counts (validating row counts after an Excel import) and convert:

INSERT INTO orders (order_no, customer, amount_eur, ordered_on)
SELECT order_no,
       customer,
       CAST(amount_eur AS DECIMAL(12,2)),
       STR_TO_DATE(ordered, '%Y-%m-%d')
FROM stage_orders
WHERE batch_id = 42;

For ISO text dates, STR_TO_DATE with %Y-%m-%d parses 2024-01-05 into a date. If the column holds Excel serial numbers instead, use DATE_ADD('1899-12-30', INTERVAL ordered DAY) (converting Excel serial dates to real dates in SQL). Two behaviors deserve attention.

SQL mode. MySQL 8 defaults to strict mode for transactional tables, so an invalid value in an INSERT ... SELECT raises an error and nothing is inserted. If sql_mode has been changed to omit STRICT_TRANS_TABLES, MySQL converts bad values to zeros or truncates strings and issues only a warning. Check with SELECT @@sql_mode;, and run SHOW WARNINGS; after every load. A load that finishes "successfully" with 300 warnings has not succeeded.

Silent conversions in CAST. CAST('12abc' AS DECIMAL(12,2)) returns 12.00 and a warning rather than failing in non-strict contexts. Validate the text with a regular expression before casting:

SELECT source_row, amount_eur
FROM stage_orders
WHERE batch_id = 42 AND amount_eur NOT REGEXP '^[0-9]+(\\.[0-9]{1,2})?$';

Any row this returns is a value the cast would have mangled. Fix the source or reject the row.

The script route

When the same kind of workbook arrives regularly, a script is easier than clicking and removes the CSV step, because pandas reads the XLSX directly:

import pandas as pd
from sqlalchemy import create_engine

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype=str, keep_default_na=False)
df = df.rename(columns={"Order No": "order_no", "Customer": "customer",
                        "Amount (EUR)": "amount_eur", "Date": "ordered"})
df["batch_id"] = 42
df["source_row"] = df.index + 2

engine = create_engine("mysql+pymysql://loader@localhost/app?charset=utf8mb4")
df.to_sql("stage_orders", engine, if_exists="append", index=False, chunksize=2000)

With dtype=str nothing is converted on the way in, so the leading zeros in 007 survive into the VARCHAR column. The charset=utf8mb4 in the URL avoids the old three-byte utf8 connection default that cannot store some characters. The columns of the dataframe must match the staging table's column names, which is why the rename comes first. to_sql appends row by row in batches and is slower than LOAD DATA; for large files, write the dataframe to a temporary CSV and run LOAD DATA LOCAL INFILE from the script. Then convert in SQL exactly as above.

If you need the loader to be repeatable, add a batch table with a file hash and an upsert on a natural key (INSERT ... ON DUPLICATE KEY UPDATE), as described in the cluster guide.

Collation, case and trailing spaces

MySQL's default collations are case-insensitive and, for PAD SPACE collations in some versions, ignore trailing spaces in comparisons. 'Acme' and 'ACME ' can compare equal in a WHERE clause and be distinct in a binary comparison. When you join staged text to existing tables, make sure the collations match, or MySQL raises "Illegal mix of collations". Trim text on the way in (TRIM(customer)), and decide whether the business treats case differences as the same customer. An identifier that must be matched exactly, such as a code, belongs in a column with a binary or case-sensitive collation.

When this MySQL import route is not the right one

You need a one-off lookup table of ten rows. Type INSERT ... VALUES or paste from a text editor. A pipeline is overhead.

**The server is managed and LOCAL INFILE is disabled.** Cloud providers often disable it. Use the script route, or the provider's import tool.

The data is large. Beyond a million rows, Excel is the wrong source. Ask for CSV at the origin and use LOAD DATA, with indexes added after the load.

The workbook needs its formulas reproduced. Load the inputs and write the logic in SQL (translating Excel formulas into SQL).

The statement is documented in the LOAD DATA reference, and the wizard in the MySQL Workbench manual on table data export and import. To check a sample of the CSV as JSON, use the CSV to JSON converter.