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
Thanks , this is very useful.
I have some questions about this bulk.
I have the table that has 15 columns and the excel file has 10 fields. I would like to import this excel file and another data into this table. Could I use this bulk for this case? If I can use this bulk How should I do?
when i was doing the same thing as it had described the above article i tried more then 3 time but it is no taking my first row at all and showing me the error ‘Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID)’
wht can i do for this
@sarika,
Please provide below information,
What is your Source, Text File or Excel Sheet.
What kind of data are you trying to insert in first column, what is the data type of First column in the destination table.
Try changing datatype of first column in destination table to Nvarchar(255) and then try to do bulkinsert, if data loads into your table then problem is with your source, you need to format your source and remove invalid characters from your source first column.
~ IM.
@Sarika
Hai Sarika…
U r sending the 1st column as int from excel to db, but in db u r not declared that 1st first column as in.
Thanks & Regards
MV Nagarjuna Rao.
I want catch bulk insert errors in a file. I tried with adding ERRORFILE option in bulk insert but it doesn’t work.
Please, guide on this.
What did you mean by “it doesn’t work?”
Note that the file will be located at Server’s directory by default
Actually – I DO get an error file created when I use the ERRORFILE option with Bulk Insert. But my biggest complaint is that the .Error.Txt file that gets created with it is useless. In the MESSAGE window of the console I get a nice descriptive error such as:
The bulk load failed. Unexpected NULL value in data file row 7263, column 6. The destination column (EffDate) is defined as NOT NULL.
But in the .Error.Txt file it just says:
Row 7263 File Offset 246943 ErrorFile Offset 26 – HRESULT 0x80004005
NOT very helpful.
insert INTO tbL_excel
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=\\pc31\C\Testexcel.xls’,
‘SELECT * FROM [Sheet1$]’)
anybody help me solve this.
Local Excel sheet to SQL(Remote) Server
Did you get any error?
Anyone help me do a bulk export ? Im trying to get sql tables from SQL Server 2005 into SQL Server Compact … thanks !
Hey ! Its really nice & simple !!
But when I’m doing it from an excel 2003 file, few of the columns in the table shows as “null” whereas its actually not (“Text” as for e.g).
and also plz help how to updat the SQL table automatically by manually updating the rows/columns of the source excel file ?
hi,
I have a similar question. How to read a file name(.tif) in SQL stored proc and split the file name and compare that with some already defined strings and if that is valid then i need to insert that name as a column field in a .txt file and save. If that is not a valid keyword while comparing the already defined once, that file has been moved from the current folder and moved to the Rejected folder.
Please advise.
Thanks
Meghana
i for got mention an example
This is the path that i have read the files like :-
\\C:\Faxes\AI in this i have a file like this 8AIL998HOT.tif
now i have read the and compare the file name like 8 is company code and it was defined as ‘008-lucky group LLC’,AIL was defined as “Airline Southwest” …and so on like that
i need to read that file name and save those values in the .txt file.
the o/p txt.file looks like :-
‘0008-lucky group LLC’,’Airline Southwest’,’0998′,’HOT’,’\\C:\Faxes\AI\8AIL998HOT.tif’
like this all the entried have to be saved in the .txt file.
I have to write a Stored Proc. Please share your thoughts.
thanks
Meghana
Hi Pinal,
I am using bulk insert to copy data from text file to SQL server table.
I want to know the number of rows affected by bulk insert, to know whether all rows in the file are copied or not.
Also I want to generate error file if any error occured while bulk inserting data file.
Please, guide
Thanks
Thank you, Sir! :)
BUENA ,.PERO QUE BUEN EJEMPLO ME SRIVIO MUCHO GRACIAS
Nice description…
Really good
Thanks very much… saved a huge amount of laborious manual entry… Awesome tip.
Exactly what I was looking for… thanks again Pinal!
Hi,
any one help me how to import data from exl sheet to the table in remote server
You can use OPENROWSET Function
Refer this post to know the example codes
Hi Ningappa,
To import excel sheet to remote server, either you should able to access remote server on your local and process excel file OR you need to copy excel file on remote server and access it using OPENRowSet as Madhivanan specified.
Thanks,
Tejas
SQLYoga.com
Hii
i am new to SQl, i have given a task of importing excel files in a folder to 1 table in SQL2008 and it should not have any duplicate row.
All files are having same field.
How to do this? Please help.
Use staging table
Insert into staging_table(col)
select * from OPENROWSET(…)
.
.
After all inserts run
insert into main_table(col)
select distinct col from staging_table
For OPENROWSET function and code examples, refer
Hi,
First my java code downloads some file from a ftp location then execute a sql server procedure to bulk insert this file into a tbale but I am getting an error that this file cannot be processed as it is currently used by some other process..
Can you please help me what can be done to fix this problem?