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
@imran;
delete all the single quotes and type again. it should not be like this
‘,’
but like this
‘,’
Hi,
I want to know y does the following script run in SQL and not in T-SQL
——————————————————-
DECLARE @tblName varchar(30)
SET @tblName = CONVERT(VARCHAR(20),GETDATE(),112) + ‘Table’
DECLARE @sql nvarchar(4000)
SELECT @sql =
‘CREATE TABLE “‘ + @tblName + ‘”
(
ID VARCHAR(15),
Name VARCHAR(15)
)’
EXEC(@sql)
go
——————————————————-
it gives you the error
Msg 170, Sev 15: Line 1: Incorrect syntax near ‘20090714Table’. [SQLSTATE 42000]
Re:
Anonymous
Hi,
I want to know y does the following script run in SQL and not in T-SQL
——————————————————-
DECLARE @tblName varchar(30)
SET @tblName = CONVERT(VARCHAR(20),GETDATE(),112) + ‘Table’
DECLARE @sql nvarchar(4000)
SELECT @sql =
‘CREATE TABLE “‘ + @tblName + ‘”
(
ID VARCHAR(15),
Name VARCHAR(15)
)’
EXEC(@sql)
go
——————————————————-
it gives you the error
Msg 170, Sev 15: Line 1: Incorrect syntax near ‘20090714Table’. [SQLSTATE 42000]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MY REPLY:
This error is being generated by T-SQL because you are trying to create a table with a digit as the first character of its name.
Re:
imran
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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MY REPLY:
T-SQL is reading the first row of your data file (C:\data\orders.txt) as data. If this row contains column headings, then you want T-SQL to begin reading on the second row. Therefore, you should add FIRSTROW = 2 below the ROWTERMINATOR = ‘\n’, statement.
@DNJ
I agree with Imran, with an additional comment; you can not insert 600,000 records into Excel in versions prior to Excel 2007.
Hi All,
please continue the thread instead of raising the another question.
I read the article, but I have a question too. I’m trying with bulkcopy to copy data from an excelsheet to my DB.
In one column there is the telephonenumber in different formats: 012-3456789, or 0123-456789 or 00321234567890. The first two are seen as text but the last is seen as number and won’t be insert into my table on the DB. My column in the db table is a varchar, all data can go into there.
Can anybody help me how I can solve this problemn?
Greetings,
Johan
@Johan,
You first load data into temporary table, in this temporary table make the data type of the column compatible with Excel Sheet i.e. nvarchar(255). Once data is in Temporary table, then you can play as you want.
I believe SSIS has a functionality in which you could change data type of column. I am not sure.
~ IM.
Thanks for ur query. It worked me a lot.
Thank u very much
Satish
@Imran Mohammed
Do you have some sample code for me? I searched all along the internet but I couldn’t find any good sample code.
Thank you very much
Johan
What do you do for commas WITHIN text qualifiers?
Hi Pinal
Please help me
Iam using sql server 2005,..I want to do a similar operation..
I have my file in C:\Test.txt. Also I create the table in my DB.
BULK INSERT CSVTest
FROM ‘c:\Test.txt’
WITH
(
FIELDTERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n’
)
GO
When I run this …am getting error saying…
Cannot bulk load. The file “c:\Test.txt” does not exist.
infact, I have the file given in the path.
Thanks,
Krishna
Hi Krishna,
Please make sure file is on the same PC where SQL server is installed, not on client location.
Thanks,
Tejas
@Tejas Shah
Awesome suggestion. I was having the same issue as Krishna and forgot that I had SQL server installed to a different box.
Thanks,
Justin
I’m trying to do just like this but in Sybase. Will anyone share the code?? Does Sybase has something like this?
Is it possible to import xls file this way?
Thank you, Its working I had tried good time with it.
God Bless You!!!
Dear Pinal,
I am also SQL Server DBA professional since last 4 years and before that i have worked as a SQL developper cum Database Analyst also using .Net 2.0 Frame work For Web & Consol based Application and VB 6.0 for Window based application.
Thanks for your articles, As your faster way to writing your articles sometimes you have to do mistakes like
–Drop the table to clean up database.
SELECT *
FROM CSVTest
GO
Inyour image file seems fine
Go
DROP TABLE CSVTest
–Drop the table to clean up database.
GO
I think you have need to have a look once of all your articles.
Regards,
Rajiv Singh
Hi Pinal,
If I execute the below query
BULK INSERT InsertTest
FROM ‘D:\Test.txt’
WITH ( FormatFile=’D:\Test.fmt’)
Getting error as
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)”.
Can you help me out how to solve this?
Regards,
Anitha
I have tried it with C:\ drive on local machine, BUT it does not work for local machine, works ONLY if the file is on the Server.
so your @filename on the bulk insert would look something like
\\\\filename.csv OR filename.txt
BULK INSERT does not work for excel files.
You have to save EXCEL file as CSV and then use it with the
bulk insert sql command.
PS:There is another option to use OPENROWSET to upload excel sheet data as follows:
select * from
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\’, ‘SELECT * FROM [Sheet1$]’)
BUT for which you need to have Microsoft.Jet.OLDEDB correctly
installed & all drivers in OK status, else it fails.
Hope it helps.