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,
Try like this
BULK INSERT TmpStList FROM ‘c:TxtFile1.txt’ WITH (FIELDTERMINATOR = ‘”,”‘)
Ref :
Thanks,
24×7
You need to use Format File to import CSV file
Awesome article , exactly what I wanted.
hi pinal,
please help me to insert data/multiple data in a csv using VBA.
thanks in advance
Plz help me yaar
Hi
You do not need to do anything . Just design a table right click and click import data select csv file and thats it. No need to do programming and stuff if its one time only.
hi there,
can you tell me how to make update to the database from the text file.the text file i have is a buffer(log file) from finger print machine.i don’t want to save a text file every time i collect the data from the device.should i make a trigger or what.is there any other way??.
other thing:how to insert the data into db from the device?
Thank u a lot, my friend……..
Explained in a simple manner. Good one
Please help urgent,
While runnning BULK INSERT statement, i am getting error
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
why we cannot do bulk insert to temporary or variable tables?
You can bulk insert to temporary table however bulk insert to table variable is not allowed
Hi,
I ve to load a table with 600000 records to excel. How do i do it.
Can anyone help me load a file through bulk insert that has a date and time appended in its name with first part of the name remaining constant and the date time part being variable everyday.
something like this:
bulk insert dbo.tablename
from ‘filename*.txt’
etc.
here the filename is the first part
and
datetime part denoted by * is the variable part
@DNJ
Definitely I would go for DTS / SSIS. That is way faster than any other tool, faster than Database Engine.
~ IM.
@Hasan
You need to write Dynamic SQL.
By Using Dynamic SQL, first prepare bulk insert script with proper file name.
Consider below script as a sample for your requirement.
Declare @sqlcmd1 varchar(1000)
set @sqlcmd1 = ‘bulk insert dbo.tablename
from filename’+convert(varchar, datename(yyyy, getdate())) + convert(varchar, datepart(mm, getdate()))+convert(varchar, datename(d, getdate()))
Execute Sp_ExecuteSql @sqlcmd1
~ IM.
hai thanks.
your code helps realy good
Thanks
Hi,
Thanks for the post.It was very helpful.
I tried it.It is working.But i need to do it for only desired columns(not for all the column).
Can anybody suggest me.
Hi Prashanti,
you have to explore the area of using the FORMATFILE = ‘format_file_path’ for bulk insert to do it for desired columns.
Some text fr
The format file should be used if:
1.The data file contains greater or fewer columns than the table or view.
2.The columns are in a different order.
3.The column delimiters vary.
There are other changes in the data format. Format files are typically created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.
Regards,
Saswata
Can someone plz help me.Its keep telling me that incorrect syntax whereas im using the exact command.
bulk insert dbo.Orders
from ‘C:\Data\orders.txt’
with
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
Incorrect syntax near ‘‘’.
Dont know whats wrong.
Can someone please help me with the following. I have a csv file that I’m trying to load into sql.
The 1st line in the file contains IDs, 2nd line – user account and remaining lines contains the change info.
Example:
1234,2586
dom\hope,dom\newberry,dom\ksayr,dom\farley
11111,1,23
11111,2,187
11111,3,9687
I broke it down to three separate bulk inserts. However, I’m having problems with the user accounts insert.
CREATE TABLE #USER
(
#1 varchar (100)
)
BULK INSERT #USER
FROM ‘C:\Documents and Settings\Nakisha\FIRST\1064PBF01.csv’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRSTROW = 2,
LASTROW = 2
)
With the script above “dom\hope,dom\newberry,dom\ksayr,dom\farley” gets display in the table.
Is there a way to have each user account display in a different row in the table?
Thanks for your help!