Bulk Insert issues with CSV file

Question

I am trying to load an SQL Server table from a CSV file.

My Bulk Insert statement:

bulk insert ClassList from 'ClassList.csv'
with (
     firstrow=2,
     fieldterminator=',',
     maxerrors=100,
     keepnulls
     );

Most of the data loads but not the few rows where one of the fields contains a comma so they have double quote delimiters - here rows 21 & 23 load but not 22:

21,Blue or Lilac Smoke Adult,22,PER,
22,"Red, Tortie, Cream, Blue or Lilac Cream or Choc. Tortie Smoke Adult",23,PER,
23,AC Silver Tabby Adult,24,PER,

My table:

Class_Number varchar(10) not null
Class_Description varchar(200) not null
Class_Order int not null
Section_ID varchar(10) not null
Judge_Initials varchar(10) null

Using SSMS v17.2.

SQL Server details:

Product Version:          13.0.4206.0
Product Name:             SQL Server 2016
Product Level:            SP1 
Product Edition:          Express Edition (64-bit)

Answer 1

OK the answer as in the link posted above is that the BULK LOAD in my versions of the software can't cope with a file having quotes around some of the fields. I've not tried to see if quoting all the character fields would work. I resaved as a tab delimited field (.txt) and it has loaded just fine.

From the MS documentation:

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

**Input file format options**
FORMAT = 'CSV'
Applies to: SQL Server 2017 CTP 1.1.
Specifies a comma separated values file compliant to the RFC 4180 standard.

FIELDQUOTE = 'field_quote'
Applies to: SQL Server 2017 CTP 1.1.
Specifies a character that will be used as the quote character in the CSV
file. If not specified, the quote character (") will be used as the quote
character as defined in the RFC 4180 standard.

The FORMAT option wasn't acceptable which is probably the source of my problems.

Answer 2

It seems you have ',' as data into your source csv file. Similar question is answered at below link -

sql server Bulk insert csv with data having comma