在 SQL Server 中批量插入部分引用的 CSV 文件 [英] Bulk Insert Partially 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

我观察到第一行正确导入，但第二行错误的方式表明引用的逗号被视为字段分隔符.

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.

我见过这样的建议

从 CSV 批量导入 SQL

更改字段终止符

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

如果数据中出现终止符，则解释为一个终止符，而不是作为数据，并且该字符之后的数据是解释为属于下一个字段或记录.所以，仔细选择你的终结者，以确保它们永远不会出现在您的数据中.

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屋！