SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server

SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server

February 6, 2008 by pinaldave

This is 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 csvtest.txt with 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

=========
vonPryz

I had some problems with bulk inserts too. The data set to insert was somewhat large, around a few hundred megabytes, resulted in some two million rows. Not all data was required, so massaging was needed.

So I wrote a smallish C#/.NET program that reads the input file in chunks of 25,000 rows, picked appropriate rows with regular expression matching and sent results to DB.

String manipulation is so much more easy in about any general-purpose programming language.

Key components: DataTable for data-to-be-inserted and SqlBulkCopy to do the actual copying.

@Dnyanesh: Your input data is likely to not have correct column/row separator characters.

I’d guess you have a line-break as row separator. If that is the case, the problem is, in Windows CR LF (0×0a 0×0d) is often used, in other systems only CR or LF are used. Check your separator characters. Use a hex editor to be sure.

@Branden: Just use sed to change quote characters :-)

@Dragan: Use Import/Export wizard or write a program to do inserting like I did.
====
#
Scott

A CSV file can contain commas in a field ‘,’ as well as be used as the delimiter. How does this Bulk import handle these circumstances? Does it handle all the CSV standards?

@vonPryz: See Example Below (don’t just replace quotes)

Example CSV lines with format (Name,Address):

Scott,123 Main St
Mike,”456 2nd St, Apt 5″

More on CSV files can be found here
http://en.wikipedia.org/wiki/Comma-separated_values

#
on February 13, 2008 at 12:02 pm Kumar

Thanks for all in this forum,

I have used BULK INSERT command load CSV file into existing SQL server table. It is executing sucessfully withougt giving any error but only concern is it is inserting every alternative records.
BULK INSERT EDFE_Trg.dbo.[tblCCodeMast]
FROM ‘c:\temp\Catalogue.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
this is my code. Please help me on this.
With advance thanks…
kumar
=========
Dave M

Did it. I used a combination of tools:

1. Lots of brute force - over 20 hours formatting, saving and converting the data. There has to be a more elegant solution but I couldn’t find it.

2. Excel 2007 because it can open a csv file that has over 200,000 rows. I loaded 25 files at a time into a worksheet (max of computer resources), reformatted the columns in bulk and used a nifty VBA script to write each worksheet out to a separate csv with the name of the worksheet.

3. Found a great procedure that uses XP_CMDSHELL and the BCP utility in SQL 2005 that loads all files located in a specified directory. Loaded over 1,300 files in less than 30 minutes.

Now daily maintenance is loading one file a day.

D.
=======================
PSilva

This Procedure can help in ASP.NET

CREATE PROCEDURE ImportFile
@File VARCHAR(100)
AS

EXECUTE (’BULK INSERT TableName FROM ”’ + @File + ”’
WITH
(
FIELDTERMINATOR = ”;”
, ROWTERMINATOR = ”\n”
, CODEPAGE = ”RAW”
)’ )

/*
EXEC dbo.ImportFile ‘c:\csv\text.csv’
*/

C# Code

SqlConnection cnn = new SqlConnection(
string.Format(@”Data Source=.\SQLEXPRESS;Initial Catalog=DB_Name;Persist Security Info=True;User ID=;Password=” ));

SqlCommand cmd = new SqlCommand(”ImportFile”, cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(”@File”, SqlDbType.VarChar).Value = txtFile.text;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();