批量插入在SQL Server中正确引用CSV文件 [英] Bulk Insert Correctly Quoted CSV File in SQL Server

问题描述

我想要导入正确引用的CSV文件，这意味着数据只有在它包含逗号时才被引用，例如：

I'm trying to import a correctly quoted CSV file, meaning data is only quoted if it contains a comma, e.g.:

41，Terminator，Black
42，Monsters，Inc.，Blue

41, Terminator, Black 42, "Monsters, Inc.", Blue

我观察到第一行导入正确，但是第二行错误的建议引用的逗号被视为字段分隔符。

I observe that the first row imports correctly, but the second row errors in a manner that suggests the quoted comma was treated as a field separator.

我已经看到过这样的建议

I have seen suggestions such as this one

SQL从CSV大量导入

更改字段终止符

FIELDTERMINATOR ='，'

FIELDTERMINATOR='","'

但是，我的CSV文件只引用需要它的字段，所以我不相信建议会工作。

However, my CSV file only quotes fields that need it, so I do not believe that suggestion would work.

SQL Server的BULK IMPORT语句是否可以导入正确引用的CSV文件？如何？

Can SQL Server's BULK IMPORT statement import a correctly quoted CSV file? How?

推荐答案

不幸的是，SQL Server将引用的逗号解释为分隔符。这适用于BCP和批量插入。

Unfortunately SQL Server interprets the quoted comma as a delimiter. This applies to both BCP and bulk insert .

从 http://msdn.microsoft.com/en-us/library/ms191485%28v=sql.100%29.aspx

如果数据中出现终止符，它将被解释为
a终止符，而不是数据，并且该字符之后的数据是
，下一个字段或记录。因此，
仔细选择您的终止符，以确保它们不会在您的数据中出现
。

If a terminator character occurs within the data, it is interpreted as a terminator, not as data, and the data after that character is interpreted as belonging to the next field or record. Therefore, choose your terminators carefully to make sure that they never appear in your data.

这篇关于批量插入在SQL Server中正确引用CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！