30 June 2004
Importing CSV files [C#/Delphi/SQL Server]
Posted by Mikhail Esteves under: C#; Tips .
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!
11 Comments so far...
Paul Menefee Says:
15 July 2004 at 2:27 am.
Note though this only works however if the table doesn’t already exist.
Midimark Says:
24 July 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’)
Angela Says:
24 October 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.
Rick Jolly Says:
17 August 2005 at 10:11 pm.
Oops, sorry ignore my first post. This is the sql I used:
INSERT INTO tableName (column1, column2,...) SELECT * FROMOPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\csvFileDirectoryPath\;Extensions=CSV;', 'SELECT * FROM csvFile.csv')
Rick Jolly Says:
17 August 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 ...
Mike Says:
24 August 2005 at 8:27 pm.
Anybody knows if this script works with excel files???
Thanks!
Tyler Says:
24 August 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.
Asif Says:
22 December 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.
Deepak Says:
6 July 2007 at 12:27 pm.
Great!! Just needed the correct connectionstring and everything fell into place… :-) Many thanks!!!
Upen Says:
11 July 2007 at 5:30 pm.
Thank You Guys… Really very usefull stuff. my problem is solved within
a few minutes.