Data import from Excel to SQL Server failing to import all data

Question

I have an odd thing happening with importing data from Excel to SQL Server using the Import and Export Data tool.

The short of it is that I can see the data in Excel, but all of the data doesn’t import.

I have an excel workbook containing one spreadsheet. I can see the data in the columns and everything looks good. When I use the import wizard to import the data, I can see the columns and the preview data, but the data doesn’t all get transferred over when the import is executed. There are no errors and the import process finishes – it just inserts a bunch of null values into the table.

It seems to happen most with numbers – so let’s say I have three columns for first name, last name, and ID number. I’ll get all of the first and last names, and some of the ID numbers. I can’t find a pattern for what’s happening.

I had this happen once before and I noticed that there were two spreadsheets to choose from during the import process: DataImport and DataImport$ - and the first one would fail as described above and the second one would import flawlessly. This time there is only one spreadsheet to select.

I’m mainly using an XLS document and the 32-bit import wizard on a 64-bit system, but I’m willing to try anything.

Any ideas of why there are two spreadsheets (sometimes) and why this import would fail?

Dave

Solution 1

The answer to your question might involve the HOW data is stored in Excel.

I just ran into the same problem. I had a column containing two types of values: numeric and numeric with a dash. (ex. 51000 and 2009-00949) I needed to import them as either NVARCHAR or VARCHAR, but the SQL import wizard (SQL2005) would only import the numerics and not the numerics with a dash or the numerics with a dash and not the numerics, depending on which type was in the first record. The values which were not imported would have a 'null' value in the field, rendering my records useless.

I tried using the Format|Cells menu option in Excel (numeric, text, general, etc) and various mapping options in SQL (nvar, nvarchar, varchar, sqlvariant, float) without success.

The solution was to use the Data|Text To Columns option in Excel, not the cell formatting option to store the numeric value as text. When I used this on the column, the numeric values (ex. 51000) converted to numbers stored as a text value (little green triangle error appeared in the upper left corner), the numerics with a dash remained as text. I had to leave the data in this format, with the little green triangle error, otherwise it would not successfully import to SQL. When the data in the column was stored in this format, the two types of column data (numeric and numeric with a dash) imported correctly with the other record data, no null values in the column.

Solution 2

My issue was that the file that I was importing was an older and somewhat maligned excel file. I opened up the file and saved it as a .xlsx and the file imported successfully with no null data after that.

Solution 3

I was able to solve the problem by sorting the data so that the text data was on top. That way SQL Server knew it was text data, not numeric data.

Solution 4

I faced exactly the same issue Using .xlsx file solved my problem.

View more solutions

Comments

[Dave Mroz]

Yeah, I'm using the Wizard. Each time I manually check (or create in some cases) the mappings so I'm pretty sure they're correct. I've used excel to transfer millions of rows over the years, but I have one client that provides an import file that doesn't always work, so I'm at a loss.

[Dave Mroz]

Unfortunately I can't share this spreadsheet - and since my theory is that manually editing the spreadsheet contaminate the data, I don't know if editing it and uploading it would be of value. I can give it a try, though, if you think that would help.

[Semih Yagcioglu]

Could you try to save the excel file as csv and import it as csv file? It may give us a deeper understanding of what causes this issue.

[Dave Mroz]

I exported the file as a tab-delimited file and then reimported it and it worked. This solution will ultimately work in the long run, but I'm interested to find out why Excel is failing.

[Semih Yagcioglu]

I am happy that it worked. I totally agree with you for the long run. This is really interesting.

[Dave Mroz]

I was working on this issue again this week, so your reply is timely. The thing I've noticed is that formatting the spreadsheet does help, but it's not foolproof. I've had a few cases where formatting the data as you described helps and a few where it doesn't. And I can't find the rhyme or reason behind it.

[J. Clay]

+100! BRILLIANT. I have been fighting this on and off forever and the solution is so simple. I wish I would have thought about it. THANK YOU!

[Neil]

Thank you! This was driving me nuts, and the Data|Text To Columns was the solution!

[Taylor Brown]

Data > Text To Columns worked for me, this was my exact scenario. Thank you.

[Dave Mroz]

I can't use CSV, but saving an XLSX as an XLS and using the 32-but import program seems to give me somewhat reliable results.

[Vinnie Fusca]

Just want to add this is still relevant in 2021 with modern versions of Excel/O365, SQL Server 2019, and SSMS 18.10.