Excel to SQL, INSERT statements and table schemas

Spreadsheets often hold the data a database should have owned from the start: a customer list, a product catalog, five years of order history. Moving it into tables usually starts with a SQL file, a CREATE TABLE statement followed by INSERT statements. Writing that by hand works for 20 rows. For 200,000 rows it needs rules. This guide covers the decisions behind a correct SQL dump and shows the output you should expect. The XLS to SQL converter applies these rules and lets you review every choice.

From sheet to table

The mapping looks obvious. The sheet becomes a table, the header row becomes column names and each following row becomes a record. The details are where imports fail.

Table and column names

Headers such as Order Date, Qty. or Customer # are not valid unquoted identifiers. Normalize them into snake_case names using letters, digits and underscores (order_date, qty, customer_no), make duplicates unique and avoid reserved words such as order, group or select. Quoting with backticks in MySQL or double quotes in PostgreSQL makes any name legal, but every future query then needs the same quotes, so clean names are kinder to the people who query the table later.

One header row

Rows above the header, such as a report title or export timestamp, have to be skipped. Merged header cells and two level headers should be flattened into single names, for example billing_city. Totals at the bottom of the sheet are not records and should be excluded.

Type inference

CSV has no types, but an Excel cell does, which makes Excel a better source for SQL than CSV. Even so, a column is only as consistent as the people who typed it. Inference should look at every value in the column, not the first hundred, because the one text value in row 48,000 is the one that breaks the import.

A few rules of thumb make the inferred schema safer.

• Identifiers with leading zeros, such as ZIP codes and SKUs, stay text even if they look numeric. 02134 stored as an integer becomes 2134.
• Money uses DECIMAL with an explicit scale, never FLOAT, which cannot represent values like 0.1 exactly.
• VARCHAR length comes from the longest value, rounded up with some headroom, for example the longest name of 41 characters becomes VARCHAR(64).
• A column with mixed numbers and text becomes text. Guessing a number and dropping the text values silently loses data.
• Dates come from the cell type, converted from the Excel serial number with the correct date system, and are written as ISO literals such as '2025-10-01'.

Inference gives a draft, not a verdict. XlsConverter shows the inferred type for each column before generating SQL, so you can override it, for example to keep a numeric looking account number as VARCHAR.

NULL versus empty string

An empty cell is not the same as an empty string. In SQL, NULL means unknown or missing, while '' is a known value that happens to be empty. For numeric and date columns there is no choice, because an empty string is not a valid number or date in strict mode, so empty becomes NULL. For text columns, NULL is still usually right, because it keeps queries such as WHERE phone IS NULL meaningful.

The schema should reflect what the data shows. A column with no empty cells can be declared NOT NULL, which documents the rule and protects later inserts. A column with any empty cell must allow NULL, or the import fails on the first gap. Cells that contain only spaces are worth trimming first, since they would otherwise arrive as text.

Escaping values

Every string value must be escaped for the dialect. A single quote inside text, as in O'Brien, becomes two quotes ('O''Brien'). MySQL also treats the backslash as an escape character by default, so a path such as C:\temp has to be written with doubled backslashes, while PostgreSQL standard strings take the backslash literally. Line breaks inside a cell are valid in a string literal but should be preserved deliberately. Building SQL by gluing raw cell text into a statement is how a spreadsheet becomes an injection risk, so the escaping has to be done by the generator, not by hand.

Batching INSERT statements

One INSERT per row is the slowest possible import. Each statement is parsed and, outside a transaction, committed separately. Multi-row INSERT statements are much faster.

CREATE TABLE customers (
  customer_no VARCHAR(16) NOT NULL,
  name VARCHAR(128) NOT NULL,
  country CHAR(2) NULL,
  credit_limit DECIMAL(12,2) NULL,
  signed_up DATE NULL,
  PRIMARY KEY (customer_no)
);

BEGIN;
INSERT INTO customers (customer_no, name, country, credit_limit, signed_up) VALUES
('000412', 'Northwind Supply', 'US', 25000.00, '2021-03-15'),
('000413', 'O''Brien Hardware', 'IE', NULL, '2022-11-02'),
('000414', 'Kowalski Metal', 'PL', 8000.00, NULL);
COMMIT;

Batches of 500 to 1,000 rows work well in practice. Much larger batches can exceed limits such as max_allowed_packet in MySQL, and a failure then rolls back a large chunk. Wrapping the whole file in a transaction means a failed import leaves the table untouched rather than half filled.

For very large sheets, generating SQL is not always the fastest route. PostgreSQL loads CSV with COPY and MySQL with LOAD DATA INFILE much faster than any INSERT stream. In that case export the data as CSV and use the generated CREATE TABLE statement for the schema. The dedicated Excel to MySQL and Excel to PostgreSQL pages describe both paths for each database.

Dialect differences worth knowing

• Identifier quoting uses backticks in MySQL and double quotes in PostgreSQL and SQLite.
• Booleans are TINYINT(1) in MySQL, BOOLEAN in PostgreSQL and integers 0 and 1 in SQLite.
• SQLite uses type affinity, so declared types guide storage rather than enforce it, which makes clean data even more important.
• Re-running an import is easier with INSERT IGNORE or ON DUPLICATE KEY UPDATE in MySQL and ON CONFLICT in PostgreSQL, once a primary key is defined.

Keys, indexes and constraints

A spreadsheet rarely declares a key, but a table should. If a column is unique and never empty, such as a customer number or an invoice number, it is a natural primary key. If no column qualifies, add a surrogate key, an auto incrementing id in MySQL or an identity column in PostgreSQL, and keep the spreadsheet columns as plain data. Check uniqueness before declaring a key, because a single duplicate makes the whole batch fail. Add indexes for the columns you filter by most, such as dates and foreign keys, after the data is loaded, since building an index once is faster than updating it on every insert. Check constraints, such as a positive quantity, document rules the spreadsheet only implied.

Several sheets, several tables

A workbook with Customers and Orders sheets becomes two tables. If Orders has a customer_no column, add the foreign key after both tables are loaded, so load order does not matter, and check for orphan rows first. Each sheet keeps its own inferred schema.

A short checklist

1. Confirm the header row and skip title and total rows.
2. Review inferred types, especially IDs, money and dates.
3. Choose NULL for empty cells and NOT NULL only where data is complete.
4. Pick the dialect and let the generator escape values.
5. Batch inserts and run them inside a transaction.
6. Count rows in the table and compare with the sheet.

When the same workbook arrives every month, the column types and names are saved in a template, so the next dump matches the existing table exactly. For a full migration project, the converters overview lists every database target.