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 Pinal,
Thank you for the excellent article here, I was very useful.
I have query regarding the same, I am importing the csv file data and it’s working fine, but need to format data while BULK Insert.
My Data format is
ID,Name,Age,Location
“1”,”James”,”25″,”London”
“2”,”Smith”,”30″,”New York”
“3”,”Tom”,”28″,”Munich”
While Bulk insert the data is inserted alogn with the quotes “” , becoz my filedSeperator = “,”.
Here I need to remove the quotes, is there any way we can do that.
Thanks
Vijay
Vijay,
Did anyone respond or did you find a solution? I have the exact same scenario and have not been able to find a work around for the bulk load.
thanks for your knowledge sharing if i want ms certificate in asp.net what i do now
Hi
Can anybody tell me how can i import data in excel file through bulk insert query
bulk insert tbllaningpages
from ‘E:\landing-page.xls’
WITH (FIELDTERMINATOR = ‘ ‘,ROWTERMINATOR = ‘\n’)
GO
above is my query but its raise an error
table have eight column and 1 identity column
Ajay
Use Openrowset function. Refer this for more informations
hi friend,
I need a urgent help,
i am installed ssis 2005 , now i want to integreate excel bulk data into my data base in ssis 2005.
i am new to this, pls explain with screen shots,
because in ssis 2008 they give a option for import
in 2005 how can i do
pls help me urgent help
use linked server to link the excel file and then insert those record into your table or temp table
Hi,
I want to convert the date value in a CSV file which is in the format 20100513 into dd/mm/yyyy when importing into the table
First import data to staging table. From there format the date
Hai
I need code for,
How can i import data from excel file to SQL server2000 in vb.net2005.
I need d code in clear manner with clear step.
Can anyone help me?
Regards
Sowmi.
Refer this post
You need OPENROWSET function
If is it possible to UPDATE the table by using Excel Sheet.
yes, use linked server with provider for excel 8.0
Thank You For your Reply, Please I need the Query.
Hai Mr.Madhivanan,
Thanks for ur reply,
But am new to vb.net.
I don’t know where to write d code dat u have posted.
Can u give full code.
Thanks in advance.
Regards
Sowmi.
You need to execute that code in VB.net application just like you run a insert statement
Just want to say you that you are Super star , thx lot you solved my big problem.
God blessed you
Rgds
AHmad
C# code for inserting values in SQl DB using ASP.Net
Refer this site http://www.asp.net
Hi.. All,,
Select col1 from test
O/p
Col1
1
2
3
But I need to display the col1 like this
col1
1,2,3
It is Possible to display like this, i need the query, Please help me
declare @col varchar(8000)
select @col=coalesce(col+’,’,”)+col1 from test
select @col
Hi Pinal,
UNC path is not working to bulk insert in sql server 2000
plz give me solution.
Regards,
Masih
Make sure the Server has proper access to the UNC path
Hello Pinal,
UNC path is not working to bulk insert in sql server 2000
plz give me solution.
Thanks,
Masih
hello pinal,
i have to import the data from notepad to sql server 2005.
notepad contains the data:-
BILLING_ENGINE_ID=41|SCP_ID=54342002|SEQUENCE_NUMBER=70196863|CDR_TYPE=1|RECORD_DATE=20100428102018
Billing_engine,scp_id,sequence_number,cdr_type,record_date are the column names.
Import the data from column fields to 41,54342002,70196863
like that
can u help me regarding this issue
Thanks & regards
kalyan
Thanks, worked for my needs.
its nice query
its ture that you are god of sql server
thankyou sir
keep it sir
I used the above coding in sql server 2005 but i am getting below errow. Pls can you help me?
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:\csvtest.txt” does not exist.
Note that the file should be in server’s directory and not in your local system
Hi sir,
I tried out the above coding in sql server 2005 but I am getting below error: can you help me why i am getting this error
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:\csvtest.txt” does not exist.
Regards,
Poongodi
Note that the file location is Server’s location
thank u…