I need to Bulk Insert a .csv file into an SQL table. The problem is within the .csv file some fields have quotes some have commas within the fields. Does anyone have a solution or a workaround of how I can handle this?
.csv file as follows:
"1 High Street","London","United Kingdom"
"1,High Street","London","United Kingdom"
"1,High Street",,United Kingdom
SQL Code as follows:
BULK INSERT test FROM 'c:\test\test.csv' WITH (FIELDTERMINATOR=',',ROWTERMINATOR = '')
select * from test
You should be able to see all the issues I am experiencing. I tried importing with the quotes specified but due to not all fields having these present it imported incorrectly again.