批量插入/OPENROWSET FormatFile终结器，用于CSV文件，数据中带有(，逗号) [英] BULK INSERT / OPENROWSET FormatFile Terminator for CSV file with , (comma) in the data

问题描述

我已经为我的百万行CSV编写了一个很好的导入文件，该导入文件工作得很好(使用OPENROWSET BULK(我没有使用BULK INSERT，因为我需要与其他一些列进行交叉联接).formatfile使用逗号作为终结者.

I've written a nice import for my million row CSV that works quite nicely (using OPENROWSET BULK (I didn't use BULK INSERT because I need to cross join with some other columns). The formatfile uses a comma as the Terminator.

以下是我用来开发CSV的示例:

Here is an example of the CSV I was using to develop:

Reference, Name, Street
1,Dave Smith, 1 Test Street
2,Sally SMith,1 Test Street

一旦我开始使用它，有人会提醒我数据本身可能带有逗号，哎呀！！！

Once I'd got it working, someone reminded me that the data itself could have a comma in it, whoops!!!!

Reference, Name, Street
"1","Dave Smith", "1 Test Street"
"2","Sally Smith","1,Test Street" <-comma in street

人们如何使用FormatFiles处理数据中带有逗号的CSV? (或者我只是说文件必须用TAB分隔)?

How do people deal with a CSV with a comma in the data using FormatFiles? (Or do I just say that the file must be TAB delimited)?

推荐答案

如果您的字段终止符可能出现在数据中，那么最好使用TAB或PIPE分隔符(或任何适用于您数据的分隔符).

If your field terminator can occur in the data, then it may be best to use TAB or PIPE delimiter (or whatever works for your data).

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

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

http://msdn.microsoft.com/en-us/library/ms191485.aspx

这篇关于批量插入/OPENROWSET FormatFile终结器，用于CSV文件，数据中带有(，逗号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！