This is a 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 sweetest. text with the 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 (https://blog.sqlauthority.com)
839 Comments. Leave new
Thanks for your it’s a very usefull
Hy all!
nice artical on bulk insert.
I have a existing table in database, on which i want to apply bulk insert. but the thing is can filter apply on bulk insert? i want to avoid the duplication, if record already exist then i want to overright. is this possible with bulk insert? I have datafile with millions record.
pls. Answer …
thanks in Advance.
Hy pinal!
In sql, we often notice that if any error occred, it will print the message “Msg xxxx, Level 16( or 15), State 1, Line xx.”
What is the meaning of level16 (or 15), how many levels in Sql? and what the state indicate?
Pls. Answer
Chirag
Thanks a lot..
Kevin HARMON
Hi Mr. Pinal,
I have a flat ascii file with tabs to delimit all the columns, within each column is text or dates, text is delimited by the quotes as seen in sample A below. I can import into ms sql with bulk insert just fine, except I want to strip the quotes off so that the final row in the sql database looks like example B. Here is my code;
BULK INSERT dbo.NC_Voter_History
FROM “C:\Users\Kevin\Documents\NC Folder\NC_His\his1-50.txt”
WITH
(
FIELDTERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n’
)
GO
What can I add to strip off the “quotes” at bulk insert?
Thanks, Kevin
Sample A:
89 “TYRRELL” “000000000002″ 23 2002-11-05 00:00:00 “11/05/2002″ “11/05/2002 GENERAL ” “IN-PERSON ” “DEM” “DEMOCRATIC ” “14″ “KILKENNY” “EE2035″ 89 “TYRRELL”——- “14″ “14″
Sample B:
89 TYRRELL 000000000002 23 2002-11-05 00:00:00 11/05/2002 11/05/2002 GENERAL IN-PERSON DEM DEMOCRATIC 14 KILKENNY EE2035 89 TYRRELL 14 14
Hello Kchusa,
BCP does not have any predefined option to remove double quote from column data. For this purpose you can use format file. In format file specify the double quote in field terminator.
Another option is to use SSIS or Import/Export wizard. In wizard we have option to specify Text Indentifier.
Regards,
Pinal Dave
Hi,
Thanks your document.
This is very useful.
Regards
Rameshkumar.T
Hello,
I would like to insert records from CSV file into SQL table in SQL Server 2005. But I do not have permission for BULK INSERT command. Is there any other way I can insert records from CSV file into SQL table?
Your help is very well appreciated.
Thanks
To create a job (balance sheet) where the total >7000 sheet must be updated to NUll and this process must be done for every 30 min
Plz help me out
Kapil
I think that this query is only work when the database and the text file on the same system.
If yes then pls tell me how can we achieve this when the database and the text file both are on different system.
Hi Pinal,
I is there are a way to run an Update Statement from an excel or csv file into the database. I am using SQL 2000 and i need to update tables in from a csv file to save time i been lookin some examples but they are mainly insert statements can you give a some help/. I am using Coldfusion 8 front end if that helps any..
thanks in advance.
Hi Alex,
We can handle data from csv file as a table using OPENDATASOURCE function. So using this function join the csv file with database table and update the table.
Regards,
Pinal Dave
Hi Pinal,
I am not sure; May be this query is only work when the database server and the text file on the same system.
If yes, Please tell me how can we achieve this when both the database and the text file are on different system.
thanks in Advance.
Hello Kapil,
The OPENDATASOURCE function can access file or database tables from remote server. But what there are many possible reason that you may not access a remote file like access permission, version of your SQL server instance, version of your source file, etc.
What error are your getting while accessing from remote computer?
Regards,
Pinal Dave
Hello Pinal,
Its nice to see your reply; when I am executing the bulk insert query then its gives this error
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “d:\\csvtest.txt” does not exist.
—————————————————————-
I am running the below query
BULK INSERT Tmp_GpMember
FROM ‘d:\test.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
—————————————————————-
Table definition
create table Tmp_GpMember ( int Number not null)
—————————————————————-
File Contains this data with file name test.txt
13
23
23
45
—————————————————————-
The text File is on my computer with Sql Server 2008 Client version installation which connect to the database server which is on another computer.
Hi Kapil,
File path is searched on computer where SQL Server instance is running. Give the file path relative to server system.
Regards,
Pinal Dave
Hi Pinal,
You Means I have to give the path like Ipaddress//d:/test.txt.
BULK INSERT Tmp_GpMember
FROM ‘192.168.1.182\\d:\test.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
I ran the above query but also it gives the error.
Try this code
BULK INSERT Tmp_GpMember
FROM &#\39;192.168.1.182dtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)
GO
Hello Pinal,
Can we load the data from one csv to multiple tables ?
If the CSV contains a heder and trailer and i want to store trailer in some table is that possible with BCP..
e.x.
Fname,Lname,Empid,Location —–>Header
Mayur,Mittal,1011739,SRE
John,Mathews,103333,PUNE
Sherlok,Holmes,100007,USA
000003 ——->Trailer (Containg the nu of records)
Can i store the trailer in some table ?
Use OPENROWSET
insert into target_table(column_list)
select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=no;FMT=FixedLength;Database=c:', text_file#txt)
where f1 like '[a-zA-Z]%'
insert into count_table(col)
select f1 from OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=no;FMT=FixedLength;Database=c:', text_file#txt)
where f1 like '[0-9]%'
Hi Pinal ,
I wanna Import CSV file to SQL 2005 (VB.NET)
if the record exist ,,over write it otherwise insert the record
so how can I read data from CSV, fill it with data set
then process each row individually
please help me as soon as possible ,,,
Greate job….
Thanks for the document…
With regards,
Ashwini.
hi pinal,
when i am executing openrowset query like this
select * from
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=d:\MCX11012010.xls’,’SELECT * FROM [Sheet1$]’)
for import excel file into sql server table then its gives this error
Cannot process the object “SELECT * FROM [Sheet1$]”. The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.
Make sure the file is located at the Server’s location
Also the file should be closed at the time of executing the query. For more informations and troubleshooting refer this post
this code is realy very help ful to me.
tahnk u very very much
I am very new to this and notice that you said “Load Comma Delimited File Into SQL Server”. Our hosted web site has 2 servers a Web server and SQL server. I am working on importing into the SQL server from a csv file on the WEB server. Is this possible or do I have to move the csv file over to the SQL server? I am told that the bulk import will only run from the SQL server. Both servers are behind the same firewall. I don’t think it is good practice to SFTP into the SQL server from outside the firewall.
It is really a simple but great share !
Thank You,
Jerome