使用Openrowset导入错误

Question

我正在尝试用数字和字母数字值导入一个列(从.csv文件)，但是当我运行openrowset过程时，它正确地导入数字行，但对于字母数字值，它默认为null。

表A

ID,A,B,C
1,12,hh,i
2,ab12,tt,b
3,2,aa,o
4,bc12,ee,l

所用代码

SELECT 
    * 
FROM 
    OPENROWSET
        (
            'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;IMEX=1;','SELECT * FROM abc.csv'
        ) t

我用的是IMEX =1，一点变化都没有。

Answer 1

问题的原因是Oledb提供者

当导入带有混合数据类型列的csv文件或excel文件时，它将用null替换非主导类型。(使用Oledb或Ace.Oledb)

解决方案

您可以通过添加包含字符串值的第一行来解决一些问题，然后在完成后删除它。

ID,A,B,C
0,a,a,a
1,12,hh,i
2,ab12,tt,b
3,2,aa,o
4,bc12,ee,l

如果使用IMEX=1，它将修复这个问题。

这将读取列为字符串，ID列为数字。(使用0)

或者将HDR=NO属性添加到连接字符串中，以便头是导入的第一行(其所有值都是字符串)

阅读有关此文章中的混合数据类型的更多信息。

其他方法

或者尝试在没有aceoledb提供程序的情况下实现这一点，只需以其他方式导入csv文件，如下所示：

使用Microsoft文本驱动程序

SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;',
'SELECT * FROM abc.csv')

使用大容量插入

CREATE TABLE dbo.MyTable 
(ID INTEGER,
 A VARCHAR(50),
 B VARCHAR(50),
 C VARCHAR(50)
)

BULK INSERT dbo.MyTable
FROM 'C:\abc.csv'
WITH 
  (
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
  )

这些条款中的其他细节：

• 如何使用OPENROWSET导入csv文件？
• 使用OpenRowSet读取CSV文件 (详细教程)

Answer 2

下面的例子应该有效。请注意，在我的系统中，我无法使用您使用的提供程序(因为我已经有一段时间没有安装它了，弄清楚要安装什么会让我抓狂)。关键部分:您需要一个格式文件来告诉SQL服务器每个列中的预期值。这里有一个很好的博客文章在查询中使用csv，我不能粘贴XMl，因为版本可能会根据数据库的不同而有所不同：

创建xml文件的最简单方法是：

1)在数据库中创建一个表。你不会插进去的。它只允许您使用下一个命令创建表：

CREATE TABLE dbo.TestInsert 
(ID INTEGER,
 A VARCHAR(10),
 B VARCHAR(10),
 C VARCHAR(10)
)

2)接下来，运行以下命令行bcp程序来生成适当的文件：

bcp YourDatabase.dbo.TestInsert format nul -c -x -fimport.xml -t, -T

3)最后，使用import.xml作为您的格式文件。

SELECT 
  * 
FROM 
   OPENROWSET
    (
        BULK 'c:\test.csv', 
        FORMATFILE='c:\import.xml',
        FIRSTROW=2
    ) t