Import CSV File Into SQL Server Using Bulk Insert

Hi,

I would like to share my knowledge regarding bulk import. I came across situation where multiple data from excel sheet need to export to SQL database. To do it using Bulk Import you can use following snippet.

First create table in SQL server database with same no of columns as defined in CSV file. Once you create the SQL table, convert you excel sheet to CSV file.

bulk INSERT [TableName]
FROM 'C:\' --full path including CSV file.
WITH 
(
ROWS_PER_BATCH = 2,--Max no of rows inserted per batch is 2.
FIELDTERMINATOR = '$' --Used to separate field values
,ROWTERMINATOR = '\n'--Determine end of row
,ERRORFILE = 'b',--Error file generate @ "C:\WINDOWS\system32"
,MAXERRORS = 2 --Max no of error that is allowed at the time of import is 2. More than 2 error will rollback whole transaction.
)
GO

I found few issues while having ',' inside our fields like Mike,”456 2nd St, Apt 5".

My approach towards this kind of situation was to change List Separator for a while and create CSV file.

Hopefully it will be helpfull to others.

Cheers,
Ashish Chotalia

P.S. - Replace "[TableName]" with your database table name.