Convert Excel to SQL

What an .xlsx actually is, and why Excel to SQL is not just CSV again

Rename a workbook to .zip and open it. Inside is a folder of XML documents: one per worksheet, one listing every distinct piece of text in the whole file, one describing number formats, and a set of relationship files tying them together. Almost nothing you see on screen is stored where you would expect it to be, and three of those surprises are enough to ruin a conversion.

The first is that the text is not in the cell. Most cells containing words hold an integer index into a shared string table, so a reader that takes the cell's value literally gives you 1 where the sheet says Austin. The second is that a date is a number — 15 March 2023 is the value 45000, and the only thing making it a date is a formatting record in a different file entirely. The third is that empty cells are simply absent. A row with a gap in the middle skips straight from column A to column D, and anything counting cells in order puts every later value in the wrong column.

The date question deserves its own answer

Because it is where most converters quietly give up. The format code has to be read, matched against Excel's built-in date formats and any custom ones the workbook defines, and then only applied to the cells whose style points at one. That check has to look past quoted text and bracketed sections too, or a currency format that happens to contain a letter gets misread as a date and every price in the column becomes a day in the 1970s.

Then the number becomes a date, which involves a piece of history: Excel believes 1900 was a leap year. It was not. Serial 60 is the 29th of February 1900, a day that never existed, and every conversion has to step around it or land a day out. The count of cells rewritten is shown to you, because turning a number into a date is an interpretation of your data and interpretations should be declared.

Sheets are chosen, never assumed

Every sheet in the workbook is listed with its row count and you pick one. Taking the first tab without asking is a good way to convert the summary page instead of the data. The order matters as well: the sheet named first in the workbook is not necessarily stored in the file called sheet1.xml, because deleting a tab leaves the remaining files with their original numbers. The relationships get followed properly rather than guessed from filenames.

Rows below your data that carry formatting but no content are skipped instead of imported, and the number of them is reported. A workbook usually trails a hundred of those, and importing them writes a hundred empty records nobody asked for.

Copy a range instead, if that is easier

A workbook file cannot sit on your clipboard, but a selection can. Highlight the cells in Excel, copy, and paste into the box — Excel puts a selection on the clipboard as tab-separated text, and it is read here exactly the way a file is. This is genuinely the faster route when you want three columns out of a large sheet, and it is a first class way in rather than a fallback.

The workbook stays on your machine

Unpacking the archive uses the decompression the browser already has, and the XML is parsed by JavaScript in this tab. There is no upload step, which is not a stylistic choice: spreadsheets destined for a database are usually people — names, addresses, order histories, sometimes payroll. Every server-side converter that does this job needs a copy of that file to do it, and then has one.

No workbook is turned away for its size. If a sheet is large enough that reading it takes real time, the work happens off the main thread and the tab stays responsive while it does.