Convert XLSX to SQL

The output is a file of INSERT statements, one per row of the sheet, each naming the table and listing the columns from the header. There is no CREATE TABLE at the top and that is deliberate: a spreadsheet has no types, no lengths, no keys and no constraints, so any schema derived from it would be invention presented as fact.

A wrong guess in a row is one bad record. A wrong guess in a schema is a VARCHAR(50) that truncates addresses for two years. The assumption here is that you have already designed the table — which is true of almost everyone who needs this conversion — and what you want is the tedious part written out correctly. If you do need a table as well, write the CREATE statement yourself and put it at the top of the file. It takes two minutes, it records a set of decisions somebody should be making deliberately, and the generated statements below it will run against it unchanged.

The name of the workbook, minus its extension, becomes the table in every statement. It is sanitised first: anything that is not a letter, a digit or an underscore becomes an underscore, and a name that begins with a digit gets one prefixed, because an identifier cannot start with a number. A file called "2024 orders.xlsx" therefore inserts into _2024_orders.

The practical advice follows directly. Rename the file to exactly the table you are loading into before converting, and the output runs as it is. Doing it the other way round means a search and replace across fifty thousand lines, which works and is one more step at which somebody replaces the wrong string. The same sanitising applies to the column names taken from the header row, so a heading reading "Order total (£)" becomes Order_total____ and will not match your column. Tidy the header row in the spreadsheet to match the table before converting, and the file needs no editing at all.

Numbers are written bare. Text is wrapped in single quotes with any apostrophe inside it doubled, which is the ANSI form every major engine understands — O'Brien becomes 'O''Brien'. Backslash escaping is deliberately not used, because it is a MySQL extension and its behaviour depends on a server setting rather than on the statement.

An empty cell becomes the keyword NULL rather than an empty string, which is almost always the right reading of a blank in a spreadsheet and is worth knowing if your column is NOT NULL. Where the distinction genuinely matters — a blank meaning "none" against "not recorded" — the spreadsheet never held it in the first place and no converter can recover it. The values themselves are not reformatted on the way through. A number appears as the workbook stores it, so a price displayed as 19.99 with two decimal places and a currency symbol is written as 19.99, and one displayed as 20 that is really 19.995 is written at its full precision.

A boolean cell is written as the literal TRUE or FALSE. Postgres takes that directly, MySQL treats them as aliases for 1 and 0, and SQLite has accepted them for years. SQL Server does not: its bit type wants 1 and 0, and a statement containing TRUE fails to parse.

It is a two-second fix once you know — replace the literals before running the script — and an irritating twenty minutes if you find out from an error on statement four hundred. If the destination is SQL Server, do the replacement first and check that no text column happens to contain the word. The safer habit for any target is to look at the first statement in the file before running the last one. It shows you the table name, the column list and one example of every value type in a single line, which is the fastest review this file will ever get.

A spreadsheet holds a date as a day count wearing a display format, and the conversion writes the value rather than the appearance. 1 January 2024 becomes 45292, counted from the end of December 1899, and a timestamp becomes that number with a fraction attached.

Inserted into a numeric column it lands quietly and is wrong; inserted into a date column it is rejected, which is at least loud. Neither is what you want. Fix it in the workbook by converting the column to text in an unambiguous format, or load into a staging column and convert inside the database, where the epoch arithmetic is a single expression. Staging is the better answer whenever the load is going to be repeated. It gives you a place to validate before anything reaches the real table, and the conversion of day counts into dates becomes part of a documented step rather than something somebody remembered to do by hand last time.

A code stored as text in the sheet is written as a quoted string, so 007 arrives as '007' and a zero-padded account reference goes into a VARCHAR column intact. This is the opposite of what happens when the same data goes through a CSV and a careless spreadsheet round trip, where those columns become numbers and the padding is gone before any conversion starts.

The useful test for whether a column is an identifier is whether adding two of its values together would mean anything. Postcodes, part numbers, phone numbers and reference codes all fail it, and all of them belong in a text column in the target table as well as in the sheet. The reverse case is the one to watch. If the workbook stored a code as a number at some earlier point, the padding is already gone and the statement will insert 7 into your VARCHAR column, quite happily and quite wrongly.

Every statement names its columns explicitly rather than relying on the table’s column order, which is what makes the file survive a table that has since gained a column. The set of columns is the union of the fields found across the whole sheet, so a field that only appears in later rows is still in the list, with NULL supplied for the rows that lack it.

That verbosity has a cost worth knowing about. The column list is repeated on every line, so the file is larger than the data in it: fifty thousand rows of six columns came out at about 5.7 MB of SQL, against roughly 4.3 MB for the .xlsx it was made from. It is the only one of these targets that is bigger than the workbook.

The statements are emitted one per row with no transaction around them and no multi-row VALUES batching. Run as-is against Postgres or MySQL, that is fifty thousand round trips and fifty thousand implicit commits, which is slow enough to be noticeable and leaves a half-loaded table if it fails in the middle.

Wrapping the file in BEGIN and COMMIT fixes both at once and costs two lines. For genuinely large loads the better tool is the bulk path — COPY in Postgres, LOAD DATA in MySQL, .import in SQLite — which reads a delimited file directly and is an order of magnitude faster. Convert to CSV or TSV for that route instead.

The first sheet of the workbook is what gets converted, because a run of INSERT statements addresses one table. If the sheet you want is not first, move it and convert again; if several sheets are going into several tables, convert once per sheet with the file named for each target table in turn.

All of it happens locally: the workbook is parsed and the statements are written in the page, with no upload and no request carrying the file. That matters more for this pair than for most, since a spreadsheet on its way into a database is usually customer records, transactions or something else that should not be pasted into a website to be processed.

If the target table is empty and the volume is large, generating SQL is the slow way round. Every database has a bulk loader that reads a delimited file directly, applies types once and writes in blocks, and it will beat a script of INSERT statements comfortably on anything above a few thousand rows.

INSERT statements earn their place when the load is small, when it has to be reviewed or checked into version control before it runs, when it will be replayed against several environments, or when the only access you have is a query window and not the filesystem. That is a real and common situation, and it is the one this conversion is for.