批量插入时出现错误

Question

当我尝试进行批量插入时，出现了错误：

BULK INSERT #tbl_InterCompanyUploadDetail_Staging 
FROM '\\SVRP03546008461D\QA\UploadTemplatewithvalidation.xlsx'
 WITH (FIRSTROW = 6, FIELDTERMINATOR ='\t', ROWTERMINATOR ='\\n' )

我收到的错误信息如下：

批量加载数据转换错误（截断）第6行，第2列（Oracle公司代码）。

Excel表格中的该列数据为470，而数据库中该列是varchar（10）类型。 因此，造成这个错误的原因是什么呢？

- Anirudh Agarwal

---

正如错误信息所说，截断是错误的原因。假设数据中的列超过10个字符。 - Gordon Linoff

不，我只有十行数据，每行都有三个字符。这就是为什么我感到困惑的原因。 - Anirudh Agarwal

我不知道 BULK INSERT 可以插入 .xlsx 文件。除非这是一个新功能……我不认为这是可能的。如果你在记事本中打开这个文件，它看起来像什么？ - Nick.McDermaid

文档解释得非常清楚：从Excel导入数据到SQL Server。BULK INSERT 只支持纯文本文件，无法读取Excel文件。 - Vladimir Baranov

解释：首先，您需要将xlsx文件导出为文本文件（例如CSV），然后再导入该文本文件。翻译：明确说明一下，您需要先将xlsx文件导出为文本文件（比如CSV格式），再将该文本文件导入。 - Nick.McDermaid

Answer 1

问题

BULK INSERT 可能无法使用 xlsx 文件，尝试将 .xlsx 文件转换为 .csv 文件以实现此目的 (使用 BULK INSERT)

第一种解决方案 - 使用 OPENROWSET

尝试使用 Microsoft.ACE.OLEDB.12.0 提供程序和 OPENROWSET：

Insert into <rawdatatable> 
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                         'Excel 12.0;Database=D:\SSIS\FileToLoad.xlsx;HDR=YES', 
                         'SELECT * FROM [Sheet1$]')

或者

SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=D:\Desktop\Data.xlsx', [Sheet1$]);

第二种解决方案 - 使用OPENDATASOURCE

SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\Desktop\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];

参考资料

• 将 Excel (.Xlsx) 或 (.Xls) 文件导入到 SQL Server
• 从 Excel 导入数据到 SQL Server 或 Azure SQL 数据库
• 如何从 XLSX 文件批量插入数据？

- Hadi

Answer 2

将 "\n" 替换为 "0x0a" 作为 ROWTERMINATOR，然后再试一次。

或者也可以

 ROWTERMINATOR = '''+cast (0x0000 as char(1))+'''

如果它有效，请告诉我。

还要检查this。

- Liquid Core

---

'+cast (0x0000 as char(1))+'' 这个不起作用，当使用 "0x0a" 时会出现错误："行的批量加载数据转换错误（截断）"。 - Anirudh Agarwal

你也检查了链接吗？ - Liquid Core

Answer 3

我怀疑使用BULK INSERT与XLSX文件不兼容。如果支持XLSX文件，则不需要使用FIELDTERMINATOR和ROWTERMINATOR。

XLSX是Zip文件，因此我猜测（但不确定）XLSX不受支持，并且您遇到截断错误，因为它将其读取为纯文本文件，并且BULK INSERT获取了长文本直到FIELDTERMINATOR。

要确认，您可以尝试将列长度增加到几千个字符并运行BULK INSERT，如果您收到垃圾字符，则代表它将其读取为纯文本文件。也许垃圾字符可能与您在记事本或Notepad ++中打开相同的xlsx文件时一样。

- par

Answer 4

你不能批量加载XLSX文件到SQL Server。你可以将XLSX文件转换为制表符分隔的文本文件，然后进行批量加载。

如果这只是一次性操作，我建议先转换为文本（但要注意Excel如何导出某些类型，如日期和大数字）。或者你可以使用导入/导出向导（https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql）

如果这是一个需要重复执行的过程，我会创建一个SSIS脚本。

- Jeremiah Cooper