批量导入CSV文件到SQL Server -删除双引号

I am running SQL 2008, bulk insert command, while inserting the data, I am trying to remove (") double quotes from the CSV file, which works partially, but doesnt work for all the records, please check my code and the screenshot of the result.

我正在运行SQL 2008，批量插入命令，在插入数据时，我正在尝试删除(“)”CSV文件中的双引号，该文件部分工作，但并没有为所有记录工作，请检查我的代码和结果的屏幕截图。

Bulk Insert tblUsersXTemp 
 from 'C:\FR0250Members161212_030818.csv'
 WITH (FIELDTERMINATOR = '","', 
 ROWTERMINATOR = '"\n"',
 --FormatFile =''
 ERRORFILE = 'C:\bulk_insert_BadData.txt')

2 个解决方案

#1

After you do the bulk insert, you could replace the double quotes.

完成批量插入之后，可以替换双引号。

UPDATE tblUsersXTemp
SET usxMembershipID = REPLACE(usxMembershipID, CHAR(34), '')

#2

You need a format file I believe, that's what I think is going on.

我认为你需要一个格式文件，这就是我所认为的。

If you use the following Bulk Insert command to import the data without using a format file, then you will land up with a quotation mark prefix to the first column value and a quotation mark suffix for the last column values and a quotation mark prefix for the first column values.

如果你使用下面的批量插入命令来导入数据没有使用格式文件,那么您将土地与引号前缀第一列值和一个引号后缀最后一列值和一个引号前缀第一列值。

Reference

参考

Example from reference:

从参考示例:

BULK INSERT tblPeople
   FROM ‘bcp.txt’
   WITH (
      DATAFILETYPE=‘char’,
      FIELDTERMINATOR=‘","’,
      ROWTERMINATOR = ‘\n’,
      FORMATFILE = ‘bcp.fmt’);

You could also potentially have dirty data that uses quotes for more than just delimiters.

您还可能有脏数据，这些数据使用的引号不仅仅是分隔符。

#1