I am trying to import data from MS Excel (Pro Plus 2013) to SQL Server 2014. Every time I attempt to do so I get the errors below. The columns in the the table are set to nvarchar(50) while in Excel none of the columns have more than 30 characters. I have tried recreating the Excel spreadsheet over and over again, just to be sure I am doing everything right. I just can't get the data to import.
我正在尝试将数据从MS Excel(Pro Plus 2013)导入到SQL Server 2014.每次尝试这样做时,我都会收到以下错误。表中的列设置为nvarchar(50),而在Excel中,没有列的字符数超过30个。我试过一遍又一遍地重新创建Excel电子表格,只是为了确保我做的一切都正确。我只是无法获取要导入的数据。
These are the errors I receive:
这些是我收到的错误:
Validating (Error) Messages:
验证(错误)消息:
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Agent_First_Name" with a length of 255 to database column "Agent_First_Name" with a length of 50.(SQL Server Import and Export Wizard)
警告0x802092a7:数据流任务1:由于将长度为255的数据流“Agent_First_Name”中的数据插入到长度为50的数据库列“Agent_First_Name”中,可能会发生截断。(SQL Server导入和导出向导)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Agent_Last_Name" with a length of 255 to database column "Agent_Last_Name" with a length of 50.(SQL Server Import and Export Wizard)
警告0x802092a7:数据流任务1:由于将长度为255的数据流“Agent_Last_Name”中的数据插入到长度为50的数据库列“Agent_Last_Name”中,可能会发生截断。(SQL Server导入和导出向导)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Agent_Email" with a length of 255 to database column "Agent_Email" with a length of 50.(SQL Server Import and Export Wizard)
警告0x802092a7:数据流任务1:由于将长度为255的数据流“Agent_Email”中的数据插入到长度为50的数据库列“Agent_Email”中,可能会发生截断。(SQL Server导入和导出向导)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Agent_Extension" with a length of 255 to database column "Agent_Extension" with a length of 4.(SQL Server Import and Export Wizard)
警告0x802092a7:数据流任务1:由于将长度为255的数据流“Agent_Extension”中的数据插入到长度为4的数据库列“Agent_Extension”中,可能会发生截断。(SQL Server导入和导出向导)
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Agent_Cell_Phone" with a length of 255 to database column "Agent_Cell_Phone" with a length of 10.(SQL Server Import and Export Wizard)
警告0x802092a7:数据流任务1:由于将长度为255的数据流“Agent_Cell_Phone”中的数据插入到长度为10的数据库列“Agent_Cell_Phone”中,可能会发生截断。(SQL Server导入和导出向导)
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "Agent_ID".(SQL Server Import and Export Wizard)
错误0xc0202049:数据流任务1:无法插入只读列“Agent_ID”。(SQL Server导入和导出向导)
Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.(SQL Server Import and Export Wizard)
错误0xc0202045:数据流任务1:列元数据验证失败。(SQL Server导入和导出向导)
Error 0xc004706b: Data Flow Task 1: "Destination - tableAgents" failed validation and returned validation status "VS_ISBROKEN".(SQL Server Import and Export Wizard)
错误0xc004706b:数据流任务1:“Destination - tableAgents”验证失败并返回验证状态“VS_ISBROKEN”。(SQL Server导入和导出向导)
Error 0xc004700c: Data Flow Task 1: One or more component failed validation.(SQL Server Import and Export Wizard)
错误0xc004700c:数据流任务1:一个或多个组件验证失败。(SQL Server导入和导出向导)
Error 0xc0024107: Data Flow Task 1: There were errors during task validation.(SQL Server Import and Export Wizard)
错误0xc0024107:数据流任务1:任务验证期间出现错误。(SQL Server导入和导出向导)
2 个解决方案
#1
1
It looks like you're trying to import into an existing table. I would recommend starting with importing the data into a new table (for example, "tmpAgent" -- and you can delete this table later). Allow SQL Server to pick the tables size of the import, or you can set the size in the "Edit mappings" section of the import.
看起来您正在尝试导入现有表。我建议从将数据导入新表开始(例如,“tmpAgent” - 您可以稍后删除该表)。允许SQL Server选择导入的表大小,或者可以在导入的“编辑映射”部分中设置大小。
After you have imported the data into a new table, then create a query to move it into your Agent table.
将数据导入新表后,创建查询以将其移动到代理表中。
#2
0
I encountered the same error message.
我遇到了同样的错误消息。
Turns out I had a rogue cluster of merged cells in the excel file. I un-merged those cells, re-tried the import, and all my data were successfully imported to the excel file.
结果我在excel文件中有一个合并单元格的流氓集群。我取消合并这些单元格,重新尝试导入,并且我的所有数据都成功导入到excel文件中。
#1
1
It looks like you're trying to import into an existing table. I would recommend starting with importing the data into a new table (for example, "tmpAgent" -- and you can delete this table later). Allow SQL Server to pick the tables size of the import, or you can set the size in the "Edit mappings" section of the import.
看起来您正在尝试导入现有表。我建议从将数据导入新表开始(例如,“tmpAgent” - 您可以稍后删除该表)。允许SQL Server选择导入的表大小,或者可以在导入的“编辑映射”部分中设置大小。
After you have imported the data into a new table, then create a query to move it into your Agent table.
将数据导入新表后,创建查询以将其移动到代理表中。
#2
0
I encountered the same error message.
我遇到了同样的错误消息。
Turns out I had a rogue cluster of merged cells in the excel file. I un-merged those cells, re-tried the import, and all my data were successfully imported to the excel file.
结果我在excel文件中有一个合并单元格的流氓集群。我取消合并这些单元格,重新尝试导入,并且我的所有数据都成功导入到excel文件中。