How to import Excel into SQL Server without losing what is in the spreadsheet

Getting a spreadsheet into a database looks like a five-minute job. It is, until the dates arrive as numbers, one column turns out to hold two kinds of thing, and the field that only appears on row 551 is in neither the schema nor the table.

Almost every import tool does the same four things, and three of them are guesses. It reads the top of the file, decides what each column is, creates a table, and pushes the rows in. When a value disagrees with the guess, something has to give — and what usually gives is the value.

This page is about what actually goes wrong on that path, and what an import has to do instead. It is written from the inside: these are the problems Netune's own spreadsheet reader had to solve before it could be trusted with anybody's data.

A date in Excel is not a date

This is the one that catches everybody, because a date cell looks like a date. Underneath, it is a number — the count of days since the 30th of December 1899 — wearing a number format that displays it as a date. The cell holding 45000 is either forty-five thousand or the 3rd of March 2023, and nothing in the cell itself tells you which. The answer is in the workbook's style table, in a different file inside the .xlsx archive.

So a reader that ignores styles turns every date column into a column of five-digit integers. One that reads them carelessly does something worse. Consider a currency column formatted as "Sold" #,##0.00. That format string contains the letter d, and a naive check for date-like characters will find it — and quietly convert an entire column of prices into dates in 2023. The format code has to be scanned outside its quoted literals, or your revenue becomes a calendar.

The schema is guessed from a sample, and the file is not the sample

Most importers read the first few hundred rows to work out the shape, then read the whole file to load it. That is two different views of the same data, and the gap between them is where columns go missing.

A field that first appears on row 551 is in the file but not in the sample. It gets a column in neither the schema nor the table, and the load reports success. Nobody is told. The same thing happens to a column that holds clean integers for four hundred rows and then a N/A: sampled, it is an integer; loaded, it is an integer with seven failed rows in it.

The fix is not a bigger sample. It is to plan from the rows you are about to write — read the file once, in full, decide the columns from what is actually there, and then create the table. A preview may still sample, because a spreadsheet's columns are its first row and showing them quickly is worth something. The run must not.

One column, two kinds of thing

Real spreadsheets have a column that is a number 493 times and text 7 times, because somebody typed "approx 40" in a hurry two years ago. There are three ways to handle that, and only one of them is honest:

The third one costs nothing extra — the file has already been read — and it is the only one where the person who knows what "approx 40" meant gets to decide what happens to it.

The three usual routes into SQL Server, and where each one bites

Everything above happens inside whichever route you take. These are the three people reach for first, and the particular way each one loses data.

1. The SQL Server Import and Export Wizard

Right-click a database in SSMS, Tasks → Import Data, choose Microsoft Excel as the source. It is the quickest route and the one with the most famous trap: the Excel driver it uses decides each column’s type from the first eight rows (the TypeGuessRows setting). If those eight rows are numbers, the column is numeric — and every text value further down comes back as NULL, without an error. The import reports success.

Two other things you will meet: the wizard needs the Microsoft Access Database Engine installed in the same bitness as the wizard itself, which is where “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine” comes from; and it creates NVARCHAR(255) and FLOAT columns unless you edit the mappings, so check them before pressing Finish.

2. OPENROWSET with the ACE provider

The same driver, from T-SQL, so the import can be scripted:

SELECT *
INTO   dbo.Sales_Import
FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',
         'Excel 12.0 Xml;Database=C:\data\sales.xlsx;HDR=YES;IMEX=1',
         'SELECT * FROM [Sheet1$]');

IMEX=1 is the important part: it tells the driver to read a column holding mixed types as text instead of nulling the minority. Even so, this route needs the Access Database Engine installed on the SQL Server machine in 64-bit, Ad Hoc Distributed Queries switched on with sp_configure, and the file on the server’s own disk — three things a DBA will reasonably question. It is not available on Azure SQL Database at all.

3. Save as CSV, then BULK INSERT

The most portable route, and the one that throws away the most. A CSV has no types, so dates become whatever the spreadsheet displayed, leading zeros on IDs are only kept if the cells were text, and Excel on a German machine writes semicolons as separators and commas as decimal points:

BULK INSERT dbo.Sales_Staging
FROM 'C:\data\sales.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ';', CODEPAGE = '65001');

-- then convert, saying which culture the text was written in
SELECT TRY_PARSE(Amount AS DECIMAL(19,4) USING 'de-DE') AS Amount,
       TRY_CONVERT(DATE, OrderDate, 104)                AS OrderDate   -- dd.mm.yyyy
FROM   dbo.Sales_Staging;

Load into an all-NVARCHAR staging table first and convert afterwards, as above: a DECIMAL column will refuse 19,90 outright, and a refused row in the middle of a bulk load is much harder to find than a NULL from TRY_PARSE you can count. FORMAT = 'CSV' (SQL Server 2017 and later) is what makes quoted fields containing the separator work.

None of the three reads the workbook’s styles, none reads the whole sheet before deciding the types, and none tells you how many values in a column did not fit. Which is the list below.

What a careful import actually does

1. Reads the whole file before creating anything. The columns come from the rows that are about to be written, not from a sample of them.
2. Reads the workbook's styles, so a date is a date and a price formatted with the word "Sold" in it is still a price.
3. Reports the mess rather than resolving it silently. How many values of each shape a column really holds, shown before the table is made.
4. Lets every inference be overridden. Rename the column, change the type, leave the column out entirely. Asking somebody to write a schema for a messy file up front asks them to already understand the mess; showing them the inference and letting them correct it does not.
5. Creates the table and inserts the rows in one transaction. A table that exists and is empty because the insert failed is worse than no table, because it looks finished.
6. Names the rows it could not convert. Not a count — the rows.

Getting the types right in SQL Server

Once the values are understood, the target types are mostly straightforward. Two are worth stating because they are so often wrong:

• Money is not FLOAT. Floating point cannot represent 0.1 exactly, and a column of prices summed as FLOAT will disagree with the spreadsheet by pennies that nobody can find. DECIMAL(19,4) is the safe default.
• An unsized NVARCHAR is a decision, not a default. NVARCHAR(MAX) cannot be indexed usefully and cannot be part of a key. Size it from the data you actually read.
• Dates with an offset lose the offset. DATETIME2 has nowhere to keep it. Convert to UTC on the way in and record that you did — silently dropping a timezone is how two systems come to disagree by an hour twice a year.

Where Netune fits

Netune imports a spreadsheet by doing all of the above, and then treats the result as what it is: a table in a database, ready to be profiled, modeled and built into a warehouse alongside everything else. The import is not a separate tool bolted on — it is the way data with no database behind it gets in.

It reads .xlsx and .xlsm directly, with no spreadsheet software and no heavyweight libraries involved — so no Access Database Engine, no bitness to match and no sp_configure. It shows you every column it inferred, what each one holds and where it is unsure, before anything is created. And it never writes into the database it read from — a rule that runs through everything Netune does. It is part of the free beta, and so is the same import for JSON files, nested arrays included.

Questions people ask