Read CSV Files with double quotes and comma into SQL DB

2012-6-12 13:04| 发布者: Test| 查看: 3010| 评论: 0|原作者: Brad Schulz|来自: SELECT Blog FROM Brad.Schulz

摘要: In this post, I’d like to talk about Comma-Separated Values in Files (i.e. CSV Files).I had a client with a need to read in a CSV file and do some subsequent processing with it. This was a very small ...

Conclusion

Considering that the procedure is potentially doing a lot of string manipulation (with a VARCHAR(MAX) variable), the performance is certainly not stellar, since T-SQL is not known for its rapid string processing prowess. The larger the file, and (especially) the more double-quote delimiters you have in the file, the longer it will take. To process a CSV file of 50,000 rows and 4 columns (3MB in size) that contained no double-quote delimiters at all (and INSERTing the data into a temp table) takes a little over 20 seconds on my laptop. Again, that’s frightfully slow, but if you need to process that much data, you should probably be using SSIS instead. This procedure is primarily designed for relatively small quick files that BULK INSERT will not handle correctly.

I hope you find this utility to be useful. I certainly had fun writing it.

1 2 3 45 / 5 页