Import quoted CSV

Question

I have a set of quoted CSV files, which I need to import into SQL Server. Files look like this:

"ColA","ColB","ColC"
"12345","John Smith","-75.33"
"12356","Jane Doe","43.12"

BCP and BULK INSERT commands do not work because SQL Server cannot handle quoted fields.

How can I import files such as above?

Answer 1

OPENROWSET command and Microsoft Access Text Driver (*.txt, *.csv) driver come to the rescue:

SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={Microsoft Access Text Driver (*.txt, *.csv)};
DefaultDir=D:\CSV file directory;
Format=CSVDelimited;
CharacterSet=ANSI;
',
'SELECT *
FROM [filename.csv]')