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 wants to use bulk insert for a text file with fieldterminator as a space. How do I use It.
Regards,
Vaibhav
Example
BULK
INSERT CSVTest
FROM 'c:csvtest.txt'
WITH
(
FIELDTERMINATOR = ' ',
ROWTERMINATOR = 'n'
)
Sir,
that is already done. can you suggest me a way to defin th table by its own using the CSV file. i.e. my first row is the column name. Now how do i create a table using the variable column names??
tank in advance.
Mukul.
Hi Pinal,
I have used Bulk command, but want to get the file path of CSV file. I want file path using T-SQL.
Please tell me how to get the file path only by providing file name.
Thanks in advance….
Try this code
declare @sql varchar(1000)
set @sq=’BULK INSERT dbo.TableName
FROM ”’+@FileName+”’
WITH ( FIELDTERMINATOR=’’;”, ROWTERMINATOR=’’\n’’)’
EXEC(@sql)
Hi Pinal,
Yours query is marvellous but i want the same query to access a text file to online server with the text placed in client machine
Can any one suggest me
Thanks in advance
Hi!!
i am facing a problem…..
i am trying to bulk insert data into a table.
the stored procedure goes like this…
====================================
CREATE PROCEDURE [dbo].[insbulk]
@circle varchar(20),@path varchar(100)
AS
bulk insert @circle from @path with (fieldterminator=’,’)
GO
======================================
The thing is that this is a wronh syntax…..
My frontend application goes like this…
which takes input the filename ie the path,the tablename
===================================
SqlCommand cmd=new SqlCommand(“insbulk”,conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(“@circle”,this.cmbcircle.SelectedItem.ToString());
cmd.Parameters.Add(“@path”,this.fldg.FileName);
cmd.Parameters.Add(“@insertiondate”,insertiondate);
cmd.ExecuteScalar();
}
catch
{
MessageBox.Show(“Error!!!Connection terminating with database”);
conn.Close();
}
@samrat
First try executing that stored procedure in SQL Server Client tools.
Check if it is working fine ?
Regards
IM
Thanks
Hi
how to transfer data into multitables from csv file and is it possible to tranfer the data into constraint tables.
I am trying to import data from multiple text files into sql server 2005. After the data has been imported in the sq server, i want to move the files to another folder. Does anyone have a solution to this?
Thanks in advance.
Hi this article is very helpfull
I was trying to run same query with SQL Server Compact Edition Database.
But faild to do same can u please help me in this
Hi Abhisek,
To move file after processing, you need to use SSIS for that.
In that you can process file and later you can move/Delete that file too.
Tejas
I had the same problem. Copying the file to the DBserver and calling the file with full name worked.
…..
bulk insert dict from “\dbservermydirectorymyfile.txt”
……
Hi Pinal
Hi Pinal & all-
Help please
I have a csv file that contains data like this
“1”,”james”,”smith”,”2323″
how do I import this to a table without the double quotes.I want to avoid a intermideate convirsion into an excell file since I like to schaduele this as a job.
hey pinal
i am trying 2 import data from multiple text files into sql server
giving error
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/forum/post_message.asp, line 211
on this particular stmt
objRSInsert.Open “Select * from messages where message_id=IDENT_CURRENT(‘messages’)”, cnnForumDC, adOpenDynamic, adLockPessimistic
iNewMessageId = objRSInsert.Fields(“message_id”)
If thread_id = 0 Then
objRSInsert.Fields(“thread_id”) = iNewMessageId
objRSInsert.Update
End If
hey pinal
i am trying 2 import data from multiple text files into sql server
giving error
ADODB.Field (0×800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/forum/post_message.asp, line 211
on this particular stmt
objRSInsert.Open “Select * from messages where message_id=IDENT_CURRENT(’messages’)”, cnnForumDC, adOpenDynamic, adLockPessimistic
iNewMessageId = objRSInsert.Fields(”message_id”)
If thread_id = 0 Then
objRSInsert.Fields(”thread_id”) = iNewMessageId
objRSInsert.Update
End If
As would be script if the columns have quotation marks double? For example:
“1”,”James”,”Smith”,”19750101″
“2”,”Meggie”,”Smith”,”19790122″
“3”,”Robert”,”Smith”,”20071101″
“4”,”Alex”,”Smith”,”20040202″
I have following script as she would modify?
BULK
INSERT CSVTest
FROM ‘c:\csvtest.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
Hi,
I regularly looking at this knowledge sharing site.
Nice and very informative
* How to import the Excel file to the remote SQL Server without using OBDC, ie only with Stored Procedure?
Hi,
How to import data like this:
“qwe,asd,zxc”,123,sometext
The result i whant to have:
qwe,asd,zxc 123 sometext
but what i have is:
“qwe asd zxc”,123,sometext
Any thoughts?
Thanks
Sorry result i whant to have:
“qwe,asd,zxc” 123 sometext
:)