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
BULK INSERT CSVTest
FROM ‘é:\sas_csv.txt’
WITH
(
FIELDTERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n’
)
GO
in this query we need to create table already
Is there any other method to insert without creating table structure initially ?
Is it possible to count lines which was read from import file ?
INSERT INTO table1(col1, col2)
SELECT col1, col2
FROM OPENROWSET(BULK ‘F:\test.csv’, FORMATFILE=’f:\test.XML’, ERRORFILE=’F:\test.csv.log’) AS a
WHERE col1%3=0;
hello, how can i update columns based on other colums…for instance i have a database of student names, student id’s and student statuses. how can i update the student statuses from a .csv file matching on the student id’s. any help would be much much appreciated
this is really helpful.
i would also like to ask, how to load a different file (.dat) with a tab/space delimited?thanks
i need reverse query
means, during a button click table contents stored in notepad (.txt)
Thanjks Pinal Dave this post helped a lot …so many tried in in SSMS import export wizard failing.
thanks
My input file has inconsitent double quotes.
1st attempt:
I tried –
BULK INSERT raw_eyecare
FROM ‘Mypath.csv’
WITH
(
FIELDTERMINATOR =’~’,
ROWTERMINATOR =’n’
);
But it gives me error saying
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 116 (tq24).
2nd attempt:
Then I tried the following statement-
BULK INSERT [raw_eyecare_bkp_20130226]
FROM ‘Mypath.csv’
WITH
(
FIELDTERMINATOR ='”~”‘,
ROWTERMINATOR =’n’
);
Again it gives error for some other column.
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (clientno).
3rd attempt:
BULK INSERT [raw_eyecare_bkp_20130226]
FROM ‘Mypath.csv’
WITH
(
FIELDTERMINATOR ='”~”‘,
ROWTERMINATOR ='”‘
);
Again it gives error for some other column.
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (clientno).
Please help
Some of the columns have double quotes while others not. Is there a method which does not uses formatfile.
Simply superb thank u very much sir
This seems to be one of your most popular posts! I am facing a problem that has stumped me for some time and cant find a simple way to get around it.
Its the matter of importing a flat file with these specifications:
1) the number of fields (columns) in each record varies. Example file definition:
record type 1 = 81 bytes
record type 2 = 132 bytes
record type 3 = 23 bytes.
There is no pattern to the file (for example, the import file will look like this:
record type 1
record type 2
record type 2
record type 2
record type 3
record type 3
record type 3
record type 1
record type 2
record type 3
record type 1
record type 2
record type 2
record type 1
2) Also — the delimiter ( a comma ) is actually embedded in the data as well. For example a column might have LAST NAME, FIRST NAME as ** one column **
I know its a bad design. Partly, the data comes from a mainframe computer system, There is no way that I can get these people to change the file. The other problem is that the sender of the file is a government entity. They are *not* going to change the file for me or create seperate files of equal record types so that SSIS doesnt choke.
The problem is that I have been researching this for a while and see tons of complaints. Doesnt Microsoft listen to people? People are asking for this functionality to come back again. It was once there!!! Systems have apparently been broken by “better” newer SQL Server editions! Workarounds have been to write code and run code? Try importing 4 million records of varying lengths into SQL Server using SSIS and an attached script task. It takes nearly 20 hours. And that is just to the required staging table!
SSIS should be able to handle this but it cant. DTS used to be able to handle this perfectly well and so did the Microsoft ISAM Jet driver (importing with SQL Sever 2000, SQL Server 7, MS Access or MS Excel up to v.2007 works). But SQL Server 2005, 2008, 2008R2 and 2012 as far as I have tried does not.
Anyone have a better idea??
Dear Sir,
i have treid your Bulk Insert, but i got this error message
“Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CompDefLevel).”
“Cannot bulk load because the maximum number of errors (10) was exceeded.
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)”.
What should i do? :(
Tq very much sir.
Great post, it helped me solve my problem. Thanks.
how do you load from a file that’s on a remote server?
Cannot bulk load. The file “D:\csvtest.txt” does not exist. getting this error even though file exist in that path
How to remove whitespace when using Bulk import?
Pinal dave ,do you have any article on using SqlBulkCopy ,inserting Line Feed and Carriage Return into Database?
I am using SqlBulkCopy to import excel data to mssql.
I have many rows of data in excel with 1 of the columns which the content has Line Feed and Carriage Return (I understand from reading online below is true)
Chr(10) == vbLF == Line Feed (LF)
Chr(13) == vbCR == Carriage Return (CR)
Chr(13) & Chr(10) == vbCrLf == Carriage Return/Line Feed
The SqlBulkCopy is working perfectly but i want to retain the Carriage Return/Line Feed which is in the excel when its imported to mssql.Currently it is inserted as a long string .
Example below .
One of the cells has a contents below [multiple rows in single cell].
1st line in cell 1
2nd line in cell 1
When i use SqlBulkCopy to import excel data to mssql.
The above status column is inserted as
“1st line in cell 12nd line in cell 1”
How can i retain the “Carriage Return/Line Feed” from the excel cell ?
so that it will insert in database as below :
1st line in cell 1
2nd line in cell 1
Below is the code i have .
—————————–
Dim conectionstring As String = “”
If strExt.ToLower() = “.xls” Then
conectionstring = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & Excel & “;Extended Properties=Excel 8.0”
ElseIf strExt.ToLower() = “.xlsx” Then
conectionstring = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & Excel & “;Extended Properties=Excel 12.0”
End If
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(conectionstring)
ExcelConnection.Open()
Dim expr As String = “SELECT * FROM [Sheet1$] where not U_Id is null”
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = “SL_DataInfo_Temp”
bulkCopy.ColumnMappings.Add(“U_Id”, “di_id”)
bulkCopy.ColumnMappings.Add(“File_Ref”, “di_fileRef”)
bulkCopy.ColumnMappings.Add(“Date”, “di_date”)
bulkCopy.ColumnMappings.Add(“Status”, “di_status”)
objDR = objCmdSelect.ExecuteReader
If objDR.HasRows Then ”And objDR.FieldCount >= 13 Then
bulkCopy.WriteToServer(objDR)
ExcelConnection.Close()
SQLconn.Close()
End If End Using
Hi Pinal,
I need a help. I am new to SQL.
i need to find difference for uniqueID in Hours between two dates(where most of the time two dates can be the same date or different),
and am using DATEDIFF function.
its pulling out data but not what i am looking for, I would like to have the the difference for uniqueID which is “opened recently” and” time of contact” need to fin difference for recent activities for uniqueID where its showing all the deatils for uniquieID which was created in past.
I have tried usinf DISTINCT FUNTION as well but its showing all the data for the particular uniqueID rather than only one.
I know its bit confusing but please can you please help me regarding this.
how to get exact difference between either two different dates or same date in hours for the most recent details for uniqueID.
Hello Pinal,
I have implemented this into my project. I am generating the comma separated text files and doing the Bulk insert. Can you please tell me how much time it’ll take to insert 25 MB file into database. Around 2 lacs rows. Because I am little confused with the performance of this query and my server configuration. Its taking over half an hour on the live server. I have 4GB RAM installed on the server.
Please, waiting for your positive reply.
Thanks,
Yogesh
I found and tried this bulk insert for the first time today. This is a great tool! Pinal, you are my favorite SQL guru.
Hi Guys,
I am facing one problem with bulk insert . i have csv file with pipe delimited values and in the bulk setting the path of the file and using fieldteriminator,rows per batch .While running the stored procedure i am getting following error message ..
cannot fetch row from ole db provider bulk for linked server
Hi, I need to copy bulk records from excel file stored as csv into sql table. I need a simple stored procedure for. I am not suppose to use any tools or packages or any import/export wizard. Kindly provide a stored procedure for this.