Convert CSV or Excel to SQL INSERT statements that actually load

Should you generate INSERTs at all?

Every major engine ships a bulk loader that reads a CSV directly, and for big or recurring loads it wins by a wide margin:

• PostgreSQL: from psql, \copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true)
• MySQL / MariaDB: LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;
• SQL Server: BULK INSERT customers FROM 'C:\data\customers.csv' WITH (FORMAT = 'CSV', FIRSTROW = 2);, or the bcp utility
• SQLite: in the shell, .import --csv customers.csv customers

Millions of rows, or a nightly job? Use the loader. But each one has conditions. Server-side COPY ... FROM '/path' reads a file on the database host and needs superuser or pg_read_server_files, which is why psql's client-side \copy exists. LOAD DATA LOCAL requires local_infile on both server and client, and MySQL 8 ships with it off; managed and shared hosts rarely let you change that. BULK INSERT resolves the path on the server's disk, not your laptop's, and on Azure SQL Database it can only read from Blob Storage.

A script of INSERT statements is the better tool when all you have is a SQL client or phpMyAdmin against a managed database, when you want to read the data before it lands or commit it as a seed file next to your migrations, when the volume is moderate (up to tens of thousands of rows), or when someone else will run the SQL somewhere you can't reach.

What breaks CSV imports

Delimiters: when a CSV isn't comma-separated

Excel's "CSV (Comma delimited)" uses the system list separator. In decimal-comma locales (Germany, France, Spain, Italy, Brazil and most of Europe and South America) that's a semicolon, so the file a colleague in Munich sends you is very likely ;-delimited. An importer that assumes commas gives you one giant column or a column-count error.

The Utiliobox CSV to SQL converter sniffs it: it counts commas, tabs, semicolons and pipes across the first five lines, ignoring anything inside quoted fields, and picks the winner. Quoted fields with embedded delimiters, line breaks and doubled "" quotes are handled. Drop the .xlsx itself and the question disappears: the first sheet is read directly.

Escaping quotes and backslashes

A single quote inside a string literal is written twice: INSERT INTO customers (last_name) VALUES ('O''Brien');. That works in MySQL, PostgreSQL, SQL Server, Oracle and SQLite. Gluing raw values into SQL isn't just a syntax error waiting to happen; it's exactly how SQL injection works. Two engine-specific traps:

• MySQL treats backslash as an escape by default, so 'C:\new' is stored with a newline in the middle. Backslashes must be doubled, unless the server runs with NO_BACKSLASH_ESCAPES, in which case doubling stores two. PostgreSQL treats them literally (standard_conforming_strings = on since 9.1).
• SQL Server needs the N prefix (N'Zoë') for Unicode. Without it, characters outside the database's code page become ?, even in an NVARCHAR column.

The converter doubles single quotes for all five engines, doubles backslashes for MySQL, and prefixes SQL Server strings with N.

NULL versus empty string

CSV can't say "no value": an empty field could be NULL or a zero-length string, and loaders disagree. PostgreSQL's COPY in CSV mode reads an unquoted empty field as NULL and a quoted "" as an empty string. MySQL's LOAD DATA stores '' in string columns and 0 in numeric ones, depending on SQL mode, and only treats \N as NULL. Oracle stores '' as NULL whatever you intend.

The converter uses one rule: an empty or whitespace-only cell becomes NULL. The generated CREATE TABLE follows from it: any blank makes the column NULL, none makes it NOT NULL. If your CSV came from a database export that writes the literal word NULL or \N, blank those first; otherwise they're treated as text, and one is enough to turn a numeric column into a string.

Numbers: decimal commas and thousands separators

SQL numeric literals only take a dot. An unquoted 12,50 in a VALUES list is two values, 12 and 50, and every column after it shifts. The converter accepts a dot or a single comma as the decimal mark and always emits a dot: 12,50 becomes 12.50 in a DECIMAL(18,2) column.

It doesn't interpret thousands separators. 1.234,56 doesn't match as a number, so the column stays text. The tricky one is 1,234: one thousand two hundred thirty-four in a US file, one point two three four in a European one. If every number in the column has that shape, the converter won't gamble and leaves it as text; one value that settles it (12,5, 0,125) is enough for it to read the column as decimals. Safest is still to strip thousands separators at the source: in Excel, format the column as Number with "Use 1000 Separator" unchecked.

Dates: ambiguity and Excel serial numbers

YYYY-MM-DD (plus HH:MM:SS) is the only format every engine reads the same way regardless of settings. 03/04/2024 is March 4 in the US and April 3 almost everywhere else, and a wrong guess doesn't fail; it loads quietly wrong.

