sql server Bulk insert csv with data having comma

Question

below is the sample line of csv

012,12/11/2013,"<555523051548>KRISHNA  KUMAR  ASHOKU,AR",<10-12-2013>,555523051548,12/11/2013,"13,012.55",

you can see KRISHNA KUMAR ASHOKU,AR as single field but it is treating KRISHNA KUMAR ASHOKU and AR as two different fields because of comma, though they are enclosed with " but still no luck

I tried

BULK
INSERT tbl
FROM 'd:\1.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW=2
)
GO

is there any solution for it?

Answer 1

They added support for this SQL Server 2017 (14.x) CTP 1.1. You need to use the FORMAT = 'CSV' Input File Option for the BULK INSERT command.

To be clear, here is what the csv looks like that was giving me problems, the first line is easy to parse, the second line contains the curve ball since there is a comma inside the quoted field:

jenkins-2019-09-25_cve-2019-10401,CVE-2019-10401,4,Jenkins Advisory 2019-09-25: CVE-2019-10401: 
jenkins-2019-09-25_cve-2019-10403_cve-2019-10404,"CVE-2019-10404,CVE-2019-10403",4,Jenkins Advisory 2019-09-25: CVE-2019-10403: CVE-2019-10404: 

Broken Code

BULK INSERT temp
    FROM 'c:\test.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW= 2
    );

Working Code

BULK INSERT temp
    FROM 'c:\test.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a',
        FORMAT = 'CSV',
        FIRSTROW= 2
    );