Import data from comma-sepertated values file.

Sometime we need to insert data in SQL Table from some Text/CSV File. Here, we have CSV file in drive C: with name import.csv with following content. The location of the file is C:\import.csv.

ID,UserName,FName,LName

1,user1,Akhil,Shah

2,user2,Nikhil,Raj

3,user3,Samar,Das 

4,user4,Arun,Ladha

Now, to import the above CSV file in SQL, We have to create a table with 4 Columns:

CREATE TABLE ImportFromCSV (  

  ID INT,  

  UserName VARCHAR(25),  

  FName VARCHAR(50),  

  LName VARCHAR(50)

); 

Now we can import the above CSV file into ImportFromCSV Table. We will use BULK INSERT to insert data into table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT ImportFromCSV   

FROM 'c:\import.csv'  WITH  

(FIRSTROW = 2,  

 FIELDTERMINATOR = ',',  

 ROWTERMINATOR = '\n')  

GO  

In above script, parameter FIRSTROW will tell BULK LOAD that we don’t want to import first line (= 1) because it contains headers.

If you will have table what have less columns than the csv file these columns will be added after text in the last column. If this column will reach maximum length, this line will not be inserted. With MAXERRORS you can specify the maximum number of error lines what are allowed before whole INSERT will be considered as failed.

You can try this also:

SELECT * FROM OPENROWSET(’MSDASQL’,’Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\import.csv; Extensions=CSV; HDR=No;’,’SELECT * FROM import.csv’)

In all cases you can write a script which will import the data manually. Parsing the csv file and running the insert is easy to write. 

But IN this CASE you have to ENABLE the Ad Hoc Distributed Queries.