Search

Monday, July 4, 2011

Import CSV File Into SQL Server Using Bulk Insert

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.
Create a 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,2
0040202

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

Ref: http://www.SQLAuthority.com

No comments:

Post a Comment