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,
I’m new to SQL, I’m a VoIP engineer. I am trying to create a call billing database. I have a simple table like this
My VOIP server exports out call records as text (comma separated) files everday.
phone,name,callerid,dialednumber
3929,joel,3929,3454
3454,anita,3454,3929
I need to be able to upload data from text files onto the same table. Is there a way to do this and set it to automatically import the text files?
Thanks in advance,
joel
hi guys, (newbie / VS2008 pro)
I have a table with 5 columns and a csv with 5 columns which i want to import into table.
I have used your code as above to import CSV into table and I get ERROR message :
There was an error parsing the Query. [Token line number =1, token line offset =1, Token in error = BULK]
what does this mean ?
not sure if i am in the right area but am in the Server Explorer window, right clicked on database name then selected
‘New Query” ( is this right ?)
also, i can’t find DTS in the \bin folder ?\ of VS2008/ SSME
thanks in advance
viv (frustrated)
Hi,
Please look at this:
Let me know if it helps you.
Thanks,
Tejas
Hi Tejas,
I actually need help with the error below when ever i try to run the query ?
“There was an error parsing the Query. [Token line number =1, token line offset =1, Token in error = BULK] ”
please help
(VS2008 Pro)
thanks
viv
The code provided in this site will work only in SQL Server and not in Mysql
Hi,
Could you give me any sample data?
So I can try my own and let you know.
Thanks,
Tejas
Is there a way using DTS or SSIS to easily pick up and import in reoccurring delimited files.
They are very simple files with about 10 pipe delimited fields that very easily import in manually. They are placed in a directory by another business process and contain a unique DateTimeStamp filename. All of the current current BCP or Import tasks I see in either 2000 or 2005 force you to select a specific filename rather than a wild card. I understand that I will have to deal with moving the files also as they are processed which there appears to be a file operations task I could use. I thought for sure that this would be a commonly needed slam dunk task to perform in DTS or SSIS, but right now feel like just writing a small custom app. to do it. Any insight you have to offer would be greatly appreciated. Thanks!
Hi there,
sorry I’m new to SQL Server.
I was wandering to load the ASCII file into SQL Server table with this code:
BULK
INSERT CSVTest
FROM ‘c:\csvtest.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
c:\csvtest.txt’ refers to the file in the same filesystem with the SQL Server or they can be on different boxes?
Thanks
Dear Pinal,
I still have a problem I want to use the same bulk insert query by passing the filename as a parameter since I dont want to hard code it in the query is it possible??
declare @filename varchar(100)
set @filename = ”” + ‘C:\test.txt’ + ””;
bulk insert vishu_test
from @filename
with
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
Try
declare @sql varchar(1000), @filename varchar(100)
set @filemame='C:test.txt'
set @sq='BULK INSERT vishu_test
FROM '''+@FileName+'''
WITH ( FIELDTERMINATOR='';'', ROWTERMINATOR=''\n'')'
EXEC(@sql)
@ Vishwanath,
Yes, Of-course it is possible. What you need to do is store whole script into another variable and execute that variable. something like this,
declare @SQLCMD varchar(1000)
declare @filename varchar(100)
set @filename =‘C:\test.txt’
set @SQLCMD = ‘
bulk insert vishu_test
from ‘+@filename+’
with
(
FIELDTERMINATOR = ‘‘,’’,
ROWTERMINATOR = ‘‘\n’’
)’
Print @SQLCMD
Exec (@SQLCMD)
~
declare @SQLCMD nvarchar(1000)
declare @filename nvarchar(100)
set @filename =‘C:test.txt’
set @SQLCMD = ‘
bulk insert vishu_test
from ‘+@filename+’
with
(
FIELDTERMINATOR = ‘‘,’’,
ROWTERMINATOR = ‘‘n’’
)’
Print @SQLCMD
exec sp_executesql @sql(@SQLCMD)
This method is recommended as it prevents and SQLInjection…
Hi,
I wonder if anyone can help. I’m trying to get a bulk data import to mssql but I want to have the file name also included in one of the colums. Also i’m trying to get the importer to import any file name .txt file int he import folder. Is this possible? here is my script:
BULK
INSERT orders
FROM ‘c:\imp\test3.txt’
WITH
(
firstrow=2,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
is there a wildcard for the filename? something like FROM ‘c:\imp\*.txt’
No. That is not supported
hello……..
using this i can insert the contents of csv file to sql.but the last row was not added into the table……
BULK INSERT portf FROM ‘E:\\portfolio\\WebSite2\\grouped\\2007\1\\EQ020107.CSV’
WITH (FORMATFILE=’C:\\Documents and Settings\\user\\portfol.fmt’,FIRSTROW=2)
Have any solution for this problem?
This is really a very simple a good article. Informative indeed.
Thanks for the help and keep up the good work!
Good Luck!
ComputerVideos.110mb.com/
Pinal Hi,
Based on your example how do you insert multiple txt files
Like
C:\csvtest1.txt
C:\csvtest2.txt
C:\csvtest3.txt
to the csvtest table in sql
Thank you very much.
Oded Dror
Thanks! This worked splendidly.
This is a great example to start. I am facing only one problem
How can i Skip Header while inserting CSV or Tab Seperated Values. B’Cause my TXT files consisting Header informations as well.
That would be great help..Thanks
You can skip the column names by using FIRST_ROW option
BULK INSERT …. FROM …
WITH
(
FIRST_ROW=2,
FIELDTERMINATOR =’ |’,
ROWTERMINATOR =’ |n’
)
Hi Pinal
I followed your bulk insert and it worked perfectly. But I was trying a couple of other things which did not work for me. Basically I want to auto increment the primary key by 1, instead of storing 1,2,3…… in csv file
Eg: my csv file looks like this
James,Smith
Meggie,Smith
Robert,Smith
Alex,Smith
and my table looks like this
csvinsert(id int identity(1,1), fname varchar(20), lname varchar(20), primary key(id))
Now when I follow your commands it gives me dataconversion error as it is trying to insert a string in id column. What can I do to make this work?
You need to skip the identity column from being updated
Refer this to skip the column
If I have a file:
“1”,”James”,”Smith”,”19750101″,,
“2”,”Meggie,Smith”,”19790122″,”A”,
“3”,”Robert,Smith”,”20071101″,”B”
“4”,”Alex”,”Smith”,”20040202″,,
How can I do it. (import to SQL tables)
Thanks,
Thank you very much, pinaldave your site is very appricated the fresh candidates also