<< Convert Numbers to Words in C# | Populating, Restore and Backup SQL Database using C# >>

Import CSV File Into SQL Server Using Bulk Insert

by Prashant 26. May 2009 00:51

You have large data to import to SQL Server, and you have all the data in a CSV File (Comma Seperated Value file). Are you going to map the field one by one?

OR just use a small function od SQL to import everything in the table. But before you proceed with this you need to map each column of CSV with the one with your table column. Look at the below quick example on how to use the BULK INSERT to import CSV file data into SQL Server.

Step 1: Create a CSV file.

Step 2: Create a table and map the fields accordingly: (Check below screenshot)

Here is the CODE:

BULK INSERT TestTable
FROM 'c:\TestData.csv'  -- Full path of the CSV file
WITH
(
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n'   --Use to shift the control to next row
)

If you enjoyed this post, make sure you subscribe to my RSS feed!

a3fe4d7d-8368-4d8a-ace1-55356495098e|2|5.0

Tags: sql, bulk insert, csv import

T-SQL

Insert binary files to SQL server using BULK operationPerforming BULK operations with SQL can be useful when you are trying to insert any type of file.&nb...HTML5 File Drag and Drop Upload With jQuery and ASP.NETI came across an article on Tutorialzine which demonstrate file drag and drop upload with jQuery and...Use Sql query to write/create a fileThis SQL stored procedure will allow you to write to the file on your file system. Note the file sys...

Comments (5) -

Brian

2/2/2010 7:21:22 AM #

Your post has been the most helpful of all.

Is there a way to insert a csv file that is on a different computer?

Thanks for your help

Brian

Prashant

2/3/2010 5:48:26 AM #

Yes you can Brian......

In that case your path for the CSV should be the one where you CSV file is placed. In my case it is at '\\ENN-14\Shared Folder\Test csv'.

Right now I am using a machine on a domain so cannot show how to get the file from the C or D drives. But in that case you can have the path of the CSV as

'\\ENN-14\D$\Shared Folder\Test csv'

Note the D$ represents the drive letter

BULK INSERT TestTable
FROM '\\enn-14\Shared Folder\Test.csv'-- Full path of the CSV file
WITH
(
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n' --Use to shift the control to next row
)

Brian

2/4/2010 6:02:06 AM #

Prashant,

Thank you so much for your help.

Brian

kani

1/27/2012 11:43:37 PM #

Thanks u.

Is der any possible to add static column value in this bulk insert. Current date column.

Prashant

1/28/2012 7:09:22 AM #

I haven't tried that, but you can set the default value of the column in the create table script above and then try using the bulk insert. I hope this would work.

Add comment