The xlsx is on disk, the table is empty, and the wizard says it copied 4 rows. Then you subtract: the spreadsheet had 6. Two rows went somewhere, and nothing in the log says where.
That is the shape of most “excel to sql server” searches: not “how do I import”, but “why is the data wrong now”. The questions with the most views on Stack Overflow are about exactly this — Error converting data types when importing from Excel to SQL Server (137,275 views), failing to import all data (77,954), and the fine detail default varchar 255 of a column (20,618). Three different failure modes hide behind one wizard.
This is about what those three are, how to see them before they happen, and what our own tool does and does not protect you from. Everything below is measured on a 4-row file, so you can reproduce it.
1. The three ways an Excel import goes wrong
An importer has to decide, without asking you, three things: what type each column is, what to do with a cell that does not fit, and what to do with the rows already written when one fails. Its defaults, not Excel’s data, decide the outcome.
- The type. Most importers create
varchar(255)columns unless told otherwise, or guess from the first few rows. - The bad cell. A value that does not fit either becomes NULL or kills the row.
- The half-done import. Rejected rows are skipped; the accepted ones may already be committed.
Each of these produces a “successful” import with wrong data, and each has a different tell.
2. Trap 1: the type you never chose
Take a real spreadsheet column: ZIP codes. 01234, 00501, 00012. Excel keeps them as text, which is correct — the leading zero is part of the code.
Now the importer decides. SQL Server’s own wizard creates varchar(255) for unrecognised columns, which is safe for the ZIP but wrong for everything else; a tool that guesses numeric turns it into 1234, 501, 12, silently, because 01234 is a valid number.
Both directions are dangerous:
- Too wide —
varchar(255)on every column means long text is fine, but dates, amounts and integers all arrive as text, and every later query needs casting. - Too narrow — an integer or
varchar(10)where the data is wider: rows get rejected (trap 3) or values get truncated.
The fix is one step, and it happens before the import: look at the column list the tool proposes and change the types yourself. Our wizard shows them as an editable grid — zip as varchar(255), when as timestamp, qty as integer, amount as numeric — and the spreadsheet is the only thing that decided them.
That is also the answer to the varchar(255) question on Stack Overflow: you are not supposed to accept the default, you are supposed to name the type.
3. Trap 2: the cell that becomes empty
The second trap is quieter. One cell in a numeric column says n/a. Excel does not mind; the column is “mostly numbers”.
Import it with qty typed as integer and watch what happens: no error, no warning, all 4 rows written — and qty has 3 values, not 4. The n/a became NULL.
That is the friendliest version of the trap. SQL Server’s wizard is harsher: rows that do not convert are dropped and the summary still reads “copied N rows”, which is where the failing to import all data thread comes from. Same cause, two symptoms: a cell that does not fit its column.
What to check, in the file, before importing:
- Empty vs missing. An empty cell and a cell with a space are different; both become NULL, and NULL is not the same as 0 or
''. - One text cell in a numeric column. Sort the column; the
n/a, the-, theTBDsit at one end. - Formulas. If a formula was never calculated in Excel (no cached value), the cell reads as empty to every importer. Open the file, let Excel recalculate, save.
- Merged cells. Only the top-left cell of a merged range holds a value; the rest are empty. Our tool takes the sheet as a grid, so a merged header shows up as one value and blanks.
None of this is visible after the import. A NULL looks like a NULL, not like a decision.
4. Trap 3: the import that stops halfway
The third trap is the one that turns a data problem into a reconciliation problem. A value does not fit the target column — say a note of 40 characters into varchar(10) — and the import reports it correctly:
“ At: 2, value too long for type character varying(10) Write error at index: 2 1 records, Elapsed: 00:00:00.03 1 records can not convert. 1 record(s) written, 1 failed “
Row 2 is rejected with its row number. And row 1 is already in the table. The process exits with code 3, not 0, so a script can tell — but a person watching the progress bar sees “100%” and a green bar.
So the question to ask of any importer, including ours: when row 2 fails, is the transaction rolled back, or is row 1 kept? Ours keeps it, and says so in the log. If you need all-or-nothing, import into a scratch table first, check the counts, then move the rows.
This is why “the import said 4 records” is not evidence. The number to compare is the spreadsheet’s row count against select count(*) after the import, and the log’s failed count against zero.
5. What to check before you click Next
Four questions, in this order, and each one takes a minute:
- What type is each column? Open the column list, read it out loud. If a ZIP, a phone number or an ID has a leading zero, it is text, not a number.
- Which cells are empty or not-a-number? Sort each numeric column. Anything at the top that is not a number will become NULL or kill a row.
- How many rows should arrive? Excel shows the count in the status bar when you select the range; note it down before importing.
- What happens to the rows already written when one fails? Try one failing row on purpose in a scratch table. If the good rows stay, you need the count check; if it rolls back, you do not.
Those four answers explain almost every “the import is wrong” case, including the ones where the tool behaved exactly as documented.
6. Doing it in the wizard
The three screens below are FileToDB, which reads one sheet of an xlsx at a time and writes the rows into a table you define. The whole point of using it here is that the column types are shown to you before the first row is written.
First the source: the file, the sheet (a workbook can have several), whether the first row is a header, and a preview of the raw cells.
Note what the preview shows: 01234 with its zero, the date as 2024-04-03, and n/a sitting in the qty column. This is the last moment where the file is still the file.
Then the target table, and the column grid — the whole point of this article:
zip is varchar(255) because the values have leading zeros; when is timestamp; qty is integer even though one cell is n/a — and that cell will arrive as NULL, which is the honest outcome for “not a number”. If qty should reject the row instead, type it as something narrower and let trap 3 do its job.
Then Convert Data: 4 records, one log line per decision.
7. From the command line
The same session, without the wizard:
“ FileToDB import ^ --from="orders.xlsx" ^ --to="SQLServer:user:pass@host:1433/db" ^ --table=orders logfile=orders.log “
The exit code is the part that matters in a script: 0 all rows written, 3 some rows failed. A cron job that ignores the code will happily report success for a half-written table.
8. FAQ
Why did my ZIP codes lose their leading zeros?
The column was created as a number. Import it as varchar — the values are codes, not quantities.
Why are some rows missing, with no error?
A cell in those rows did not fit its column type. SQL Server’s wizard drops them; our tool logs At: and exits 3. Compare the spreadsheet’s row count with select count(*).
Why did a numeric column get NULLs?
A cell held text (n/a, -, a space). NULL is what “not a number” becomes unless the row is rejected.
The dates came in wrong.
Excel stores dates as numbers with a format. If the target column is text, you get the serial number or a locale-formatted string; type the column as a date and pick the format in the mapping.
Can it read more than one sheet?
Yes — the Sheet dropdown lists them. Each sheet is a separate import; one table per sheet.
Will it work with 500,000 rows?
The xlsx format itself caps at 1,048,576 rows. Reading is streaming, so a large sheet takes memory in proportion to the batch, not the file; if the file is bigger than Excel can hold, it is not an xlsx problem any more — see CSV files that Excel cannot open.
Two of the three traps are the importer’s defaults, and defaults are changeable: the column types are a grid you can edit, and the sheet is a dropdown. The third is a transaction question, and the only honest answer is to look at the log and the count.
FileToDB imports an xlsx into SQL Server, PostgreSQL, MySQL, Oracle, SQLite and DB2, with the column types in front of you and one log line per rejected row. If you already have a “4 easy steps” workflow, it still applies — Excel to PostgreSQL and Excel to SQL Server cover the steps; this article is about the step after, when the numbers do not match.