Tuesday, March 4, 2008

SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server

This is very common request recently - How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData GO CREATE TABLE CSVTest (ID INT, FirstName VARCHAR(40), LastName VARCHAR(40), BirthDate SMALLDATETIME) GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:csvtest.txt

1,James,Smith,19750101 2,Meggie,Smith,19790122 3,Robert,Smith,20071101 4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT CSVTest FROM 'c:csvtest.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = 'n' ) GO

Check the content of the table.

SELECT * FROM CSVTest GO

Drop the table to clean up database.

DROP TABLE CSVTest GO

Reference : Pinal Dave (http://www.SQLAuthority.com)

2 comments:

Anonymous said...

thanks you so much!!!!!!!!
its work!!!!!!!!!!

Ben said...

It's clear to me, after searching around a bit, that there's no way to specify a text delimiter for the CSV (or any delimited file format,) in BULK INSERT for those cases when the delimiter appears within the data field.

What happens when we include suffices in your example?

1,"James, Jr",Smith,19750101

This will result in 5 fields instead of 4 when processed by BULK INSERT. This is a short-sighted move on MS's part. Every other RDBMS can do this correctly.

Basically, it's broken and can't actually load a standard CSV file. MS not adhering to (in this case, very old) standards? No surprise here.

</bitter>

Post a Comment