Importing CSV files [C#/Delphi/SQL Server]

On a project that I was recently working on, I was faced with the task of having to import CSV files into an SQL Server database. Just before starting off on writing a custom class to import from CSV, I stumble across a simple way of importing data directly into SQL Server:

SELECT *
INTO theImportTable
FROM
     OPENROWSET('MSDASQL',
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=D:\;Extensions=CSV;',
        'SELECT * FROM CSVFile.csv')

And that’s it!

Another way of parsing through CSV files is to open the CSV file as a Dataset using ADO.NET, ODBC or OLE DB and run select/update/insert statement on it! How cool is that? This method can be used to work with Excel Files, XML files, etc. Here is one excellent repository for Connection Strings!

This place has a decent tutorial on how to select data directly from an Excel sheet. Use an appropriate connection string for CSV and the same example should work for you!

  • Share/Save/Bookmark
By Mikhail Esteves | June 30th, 2004 in C#/.NET, Tips

11 Comments

Paul MenefeeJuly 15th, 2004 at 2:27 am

Note though this only works however if the table doesn’t already exist.

MidimarkJuly 24th, 2004 at 12:24 am

That one only works if the table doesn’t exist… I was able to make some changes so that it now works in an existing table… It helped me out, so I thought I’d share what I came up with!

INSERT INTO tableName (column1Name, column2Name)
SELECT *
FROM OPENROWSET};
DEFAULTDIR=C:\cvsFolder\;Extensions=CSV;’, ‘SELECT * FROM cvsFile.csv’)

AngelaOctober 24th, 2004 at 12:15 am

first the 2nd example doesn’t work.

The first example works when the table already exists.

Other special notes are the following.
1) must be a dba or sa user to do this in a programming language (such as access, vb, c#)
2) the default directory is the default directory ON THE SERVER

This would have been nice functionality for me seeing how i am writing an app where they do monthly imports. However, because of the 2 items listed above I couldn’t really use it.

However, I will keep it around incase for future projects.

hoe this helps.

GeorgeMarch 15th, 2005 at 7:45 pm

The script works great…

Rick JollyAugust 17th, 2005 at 10:11 pm

Oops, sorry ignore my first post. This is the sql I used: 

INSERT INTO tableName (column1, column2,...)
SELECT *
FROM

OPENROWSET('MSDASQL',
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=C:\csvFileDirectoryPath\;Extensions=CSV;',
        'SELECT * FROM csvFile.csv')
Rick JollyAugust 17th, 2005 at 10:17 pm

My posts are being reformatted, sorry. Probably the same thing that happened to Midimark. For an existing table use the first solution from Mikhail but replace:

SELECT *
INTO theImportTable
FROM ...

with 

INSERT INTO importTable (column1, column2,...)
SELECT *
FROM ...
MikeAugust 24th, 2005 at 8:27 pm

Anybody knows if this script works with excel files???

Thanks!

TylerAugust 24th, 2005 at 9:43 pm

Also of note here: the MS Text Provider used here will only accept a fairly standard definition of CSV files, and discard any records that don’t conform.

I’ve got a CSV file with subfields delimited with “;”. To wit:
“Field1”,“Field2”,“Field3.1”;“Field3.2”;“Field3.3”,“Field4”,“Field5”

The MS Text Driver will discard a record like this. Excel, on the other hand, will import it with some minor changes. So I’m trying to write a routine that will properly handle a second delimiter such as this (which is not consistently applied). I’m very surprised I’ve not found any code snippets on dealing with this.

AsifDecember 22nd, 2006 at 11:21 am

Dear,

I am new to SQL and fortunately I have to start with to import CSV file into SQL. I found this piece of Code really perfect to meet my 100% requirements.

DeepakJuly 6th, 2007 at 12:27 pm

Great!! Just needed the correct connectionstring and everything fell into place… :-) Many thanks!!!

UpenJuly 11th, 2007 at 5:30 pm

Thank You Guys… Really very usefull stuff. my problem is solved within
a few minutes.

Leave a comment

Your comment