CSV FormatFile for OPENROWSET BULK catering for both plain COMMA and "COMMA"

Question

Possible Duplicate:
SQL Server Bulk insert of CSV file with inconsistent quotes

I have been given a million row CSV to import into SQL Server that is basically like this:

"A",B,C,D,"E","F"

I have done it ok in the past using OPENROWSET BULK and a format file catering for CSV file with no quotes around any data or all columns with quotes.

What do people normally do to handle a CSV like this and a FormatFile where some have quotes round and some fields dont?

Note: i will be cross joining with another table and not importing all coumns from the CSV, which is why I used OpenRowset Bulk (and I dont have access to bcp on the server either).

Yes, good spot (it wasnt flagged up when searching/posting the question) — DomBat, Jul 19 '12 at 11:38

score 2 · Answer 1 · edited May 23 '17 at 12:20

2

The solution offered in SQL Server Bulk insert of CSV file with inconsistent quotes suggests preprocessing the file. Given your example, you could use the following update statement to correct the data, however this will only work if the quotes are not surrounding data that contains commas.

UPDATE  @table
SET     value = CASE WHEN LEFT(value, 1) = '"'
                          AND RIGHT(value, 1) = '"'
                     THEN SUBSTRING(value, 2, LEN(value) - 2)
                     ELSE value
                END

edited May 23 '17 at 12:20

Community

1
1

answered Jul 19 '12 at 03:10

Carl

324
1
15

If double-quotes are in the text file,how does this update help import data from textfile? – AnandPhadke Jul 19 '12 at 04:52

score 0 · Answer 2 · answered Jul 19 '12 at 04:51

0

You can first find and replace all double-quotes in the text file with nothing.Then you can do the normal bulk insert with comma(,) as delimiter.

answered Jul 19 '12 at 04:51

AnandPhadke

13,160
5
26
33

CSV FormatFile for OPENROWSET BULK catering for both plain COMMA and "COMMA"

2 Answers2