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
the code above is very simple and easy.
but i get an error when i try to use it.
the error is as follows:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “cdatatempfile.txt” could not be opened. Operating system error code 3(The system cannot find the path specified.).
i tried adding the name of the computer to the address hoping it will work but i get the following error:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “\LTSR397941cdatatempfile.txt” could not be opened. Operating system error code 53(The network path was not found.).
we are using SQL server 2008 with windows authentication.
sql server resides on a remote server to which my i have access.
can anyone tell me what is wrong with the situation.
thanks
Vijaya
Have you tried this path?
\LTSR397941c$datatempfile.txt”
Also make sure that the file is shared to the windows user
Leave out the server name (your file must be on the same server as your database) and put a colon after the C.
ie, c:\data\tempfile.txt
I tried that also. i tried putting the file on the same server and tried typing the path with and without a “:” after c. on the server it gives me an
Operating system error code 5(system does not have permission to open the file).
my manager said instead of trying to open the file from the server i should copy it onto my local machine and work from there. he is wary of giving permission for the server.
so i use a batch file to copy the file from the server to my local machine. but i have to bulk insert it to my sql table.
thanks for trying to help though.
vijaya
Hi,
Thanks for your code.
I have a small problem, could you help me?
I have a text file with this structure:
0,udp,private,SF,105,146,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0.00,0.00,0.00,0.00,1.00,0.00,0.00,255,254,1.00,0.01,0.00,0.00,0.00,0.00,0.00,0.00,normal.
0,udp,private,SF,105,146,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0.00,0.00,0.00,0.00,1.00,0.00,0.00,255,254,1.00,0.01,0.00,0.00,0.00,0.00,0.00,0.00,normal.
….
each of row is a network connection log, but this file didnt save with “.txt” extension, it is only file name, example “Log01”, “Log02” without extension.
And this is my code:
BULK INSERT MyFile FROM 'D:\Log01' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )But when i execute, there some error occured:
[error]
Msg 4866, Level 16, State 1, Line 6
The bulk load failed. The column is too long in the data file for row 1, column 42. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 6
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 6
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
[/error]
When i created a text file by text editor, such as notepad, with the same content (i only copy from log file to text file) and re-execute. Wow, It’s worked fine.
Could you help me explains this case and show me the way to import directly from logfile to a table without copy to text file, because logfiles are very big (GigaByte). i waitting for you answer.
Thank you very much and best wishes for you.
Nam
The file name needs extention. Otherwise SQL Server may think that it is a folder name. Can you just renmae it to have a file extention?
Hi,
How to import data from excel 2010 (only sheet2 data) into sql temporary table?
my requirement is, take bulk data into one temp table from excel 2010.
And remove some columns & adding some conditions finally load this sorted data into destination table.
Please help me, Thanks in advance
Cheers,
Sarath
U&se OPENROWSET function which can directly read the data and load it into the table. Refer this post
Wow! Thats a great example. Thanks a lot sir.
Very much……helpful.
Hi Pinal,
Just wanted to thank you ^ 10th for the brilliant insight you provide to the community of SQL enthusiasts/users. You break the “rabbit hole” SQL logic down into lingua franca so that it is accessible to anyone interested.Thanks so much. Michael
thanks alot.. its worked
Hi there,
I using the follow code to insert data from an CSV file to my data table
BULK
INSERT dbo.MessageFocusKnownBounce
FROM ‘\\192.168.16.184\SQL\hardbounces.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
I am getting the following error message
OLE DB error trace [OLE/DB Provider ‘STREAM’ IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘STREAM’ reported an error. The provider did not give any information about the error.
The statement has been terminated.
Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 6. Make sure the field terminator and row terminator are specified correctly.
Can any one help please
Regards
Katan
It means that column number 6 in the table has lower width than the data length. You need to increase the size of that column
Thanks …..
Pinal
I’d like to make your readers aware of our new free CSVexpress offering, which allows them to quickly and easily load any CSV file into any database.
Thanks
Michael
The question I have is: does it support variable columns? In other words, variable record lengths? Some of my import files have 3 different records. Some are 413 byes while others are 1027 records and others might be 28 records — all in the same import file and they need to be imported all at once into the same table (table gets NULLS for columns not used in the shorter records)…
Not sure if it possible in the tool, but it is possible in SQL Server Management studio as you as you map them with the format file. Refer this for more informations
BULK INSERT CusImportCurrentBatch from ‘D:\BathRugby_PositiveData\customers_2011-09-03.CSV’ with (FIRSTROW = 2, FIELDTERMINATOR = ‘,’
)
BUT my CSV FILe Do does not have rowterminator. thats why it returm error. when i use ssis package . it works perfectly.
any idea?
Your file should have a row terminator. Otherwsie It will import only one row data
BULK INSERT can also export data files. xls excel? if someone knows help me.
I am having a .CSV file with 1st row as column heading. I want to take these column headings and create # table of it and then do the BULK INSERT into this # table. I am not getting how to get the 1st row, seperate the column headings seperated by , and create a table of it. Please help.
Thanks in advance …
In Bulk insert specify first_row and last_row option as 1 so that it will pickup only the first row from the file
Hello Madhivanan,
I think you did not get my problem. My CSV file starts with say
Cust_ID, Cust_Name, Cust_Address, Cust_Phone, etc…..
and then from 2nd row onwards the actual data of these columns starts.
Now my problem is that I want to get the first row of this file e.g. Cust_ID, Cust_name, Cust_Address……Create a temp table of these columns and then do the BULK Insert of the values of these columns (i.e. from 2nd row onwards)
As suggested by you I cannot do the BULK Insert by keeping option as first_row and last_row as 1. It will give me conversion error.
In Oracle we have functions like utl_file.fopen, utl_file.get_line, etc…Likewise what do we have in SQL Server 2005?
Hope you understood my problem….
Thanks…
Refer this post
Use the second method which uses openrowset function and limit it to first row by using top operator;create a table based on those columns dynamically;Do bulk insert by setting first_row=1
Thanks madhivanan for your help,
Check out to load any CSV file to any database for free!
To import data from *. csv file, I propose to open it in notepad and save it in unicode format and import it before. Imports are also a line from the file :)
Bulk insert is actually throwing errors with respect to permissions. Any clue as to what might be wrong? I have full administrator permissions to the database. Also has anyone used the bcp command?
What is the error that is generated when using bulk insert statement?
I am new for this problem. My data csv file contains
modelid,sopid,stepid,label,DESC
1100,1000000,0,0,”Supplies”
And My format file
10.0
5
1 SQLCHAR 0 12 “,” 1 MODELID “”
2 SQLCHAR 0 12 “,” 2 SOPID “”
3 SQLCHAR 0 12 “,” 3 STEPID “”
4 SQLCHAR 0 12 “,” 4 LABEL “”
5 SQLCHAR 0 0 “,” 5 DESC SQL_Latin1_General_CP1_CI_AS
However, when I bulk insert into SQL server 2008, I got the following error.
Any idea? Thanks
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 2, column 4 (LABEL).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 3, column 3 (STEPID).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 4, column 2 (SOPID).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 5, column 1 (MODELID).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 6, column 4 (LABEL).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 7, column 1 (MODELID).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 8, column 1 (MODELID).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 9, column 1 (MODELID).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 10, column 4 (LABEL).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 11, column 1 (MODELID).
Msg 4864, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Bulk load data conversion error (type mismatch or invalid character for the spec
ified codepage) for row 12, column 1 (MODELID).
Msg 4865, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Server localpcSQLEXPRESS, Line 3
The OLE DB provider “BULK” for linked server “(null)” reported an error. The pro
vider did not give any information about the error.
Msg 7330, Level 16, State 2, Server localpcSQLEXPRESS, Line 3
I want to get rid of 1100,1000000,0,0,”Supplies” quote mark after insert into SQL server.
You can write an update statement to remove the quotes. Also you can use format file to remove the quotes
Hi My file saved in notepad got im[orted in database sucessfully.
Thanks.
Now how do i import Excel file in same way.
I got an error as
Bulk insert data conversion error (type mismatch) for row 1, column 1 (ID).
Can u help me.
Regards
Amul
how to store excel file into database table as varbinary datatype..
Have a look at OPENROWSET function in SQL Server help. It has example code on how to do it