sql server Bulk insert csv with data having comma

Unfortunately , SQL Server Import methods( BCP && BULK INSERT) do not understand quoting " "

Source : http://msdn.microsoft.com/en-us/library/ms191485%28v=sql.100%29.aspx

The answer is: you can't do that. See http://technet.microsoft.com/en-us/library/ms188365.aspx.

"Importing Data from a CSV file

Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL Server. For information about the requirements for importing data from a CSV data file, see Prepare Data for Bulk Export or Import (SQL Server)."

The general solution is that you must convert your CSV file into one that can be be successfully imported. You can do that in many ways, such as by creating the file with a different delimiter (such as TAB) or by importing your table using a tool that understands CSV files (such as Excel or many scripting languages) and exporting it with a unique delimiter (such as TAB), from which you can then BULK INSERT.

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
    );