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
Hi,
Great forum! I am encountering an issue that I have always been able to overcome, but this time it has me stuck. I have a .txt file that is comma delimited. There are some data issues in the file. Normally, I would import all records into one column, then parse from there. I am using {LF} as both the column delimiter and record delimiter, so that all data will go into my table that has 1 large (nvarchar (4000)) field. When the import hits line 62007, I get an error message “Column Delimiter Not Found.” I can look at the text file in jujuEdit and see the {LF} at the end of each column.
Has anyone encountered anything like this before?
Thanks!
The command is run from the sql server, you can put the file anywhere on the network and you should no longer get the error that the file does not exist.
Did anyone ever get a solution to the text qualifiers (double quotes in the text)?
Hi Melissa
I tried to insert text which has double quotes in that text, its executed successfully with out any error.
my text file data is like this
12 I need “my” car brakes to be changed
14 ertetr etert ert etert
15 i need a land line phone in my home
if its not the case can you describe problem details.
Hi all!
my problem is that during bulk insertion, it is showing that
“You do not have permission to use the bulk load statement.”
so can anyone tell me how to give permission for bulk insertion.
You must have sysadmin level security to execute a bulk insert command.
Thanks .it’s realy helpfull for me
now i do it from server side. i want to do t from frontant. i use VB.NET 2005 and SQL SERVER 2005.From a form i want to do the work when i click on butto. now where i write the code.
Create a procedure that accepts file name as parameter
You can execute that procedure from the VB.NET application by passing file name
Pinalbhai!
It has helped a lot. Especially since Microsoft has not provided SSIS in SQL Server 2005 express edition.
Hi All,
I want to read the the csv file and insert into table using sql server 2005. I have code below. This will do that insert using OPENROWSET function. My pblm is when i give th .csv file with out header it throws an exception like ‘Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name “NoName” is a duplicate.’ can anyone solve this pblm
Dim strSQL As String
strSQL = “Select * ” & _
” INTO ” & DATABASE & “.dbo.[List_staging] ” & _
“FROM OPENROWSET(‘MSDASQL’,’Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\VoicenetSQL\project\tampa\Politic\” & projectfile & “; Extensions=CSV; HDR=No;’,’SELECT * FROM at1008.csv’) ”
Thanks in advance..
G.V
You need to remove that column Noname from csv file to successfully execute the script
This may be the best tech blog I have ever seen. Keep up the good work!
Hi Pinal,
Thank’s a lot. It has solved my problem.
Regards,
Nadeem.
Hi,
I need to restore the data from SQL server 2005 to 2000. In my data there are many languages(it means that data has mixure of all characters).
Are datatypes similar in SQL server 2000 and 2005? am receiving the problem datatype problem..
can any one suggest me how do i restore it?
Regards,
Minchala
excellent!!!!!!
Just wanted to say thanks. This code worked perfectly for a simple program i wrote to run at a scheduled time. It saved me so much work from having to manually parse the CSV!
Hi, thanks for the post, I noted that the size of the table is too big you can reduce it?
grateful,
Edson
Hi Dave,
You are amazing, thanks for sharing your knowledge.
I need a stored proc to import data from a .txt file to a table in sql server 2000 but I need to do it using BCP command and the stored proc should also perform error handling, if possible could you please post the stored proc which performs this job.
Thanks! I’m glad your blog always comes up in one of the first google hits. You help a lot :)
It is an excellent example indeed .Thanks.
I am trying to load a CSV file from a VB .NET 2.0 app. Since the CSV file is in user’s local drive, I can not use BULK INPUT method, can I?
Bulk Input method requires the CSV file to be present in the SQL Server’s local drive/folder, right?
I will appreciate if you can comment on my assumptions. Thanks.
Regards,
Mehdi Anis
If it is in a User’s machine, use UNC path like
//machine_name/folder_name
and make sure the file is readable by a Server
Dude. You just saved my life.
Hi
I’m having some problem something similar to what you have explained, bt the only difference is that my data in csv file is not seperated with commas, its all chunk together bt i want my output to be like yours. Any idea how i may be able to do that?
Thanks in advance. =)