从Excel导入到SQL Server的数据导入未能导入所有数据 [英] Data import from Excel to SQL Server failing to import all data

问题描述

我使用导入和导出数据工具从Excel导入数据到SQL Server有一个奇怪的事情。

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

其中的一点是，我可以看到Excel中的数据，但所有的数据都不会导入。

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

我有一个包含一个电子表格的excel工作簿。我可以看到列中的数据，一切都看起来不错。当我使用导入向导导入数据时，我可以看到列和预览数据，但是在执行导入时数据并不全部被转移。没有错误，导入过程完成 - 它只是在表中插入一堆空值。

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.

似乎最多的是数字 - 所以让我们说三列名称，姓氏和身份证号码。我会得到所有的名字和名字，以及一些ID号码。我没有找到发生什么的模式。

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.

我曾经发生过一次，我注意到在导入过程中有两个电子表格可供选择：DataImport和DataImport $ - 并且第一个将如上所述失败，第二个将导入无瑕疵。这次只能选一个电子表格。

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.

我主要在64位系统上使用XLS文档和32位导入向导，但我愿意尝试任何东西。

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

推荐答案

您的问题的答案可能涉及到如何将数据存储在Excel中。

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

我刚遇到同样的问题。我有一个列包含两种类型的值：数字和数字与破折号。 （例如51000和2009-00949）我需要将它们导入为NVARCHAR或VARCHAR，但是SQL导入向导（SQL2005）只会导入数字，而不是使用破折号或数字的数字，而不是数字，取决于第一条记录中的哪一种。未导入的值将在该字段中具有null值，使我的记录无用。

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.

我尝试使用Excel中的格式|单元格菜单选项（数字，文本，一般等）和SQL（nvar，nvarchar，varchar，sqlvariant，float）中的各种映射选项，而不成功。

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.

解决方案是使用Data | Excel中的文本到列选项，而不是将数值存储为文本的单元格格式化选项。当我在列上使用它时，转换为存储为文本值的数字（小的绿色三角形错误出现在左上角）的数值（例如51000）），破折号的数字保持为文本。我不得不以这种格式离开数据，带有一点绿色的三角形错误，否则不会成功导入SQL。当列中的数据以此格式存储时，两种类型的列数据（带有破折号的数字和数字）与其他记录数据正确导入，列中没有空值。

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.

这篇关于从Excel导入到SQL Server的数据导入未能导入所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！