Error converting data types when importing from Excel to SQL Server<\/code> (137,275 views), failing to import all data<\/code> (77,954), and the fine detail default varchar 255 of a column<\/code> (20,618). Three different failure modes hide behind one wizard.<\/p>\nThis 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.<\/p>\n
1. The three ways an Excel import goes wrong<\/h2>\n
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.<\/p>\n
\n- The type.<\/strong> Most importers create
varchar(255)<\/code> columns unless told otherwise, or guess from the first few rows.<\/li>\n- The bad cell.<\/strong> A value that does not fit either becomes NULL or kills the row.<\/li>\n
- The half-done import.<\/strong> Rejected rows are skipped; the accepted ones may already be committed.<\/li>\n<\/ul>\n
Each of these produces a “successful” import with wrong data, and each has a different tell.<\/p>\n
2. Trap 1: the type you never chose<\/h2>\n
Take a real spreadsheet column: ZIP codes. 01234<\/code>, 00501<\/code>, 00012<\/code>. Excel keeps them as text, which is correct \u2014 the leading zero is part of the code.<\/p>\nNow the importer decides. SQL Server’s own wizard creates varchar(255)<\/code> for unrecognised columns, which is safe for the ZIP but wrong for everything else; a tool that guesses numeric turns it into 1234<\/code>, 501<\/code>, 12<\/code>, silently, because 01234<\/code> is a valid number.<\/p>\nBoth directions are dangerous:<\/p>\n
\n- Too wide<\/strong> \u2014
varchar(255)<\/code> on every column means long text is fine, but dates, amounts and integers all arrive as text, and every later query needs casting.<\/li>\n- Too narrow<\/strong> \u2014 an integer or
varchar(10)<\/code> where the data is wider: rows get rejected (trap 3) or values get truncated.<\/li>\n<\/ul>\nThe fix is one step, and it happens before<\/em> the import: look at the column list the tool proposes and change the types yourself. Our wizard shows them as an editable grid \u2014 zip<\/code> as varchar(255)<\/code>, when<\/code> as timestamp<\/code>, qty<\/code> as integer<\/code>, amount<\/code> as numeric<\/code> \u2014 and the spreadsheet is the only thing that decided them.<\/p>\nThat is also the answer to the varchar(255)<\/code> question on Stack Overflow: you are not supposed to accept the default, you are supposed to name the type.<\/p>\n3. Trap 2: the cell that becomes empty<\/h2>\n
The second trap is quieter. One cell in a numeric column says n\/a<\/code>. Excel does not mind; the column is “mostly numbers”.<\/p>\nImport it with qty<\/code> typed as integer<\/code> and watch what happens: no error, no warning, all 4 rows written \u2014 and qty<\/code> has 3 values, not 4. The n\/a<\/code> became NULL.<\/p>\nThat 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<\/code> thread comes from. Same cause, two symptoms: a cell that does not fit its column.<\/p>\nWhat to check, in the file, before importing:<\/p>\n
\n- Empty vs missing.<\/strong> An empty cell and a cell with a space are different; both become NULL, and NULL is not the same as 0 or
''<\/code>.<\/li>\n- One text cell in a numeric column.<\/strong> Sort the column; the
n\/a<\/code>, the -<\/code>, the TBD<\/code> sit at one end.<\/li>\n- Formulas.<\/strong> 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.<\/li>\n
- Merged cells.<\/strong> 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.<\/li>\n<\/ul>\n
None of this is visible after the import. A NULL looks like a NULL, not like a decision.<\/p>\n
4. Trap 3: the import that stops halfway<\/h2>\n
The third trap is the one that turns a data problem into a reconciliation problem. A value does not fit the target column \u2014 say a note of 40 characters into varchar(10)<\/code> \u2014 and the import reports it correctly:<\/p>\nAt: 2, value too long for type character varying(10)\nWrite error at index: 2\n1 records, Elapsed: 00:00:00.03\n1 records can not convert.\n1 record(s) written, 1 failed<\/code><\/pre>\nRow 2 is rejected with its row number. And row 1 is already in the table<\/strong>. The process exits with code 3, not 0, so a script can tell \u2014 but a person watching the progress bar sees “100%” and a green bar.<\/p>\nSo 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.<\/p>\n
This is why “the import said 4 records” is not evidence. The number to compare is the spreadsheet’s row count against select count(*)<\/code> after the import, and the log’s failed<\/code> count against zero.<\/p>\n5. What to check before you click Next<\/h2>\n
Four questions, in this order, and each one takes a minute:<\/p>\n
\n- What type is each column?<\/strong> 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.<\/li>\n
- Which cells are empty or not-a-number?<\/strong> Sort each numeric column. Anything at the top that is not a number will become NULL or kill a row.<\/li>\n
- How many rows should arrive?<\/strong> Excel shows the count in the status bar when you select the range; note it down before importing.<\/li>\n
- What happens to the rows already written when one fails?<\/strong> 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.<\/li>\n<\/ol>\n
Those four answers explain almost every “the import is wrong” case, including the ones where the tool behaved exactly as documented.<\/p>\n
6. Doing it in the wizard<\/h2>\n
The three screens below are FileToDB<\/a>, 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.<\/p>\nFirst 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.<\/p>\n
![\"Open](\"https:\/\/www.withdata.com\/wp\/wp-content\/uploads\/2026\/11\/xlimport-01-open-source.png\")
<\/p>\n
Note what the preview shows: 01234<\/code> with its zero, the date as 2024-04-03<\/code>, and n\/a<\/code> sitting in the qty<\/code> column. This is the last moment where the file is still the file.<\/p>\nThen the target table, and the column grid \u2014 the whole point of this article:<\/p>\n
![\"Create](\"https:\/\/www.withdata.com\/wp\/wp-content\/uploads\/2026\/11\/xlimport-02-create-table.png\")
<\/p>\n
zip<\/code> is varchar(255)<\/code> because the values have leading zeros; when<\/code> is timestamp<\/code>; qty<\/code> is integer<\/code> even though one cell is n\/a<\/code> \u2014 and that cell will arrive as NULL, which is the honest outcome for “not a number”. If qty<\/code> should reject the row instead, type it as something narrower and let trap 3 do its job.<\/p>\nThen Convert Data: 4 records, one log line per decision.<\/p>\n
![\"Convert](\"https:\/\/www.withdata.com\/wp\/wp-content\/uploads\/2026\/11\/xlimport-03-convert.png\")
<\/p>\n
7. From the command line<\/h2>\n
The same session, without the wizard:<\/p>\n
FileToDB import ^\n --from=\"orders.xlsx\" ^\n --to=\"SQLServer:user:pass@host:1433\/db\" ^\n --table=orders logfile=orders.log<\/code><\/pre>\nThe exit code is the part that matters in a script: 0<\/strong> all rows written, 3<\/strong> some rows failed. A cron job that ignores the code will happily report success for a half-written table.<\/p>\n8. FAQ<\/h2>\nWhy did my ZIP codes lose their leading zeros?<\/h3>\n
The column was created as a number. Import it as varchar<\/code> \u2014 the values are codes, not quantities.<\/p>\nWhy are some rows missing, with no error?<\/h3>\n
A cell in those rows did not fit its column type. SQL Server’s wizard drops them; our tool logs At: <\/code> and exits 3. Compare the spreadsheet’s row count with select count(*)<\/code>.<\/p>\nWhy did a numeric column get NULLs?<\/h3>\n
A cell held text (n\/a<\/code>, -<\/code>, a space). NULL is what “not a number” becomes unless the row is rejected.<\/p>\nThe dates came in wrong.<\/h3>\n
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.<\/p>\n
Can it read more than one sheet?<\/h3>\n
Yes \u2014 the Sheet dropdown lists them. Each sheet is a separate import; one table per sheet.<\/p>\n
Will it work with 500,000 rows?<\/h3>\n
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 \u2014 see CSV files that Excel cannot open<\/a>.<\/p>\nTwo 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.<\/p>\n
FileToDB<\/a> 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 \u2014 Excel to PostgreSQL<\/a> and Excel to SQL Server<\/a> cover the steps; this article is about the step after, when the numbers do not match.<\/p>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2772],"tags":[13295,470,228,13774,1200,13869],"class_list":["post-45886","post","type-post","status-publish","format-standard","hentry","category-filetodb","tag-excel-import","tag-excel-to-postgresql","tag-excel-to-sql-server","tag-filetodb","tag-import-excel-to-sql-server","tag-varchar255"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45886","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=45886"}],"version-history":[{"count":1,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45886\/revisions"}],"predecessor-version":[{"id":45887,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45886\/revisions\/45887"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=45886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=45886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=45886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}