CSV文件的SQL Server BULK INSERT错误（“ IID_IColumnsInfo”） [英] ("IID_IColumnsInfo") error with SQL Server BULK INSERT of CSV file

问题描述

我是SQL Server的新手，所以请原谅我有点菜鸟。

I'm new to SQL Server, so forgive me for being a bit of a noob here.

此处显示的代码返回以下错误：

The code shown here returns the following error:

无法从OLE DB访问接口 BULK获得链接服务器（空）的必需接口（ IID_IColumnsInfo）。

Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

代码：

BULK INSERT testingtable
FROM 'D:\TimeLords\data\db-test-file.csv'
WITH
    (FORMAT = 'CSV', 
     FIELDQUOTE = '"',
     FIRSTROW = 2,
     FIELDTERMINATOR = ',', 
     ROWTERMINATOR = '\n',  
     TABLOCK)

我尝试使用：

ROWTERMINATOR = '0x0a'

和

ROWTERMINATOR = '\r\n'

这是CSV文件： https://gyazo.com/0392b660c97e3cac27f2337993190c69

这是我的SQL表： https：// gyazo.com/fbbaf6204df9bb574d8887864cc95ea0

这是完整的SQL查询： https://gyazo.com/ffe020437f07524ce44420bedeebf0d4

And this is the complete SQL query: https://gyazo.com/ffe020437f07524ce44420bedeebf0d4

我已经对StackOverflow进行了搜索，找不到任何可行的解决方案。任何想法将不胜感激。

I've scouted StackOverflow and can't find any solution which works. Any ideas would be appreciated.

谢谢

推荐答案

还有另一种潜力罪魁祸首。我一直在SQL Server 2017 Express中运行BULK INSERT，并且我的语法使用FORMAT ='CSV'和ROWTERMINATOR为'\n'-并且已经工作了好几个月了。

There's another potential culprit. I've been running BULK INSERTs into my SQL Server 2017 Express, and my syntax used FORMAT = 'CSV' and a ROWTERMINATOR of '\n' -- and it had been working fine for months.

我在另一个系统中添加了一个新列，在该系统中我通常将数据导出为CSV，而当我去做另一个批量插入时，它失败了，因为我的CSV中有一个额外的列与我的SQL表中的列不一致。 DOH！我只需要在SQL数据库中添加相同的新列，一切就恢复了。我这是一个愚蠢的错误，但也许会对其他人有所帮助。

I added a new column to the other system where I was routinely exporting data as a CSV, and when I went to do another BULK INSERT, it was failing because I had an extra column in my CSV that didn't line up with the columns in my SQL table. DOH! I just needed to add that same new column in my SQL db and all was well again. A stupid error on my part, but maybe it will help someone else.

这篇关于CSV文件的SQL Server BULK INSERT错误（“ IID_IColumnsInfo”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！