I have a few fundamental questions and I need assistance importing a.csv file into SQL Server using BULK INSERT.
- How can I make the import process handle the data that may have a comma (,) in between it in the CSV file (for example, the description)?
- The data with commas are surrounded in "" (double quotes) [as in the example below] if the customer prepares the CSV from Excel. How can the import manage this, then?
- How can we determine which rows the import skipped because of incorrect data? When importing, rows that cannot be imported are skipped.
Here is the sample CSV with the header:
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
The SQL statement to import:
BULK INSERT SchoolsTemp
FIRSTROW = 2,
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
Can someone please help me with this?