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
Hi,
Excellent Code to import data from text file into Database
Thanks.
HI dave,
i need to store a file content in database, it could be a text file or image file how can i??????
HI,
The above code works great for me, But I have several problem
1] I have the CSV file with 1155585 records the format of data is
“Bhavin”,”Mumbai”,”10/2/2004″,1,2,3,4,5
“Bhavin12″,”Mumbai12″,”10/2/2005”,1,2,3,4,5
so what i did i replace all string which contains Quotes to null i.e nothing
now the data looks like
Bhavin,Mumbai,10/2/2004,1,2,3,4,5
Bhavin12,Mumbai12,10/2/2005,1,2,3,4,5
Now I tried using the above code with comma as a deliminator, but I can only see 1155195 records
plz help me how to proceed…..
Fisrt of all, thank you for your example with bulk insert it solved my problem, but I styill have a little problem: I have in my .csv file special characters like (şţăîâ) and I’d like some help on this matter, please!
Thank you!
Excellent article!
I am importing a csv file and several fields have a very long field length (200 characters). I want to truncate it (25 characters) and add a special character (for example a tilde to advise me the data is truncated) after importing. Any suggestion would be appreciated.
Thank you.
I get this error msg on simple 6 row table(same format and file location as described above):
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
any help for this?
Hi Rose,
I’m also getting same kind of problem in bulk insert.
Could you get any solution to this problem.
Thanks
If the last line is empty in the file, remove it and try the code again
hello,
Looking to import a txt file that at present errors when dts package enncounters nagative vlaues. Any suggestions?
Negative values are assoicated with Money
0000003.34 will pass
00000-3.34 will fail
Thanks,
Cheif
I used the script to import a CSV into a new table in Database Explorer in Visual Web Developer. It works! Thanks for your help.
I am using your query to import CSV file to Sql Server.
But I am getting the following error”Cannot bulk load. The file “C:\CSVTest.txt” does not exist.”
What should I do now?
Any suggestions are welcome.
While runnning BULK INSERT statement, i am getting error
BULK INSERT #temptb FROM ‘/home/msrivast/temptable.txt’
go
sg 102, Level 15, State 0
ASA Error -131: Syntax error near ‘BULK’ on line 1
Please help
Hi Pinal,
How do we load CSV file has embedded newlines (in varchar colomuns) into MS SQL server?
For instance, how do we load the CSV file below. Note that the second record has a “newline”/”line break” in Column 2
id, reason, season
==, =====, =====
10,’cold’, ‘winter’
12,’cold
flu’,’autumn’
i couldn’t find anyone with an answer for dealing with CSV’s that have quotes in the text with commas within them.
The key issue being importing this:
Col1, Col2, Col3
“Joe”,”Smith”,”Sr Architect”
“Nicole”,”Dawson”,”Manager”
“Jon”,”Stephens, PHD”,”PM”
I was able to work out this solution. Basically describe the delimiter (field terminator) as “,” like this using XML Format Files for SQL Server:
And notice i had to describe the ” as " format for each field (i only listed 1 as the example).
Thanks
-B
I am loading lattitude longitude information into database using load file command.I loaded them into database successfully .But after checking using “select * from data base name” it’s showing all zeros.Any help would be helpful.Thanx in advance.
What if I want to load hundred of files with one script saved into one folder? Above script is just a fun. Do something actual through which many people can find the solution to their problems.
Please help.
i get large amount of information in an excel file which i need to store in MSSql database. i used to import but i need to make an ASP interface that picks the information from excel or csv and inserts into the database.
is it possible?
please help me. I got following error when i run insertion script below-
USE WATCHDOIT
BULK
INSERT CSVTest1
FROM ‘C:\Inetpub\wwwroot\WatchDoit\BusinessList1.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
Really Superb Pinal Sir..
I m njoying your SQL Tips daily…
Continue with your Favours…
Bye…
Hi,
insert Only one row
Plz help me
as early as possible
Try
BULK
INSERT CSVTest
FROM 'c:csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
FIRST_ROW=1;
LAST_ROW=2;
)
GO
Hi,
I’m using bulk insert and everything works fine but I cannot get ‘£’ sign characters to import properly. It always change into ‘?’ sign. I tried using nvarchar datatype but it did not help.
Please Help me.
Thanks