sql server Bulk insert csv with data having comma

sumit picture sumit · Jan 20, 2014 · Viewed 39.6k times · Source

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

david.pfx picture david.pfx · Jan 20, 2014

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.