Know what the converter assumes: it recognizes ISO dates and slash- or dash-separated dates with a four-digit year, and picks the order by looking at the whole column. A 12/25/2024 anywhere means month/day; a 25/12/2024 means day/month. When no value goes past 12 the data can't tell, so the English page assumes month/day and the detected types flag it as “assumed” for you to check. If your file comes from outside the US and every date is ambiguous, convert to yyyy-mm-dd in Excel first. A date that's impossible either way leaves the column as text instead of inventing a month. For Oracle, dates are emitted as TO_DATE('2024-04-03', 'YYYY-MM-DD') so nothing depends on NLS_DATE_FORMAT.

When a cell loses its format you see the serial number: 45000 is March 15, 2023. Convert in the database by adding the days to December 30, 1899, an epoch that already absorbs Excel's fake 1900 leap day: DATE '1899-12-30' + 45000 in PostgreSQL, DATE_ADD('1899-12-30', INTERVAL 45000 DAY) in MySQL, DATEADD(day, 45000, '1899-12-30') in SQL Server. Workbooks using the old Mac 1904 date system are 1,462 days off, so check one known date first.

When the converter reads an .xlsx, it takes each cell's displayed text, not its raw value, so serials won't appear. A short date format can still arrive with a two-digit year and go unrecognized; the detected types under the panel will show that column as text. Reformat it as yyyy-mm-dd and drop the file again.

Leading zeros: ZIP codes, phone numbers, account IDs

Boston's ZIP is 02134, not 2134. Digit-only codes are identifiers and belong in text columns. The zeros vanish in two places:

• Excel parses them as numbers when it opens a CSV; save it and they're gone. Open CSVs via Data > From Text/CSV and set the column to Text.
• Many type-inferring converters propose an integer for a digits-only column and write the value as 2134. This one keeps any column where a value starts with zero as text, so 02134 arrives intact. If the zeros were already lost upstream, change the column to VARCHAR(5) in the CREATE TABLE, load, then restore the padding with UPDATE customers SET zip = LPAD(zip, 5, '0'); (MySQL, PostgreSQL) or RIGHT('00000' + zip, 5) in SQL Server.

Phone numbers also lose their +. A column containing +1, spaces or dashes stays text, which is what you want.

Encoding: UTF-8, Windows-1252 and the BOM

José means a UTF-8 file was read as Windows-1252; Jos� means the reverse. On Windows, plain "CSV (Comma delimited)" is saved in the system code page, while "CSV UTF-8 (Comma delimited)" is UTF-8 with a byte order mark. Pick the second.

The BOM is three invisible bytes (EF BB BF). Tools that don't expect it glue it to the first header, leaving you with an id column that SELECT id can't find. The converter reads text files as UTF-8 and drops the BOM; if you see � in the input panel, the file was Windows-1252 and needs re-saving. On the target side, use utf8mb4 in MySQL (legacy utf8 can't store emoji) and NVARCHAR in SQL Server.

Quoting column names per engine

PostgreSQL is the one that bites later: create the table with "Email" and every future query needs the quotes. Lower-case, underscore-separated names avoid it. The converter does that by default: "Signup Date" becomes signup_date, accents and symbols go, and a leading digit gets a c_ prefix. Turn the option off to keep the original headers, quoted in the chosen engine's syntax.

Batching, engine limits and transactions

A multi-row INSERT packs many rows into one VALUES (...), (...) and is typically an order of magnitude faster than one statement per row. The limits: SQL Server allows at most 1000 rows in a VALUES constructor (Msg 10738, not configurable; the often-quoted 2100 limit is for parameters, a different thing). MySQL has no row cap, but the statement must fit in max_allowed_packet, 64 MB by default in MySQL 8 and often less on shared hosts. Oracle lacked multi-row VALUES before 23ai; INSERT ALL ... SELECT * FROM dual works everywhere.

The converter offers one-per-row or multi-row INSERTs with a batch size from 1 to 1000 (default 100), so the SQL Server limit can't be exceeded, and it switches to INSERT ALL for Oracle. A few hundred rows per batch is a good middle ground: fast, and a failure points to a small block.

It doesn't add a transaction, so add one: otherwise a failure at row 8,000 leaves 7,999 rows committed.

• PostgreSQL: BEGIN; ... COMMIT;. DDL is transactional, so even the CREATE TABLE rolls back.
• MySQL: START TRANSACTION; ... COMMIT; on InnoDB. Run the CREATE TABLE separately; DDL commits implicitly.
• SQL Server: SET XACT_ABORT ON; BEGIN TRANSACTION; ... COMMIT;. Without XACT_ABORT, a constraint violation aborts only that statement and the script keeps going.
• SQLite: BEGIN; ... COMMIT;. Without it, every INSERT syncs the file to disk and the load crawls.

A workflow that holds up

1. Export as "CSV UTF-8", or drop the .xlsx into the converter; nothing is uploaded anywhere.
2. Check the detected types: dates as dates, amounts as decimals, ZIP codes as text.
3. Edit the CREATE TABLE: add a primary key and tune lengths, which are deliberately rounded up from your sample.
4. Use multi-row batches of a few hundred inside a transaction, then compare COUNT(*) with the file's row count.