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!


Note though this only works however if the table doesn’t already exist.
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’)
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.
The script works great…
Oops, sorry ignore my first post. This is the sql I used:
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:
with
Anybody knows if this script works with excel files???
Thanks!
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.
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.
Great!! Just needed the correct connectionstring and everything fell into place… :-) Many thanks!!!
Thank You Guys… Really very usefull stuff. my problem is solved within
a few minutes.