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
My CSV file doesn’t have the same number of columns
Plus the one who imported this CSV file didn’t export the product ID with them
===========================================
I have products table with 9 columns with 50000 rows
CSV only has 4 columns and 40000 rows
I want to update the product table with importing the CSV
i don’t have the product_id in the CSV but i have another Unique Column which is Part Number
Can I do that
===========================================
Phpmyadmin
When i export data Excel to Sql then give me this error
“The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered.”
So any solutions for that
You need to install Jet engine for EXCEL
First of all, Thanks a lot!
You have said that “If there is any error in any row it will be not inserted but other rows will be inserted.” ,
Is there any way to know which of the rows have encountered conflict and are not inserted?
Very good post , keep it up:)
What if you have txt file you are trying to load, see below
123342,”John”,”Thomas, J”,”123 Main St, Suite1″,”MainCity”,”NY”,36543,03,12/23/1970
where fields are seperated by vommas, but there are commas within fields as well e.g. address, last name, and some fields have quotations around them but some don’t.
Thanks a lot.
In most cases, can’t you just use the “Import” feature of MS SQL Server 2005 (and above). E.g., right-click on the database name, select Tasks -> Import, then select the source, identify the parameters, select the destination, tweak the output column names and there you are!
Hi
Currently i have a data in text pad as mentioned below.
6119039935,61190,5,Chaitanya Ravuri,United Kingdom
4155039958,41550,5,Darshan Shinde,United Kingdom
53649739917,536497,6,Nagasubramanian Natarajan,South Africa
As i am trying to upload the above data in the table, but i am getting an error.
The commands which i used
BULK
INSERT desk
FROM ‘c:\testing.txt’
WITH
(
FIELDTERMINATOR = ‘,’
)
GO
Error
(Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 13, column 2 (empid).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 19, column 2 (empid).
Msg 4864, Level 16, State 1, Line 1)
please help in resolving the the same
Hi – I tried to bulk insert to sql server 2008 express installed in my machine with the script below, but it does not work.
BULK
INSERT Project
FROM ‘C:\mine\project.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
The error msg said The file “C:\mine\project1.txt” does not exist.
Is this because of the extra folder on C drive? I can’t access the root C drive.
thanks
Jack
Hi Pinal
I am facing the follwing problem while doing a bulk import from an Excel file to SQl Server 2005. Let’s say we have an excel file with columns ‘int a’ and ‘xyz int abc’ without the quotes. While doing Bulk Insert the data in the first column which has a keyword ‘int’ at the start does not get inserted but the second column data gets inserted where the keyword is in the middle. How can I resolve the issue? The excel file is an auto generated file from another system and the column name cannot be changed in the excel.
CREATE TABLE GEOIP_Test(
Begin_ip VARCHAR(500),
End_ip VARCHAR(500),
Begin_num VARCHAR(500),
End_num VARCHAR(500),
Country VARCHAR(500),
Name VARCHAR(500)
)
BULK
INSERT testdb.dbo.GEOIP_Test
FROM ‘E:\GeoIPCountryWhois.csv’
WITH
( FIRSTROW = 2,
FIELDTERMINATOR = ‘t’,
ROWTERMINATOR = ‘\r\n’
)
GO
ERROR:
Msg 4866, Level 16, State 8, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 6. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.
Where am I going wrong
Hi Pinal
I have a question
how to import these data in sql server
ex.
0001 0011 012512 6201 3 1
0001 0012 012512 6285 6707 1
0002 0002 012512 6201 3 1
0002 0001 012512 6800 6285 1
0004 0005 012512 6285 6289 2
0005 0006 012512 8301 8000 2
0006 0017 012512 6800 6285 2
0007 0004 012512 *08 003 6210 9501118529# 83941 2
0008 0003 012512 6375 6285 2
0009 0042 012512 *08 002 6901 9888646977# 1
0009 0008 012512 6375 6285 2
0013 0002 012512 *08 004 6808 9501118521# 83941 1
0014 0002 012512 *08 005 6781 7837427169# 1
0021 0021 012512 *08 006 9878815996 6777 2
0023 0001 012512 8000 6418 1
0023 0003 012512 6176 3 1
Oh thank god… This has saved my day! This is the only method of importing a spreadsheet into SQL server that actually works.
I’ve been messing around with the Import excel sheet function and it is pathetic to say the least.
I think your drop statement is wrong:
“–Drop the table to clean up database.
SELECT *
FROM CSVTest
GO”
:)
Thanks!
Thanks sir ! I got what i really wanted.
hi
i want to get data from CSv file into sql database.suppose the CSV file changes that is updations will be done on CSV file for every 8 hrs or 4 hrs like that.Then i have to get the updations reflected in my sql datbase also. Then how it is possible ????
I very much appreciate this information as it was extremely helpful in getting data into our SQL database. However, I have multiple .csv files that I now need to import into the table. What is the best method to accomplish this?
I have a text file with one column having approximate 15 lac records, but i am not able to upload using the above method, it is working file with 1 lac record please help me to solve out the problem.
What did you mean by not working?
Hi,we have email that includes email body attachment and our system was process the email body, now we change our system and it required to read and process attachment instead of email body, so can you please help me to fix this problem
Also I just want to let you know that the attachment is csv file
Regards