Bulk insert into SQL Server a CSV with line breaks in fields

Question

I have a csv that looks like this:

"blah","blah, blah, blah
ect, ect","column 3"
"foo","foo, bar, baz
more stuff on another line", "another column 3"

Is it possible to import this directly into SQL server?

Answer 1

Every row in your file finishes with new line (\n) but the actual rows you want to get finishes with quotation mark and new line. Set ROWTERMINATOR in BULK INSERT command to:

ROWTERMINATOR = '"\n'

EDITED: I think the bigger problem will be with commas in the text. SQL Server does not use text enclosures. So the row will be divided on commas without checking if the comma is inside quotation marks or not.

You may do like this:

BULK INSERT newTable
FROM 'c:\file.txt'
WITH
(
    FIELDTERMINATOR ='",',
    ROWTERMINATOR = '"\n'
)

This will give you the following result:

col1  | col2                                        | col3
----------------------------------------------------------------
"blah | "blah, blah, blah  ect, ect                 | "column 3
"foo  | "foo, bar, baz  more stuff on another line  | "another column 3

All you have to do is to get rid of the quotation marks on the beginning of each cell.

For example:

UPDATE newTable 
SET col1 = RIGHT(col1,LEN(col1)-1), 
    col2 = RIGHT(col2,LEN(col2)-1), 
    col3 = RIGHT(col3,LEN(col3)-1)

I think you can also do this using bcp utility with format file