I have an odd thing happening with importing data from Excel to SQL Server using the Import and Export Data tool.
使用导入和导出数据工具从Excel导入数据到SQL服务器，我有一个奇怪的事情发生。
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.
我有一个包含一个电子表格的excel工作簿。我可以看到列中的数据，一切看起来都很好。当我使用import向导来导入数据时，我可以看到列和预览数据，但是在执行导入时，数据不会全部转移。没有错误和导入过程结束——它只是将一堆空值插入到表中。
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.
这似乎是最常见的数字——假设我有三个列，分别是名、姓和ID号。我会得到所有的姓和名，以及一些ID号。我找不到发生事情的模式。
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.
我以前遇到过这种情况，我注意到在导入过程中有两个电子表格可供选择:DataImport和DataImport$——第一个会像上面描述的那样失败，第二个会完美地导入。这一次只有一个电子表格可供选择。
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.
我主要在64位系统上使用XLS文档和32位导入向导，但我愿意尝试任何东西。
Any ideas of why there are two spreadsheets (sometimes) and why this import would fail?
对于为什么会有两个电子表格(有时)以及为什么这种导入会失败有什么想法吗?
Dave
戴夫
9 个解决方案
#1
33
The answer to your question might involve the HOW data is stored in Excel.
您的问题的答案可能涉及如何在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.
我遇到了同样的问题。我有一个列包含两种类型的值:带破折号的数值和数值。(前。51000年和2009 - 00949年)我需要导入它们为NVARCHAR或VARCHAR,但是SQL import向导(SQL2005)只会导入数字而不是数字破折号或少量而不是数字的数字,这取决于类型的记录。未导入的值将在字段中具有“null”值，这将使我的记录无效。
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.
我尝试使用Excel中的格式|单元菜单选项(数字、文本、通用等)和SQL中的各种映射选项(nvar、nvarchar、varchar、sqlvariant、float)，但没有成功。
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中的Columns选项，而不是单元格格式选项来将数字值存储为文本。当我在列上使用这个值时，数值(例如51000)转换为作为文本值存储的数字(左上角出现了小的绿色三角形错误)，带有破折号的数字仍然是文本。我不得不保留这种格式的数据，带有一个小的绿色三角形错误，否则它将不能成功地导入到SQL。当列中的数据以这种格式存储时，两种列数据(数字和带破折号的数字)与其他记录数据一起被正确导入，列中没有空值。
#2
1
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.
我的问题是，我正在导入的文件是一个较旧的、有点不和谐的excel文件。我打开文件并将其保存为.xlsx文件，并成功导入该文件，之后没有空数据。
#3
1
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.
我可以通过对数据进行排序来解决这个问题，这样文本数据就在顶部。这样，SQL Server就知道它是文本数据，而不是数字数据。
#4
0
You're using the Import wizard, no? Click on Edit Mappings on the "Select Source Tables and Views" page of the wizard, and check the Type, Nullable, and Size columns for each source column. It sounds like something's not getting mapped properly.
您正在使用导入向导，不是吗?单击向导的“选择源表和视图”页面上的编辑映射，检查每个源列的类型、可空列和大小列。听起来有些东西没有被正确映射。
#5
0
Most probably that supposedly blank row causing Sql Server to ignore the rest of the data. Bu to be sure could you share the schema of the excel file and the first five rows? The correct three and the next two records.
大多数情况下，可能是空白行导致Sql Server忽略其他数据。Bu确定您是否可以共享excel文件的模式和前五行?正确的3和后面的2条记录。
#6
0
Select all rows of that column and change type to Text in excel then Select the whole column and change the type to Text Will definitely work.
选择该列的所有行并在excel中将类型更改为文本，然后选择整个列并将类型更改为文本将肯定有效。
#7
0
I am also having the issue. The only way that I was able to resolve it was to save the .xlsx files as .csv files and then use the .csv files into the SQL Server "Import Data".
我也有这个问题。我能够解析它的唯一方法是将.xlsx文件保存为.csv文件，然后将.csv文件使用到SQL服务器的“导入数据”中。
It would seem that the integration between Microsoft Excel and Microsoft SQL Server would be cleaner.
微软Excel和微软SQL Server之间的集成看起来会更简洁。
My versions are: Excel 2013. SQL Server 2012.
我的版本是:Excel 2013。SQL Server 2012。
#8
0
I faced exactly the same issue Using .xlsx file solved my problem.
使用.xlsx文件解决了同样的问题。
#9
0
Finally formatting cells to general worked for me where there is numeric value, earlier it was showing %s but now it is solved.
最后，格式化单元格在有数值的地方对我有用，之前显示的是%s，但现在已经解决了。
#1
33
The answer to your question might involve the HOW data is stored in Excel.
您的问题的答案可能涉及如何在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.
我遇到了同样的问题。我有一个列包含两种类型的值:带破折号的数值和数值。(前。51000年和2009 - 00949年)我需要导入它们为NVARCHAR或VARCHAR,但是SQL import向导(SQL2005)只会导入数字而不是数字破折号或少量而不是数字的数字,这取决于类型的记录。未导入的值将在字段中具有“null”值，这将使我的记录无效。
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.
我尝试使用Excel中的格式|单元菜单选项(数字、文本、通用等)和SQL中的各种映射选项(nvar、nvarchar、varchar、sqlvariant、float)，但没有成功。
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中的Columns选项，而不是单元格格式选项来将数字值存储为文本。当我在列上使用这个值时，数值(例如51000)转换为作为文本值存储的数字(左上角出现了小的绿色三角形错误)，带有破折号的数字仍然是文本。我不得不保留这种格式的数据，带有一个小的绿色三角形错误，否则它将不能成功地导入到SQL。当列中的数据以这种格式存储时，两种列数据(数字和带破折号的数字)与其他记录数据一起被正确导入，列中没有空值。
#2
1
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.
我的问题是，我正在导入的文件是一个较旧的、有点不和谐的excel文件。我打开文件并将其保存为.xlsx文件，并成功导入该文件，之后没有空数据。
#3
1
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.
我可以通过对数据进行排序来解决这个问题，这样文本数据就在顶部。这样，SQL Server就知道它是文本数据，而不是数字数据。
#4
0
You're using the Import wizard, no? Click on Edit Mappings on the "Select Source Tables and Views" page of the wizard, and check the Type, Nullable, and Size columns for each source column. It sounds like something's not getting mapped properly.
您正在使用导入向导，不是吗?单击向导的“选择源表和视图”页面上的编辑映射，检查每个源列的类型、可空列和大小列。听起来有些东西没有被正确映射。
#5
0
Most probably that supposedly blank row causing Sql Server to ignore the rest of the data. Bu to be sure could you share the schema of the excel file and the first five rows? The correct three and the next two records.
大多数情况下，可能是空白行导致Sql Server忽略其他数据。Bu确定您是否可以共享excel文件的模式和前五行?正确的3和后面的2条记录。
#6
0
Select all rows of that column and change type to Text in excel then Select the whole column and change the type to Text Will definitely work.
选择该列的所有行并在excel中将类型更改为文本，然后选择整个列并将类型更改为文本将肯定有效。
#7
0
I am also having the issue. The only way that I was able to resolve it was to save the .xlsx files as .csv files and then use the .csv files into the SQL Server "Import Data".
我也有这个问题。我能够解析它的唯一方法是将.xlsx文件保存为.csv文件，然后将.csv文件使用到SQL服务器的“导入数据”中。
It would seem that the integration between Microsoft Excel and Microsoft SQL Server would be cleaner.
微软Excel和微软SQL Server之间的集成看起来会更简洁。
My versions are: Excel 2013. SQL Server 2012.
我的版本是:Excel 2013。SQL Server 2012。
#8
0
I faced exactly the same issue Using .xlsx file solved my problem.
使用.xlsx文件解决了同样的问题。
#9
0
Finally formatting cells to general worked for me where there is numeric value, earlier it was showing %s but now it is solved.
最后，格式化单元格在有数值的地方对我有用，之前显示的是%s，但现在已经解决了。