How to Load CSV File Into SQL Server Table/View

February 14, 2008

Hello ,

T-SQL ‘Bulk Insert’ statement lets you load CSV or any other user-specified file formats into table/view .  In a CSV file , each field is seperated by a Comma and each line represents a record. Now , open notepad and create a CSV file named ‘test.csv’ with the following content :

1,FirstName1,LastName1,20
2,FirstName2,LastName2,25
3,FirstName3,LastName3,30

 

The following procedure demonstrates loaing the CSV file into a temporary table :

Create Procedure dbo.LoadCSVFile
AS
Begin
Create Table #CSVTest( ID INT , FN Nvarchar(50) , LN Nvarchar(50) , Age INT )
BULK INSERT #CSVTest FROM 'c:\test.csv' WITH
(
FieldTerminator = ',' ,
RowTerminator = '\n'
)
SELECT ID,FN,LN,Age FROM #CSVTest
End

Executing the procedure results in :

result1.jpg

Advertisements