SQL Server - Bulk Insert - FIELDQUOTE does not recognize double quote

Question

I'm trying to BULK INSERT a CSV table. Some of the fields have values like this: "Smith, John" Because the comma is in the 'field' - it's 'delimiting' and placing 'John' in the next column.

Here's a sample table

CREATE TABLE [dbo].[Test_CSV](
    [a] [int] NULL,
    [b] [varchar](50) NULL,
    [c] [varchar](50) NULL,
    [d] [varchar](50) NULL
) ON [PRIMARY]
GO

Here's a sample CSV file:

2,"2","Mary Smith ","Ms."
1,"1","Smith, John","Mr."

Here's the command I'm using

BULK INSERT Test_CSV FROM  'C:\MyCSV.csv' with (  FIELDQUOTE='"', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

Here's the data, which is WRONG, I get '

Notice column d containing ' John","Mr."

What is the correct command? thx in advance

Answer 1

Try

BULK INSERT Test_CSV
FROM  'C:\MyCSV.csv' 
WITH ( FORMAT='CSV');

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15