Importing CSV Files Into MSDE Databases

  • Thread starter Thread starter Fred Chateau
  • Start date Start date
F

Fred Chateau

What's the easiest way to update an MSDE database with CSV data using C#?
(I'm assuming all that DTS stuff is not licensed for use with MSDE. And if
it is, how do you access it from .NET?)
 
Hi Fred,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to import file from an CSV
file to an MSDE database. If there is any misunderstanding, please feel
free to let me know.

As far as I know, the SQL DTS is the easiest way to achieve this. We can
use DTS to transfer data directly to the MSDE server if you have SQL client
installed.

However, we can also use C# code with ADO.NET to do this. Generally we can
use a DataSet as the temp table to store data in memory, and pass data to
MSDE server. Use an ODBCDataAdapter to fill data from the CSV file and
insert records with a SqlDataAdapter. Here I have written a code snippet.

DataSet ds = new DataSet();
OdbcDataAdapter oda = new OdbcDataAdapter("SELECT * FROM Table1.csv",
this.odbcConnection1);
oda.AcceptChangesDuringFill = false; //Make sure to add this line, so that
the RowState will be marked as Added.
oda.Fill(ds, "Table1"); //Put data to a temp DataSet object.

SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Table1",
this.sqlConnection1);
SqlCommand com = new SqlCommand("INSERT INTO Table1(Field1)
VALUES(@Field1)", this.sqlConnection1);
com.Parameters.Add("@Field1", SqlDbType.VarChar, 50, "Field1");
sda.InsertCommand = com;
sda.Update(ds.Tables["Table1"]); //Update the data to MSDE database.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
...
As far as I know, the SQL DTS is the easiest way to achieve this. We can
Click to expand...
use DTS to transfer data directly to the MSDE server if you have SQL client
installed.

Thank you. I appreciate the code snippets, which were certainly more than I
expected . . .

These CSV imports to an MSDE Web content database must be done on the
server, on a recurring basis. If by SQL client, you are referring to the
assortment of client utilities that come with SQL Server (Enterprise
Manager, DTS Wizards, etc.) my understanding of my license for those
utilities is for development use only.

My concern is for the best way to accomplish this on a regular basis in a
production environment, after I have removed myself from the process. If it
is possible to create a DTS import package with the development tools on my
developer workstation, and then simply run it on a server which has MSDE
installed, I suppose it might be worth looking into, as I am already
importing the CSV files manually at my workstation, using the DTS
Import/Export Wizard.

Otherwise, it would appear your C# code would be the proper way to go.
 
Fred said:
...



use DTS to transfer data directly to the MSDE server if you have SQL client
installed.

Thank you. I appreciate the code snippets, which were certainly more than I
expected . . .

These CSV imports to an MSDE Web content database must be done on the
server, on a recurring basis. If by SQL client, you are referring to the
assortment of client utilities that come with SQL Server (Enterprise
Manager, DTS Wizards, etc.) my understanding of my license for those
utilities is for development use only.

My concern is for the best way to accomplish this on a regular basis in a
production environment, after I have removed myself from the process. If it
is possible to create a DTS import package with the development tools on my
developer workstation, and then simply run it on a server which has MSDE
installed, I suppose it might be worth looking into, as I am already
importing the CSV files manually at my workstation, using the DTS
Import/Export Wizard.

Otherwise, it would appear your C# code would be the proper way to go.
Click to expand...

Develop and store a local DTS package on your server then,either
schedule it to execute on some regular interval or have someone (anyone)
execute the package form the server (or remotely).
 
Hi Fred,

According to your situation, I think there are two ways to achieve this as
hclarius mentioned.

1. You can schedule the import/export jobs in DTS wizard. However this
requires your production server import data regularly.

2. Create a DTS package and save it as a Visual Basic module.
1) In a server instance, navigate to Data Transformation Services / Local
Packages.
2) Right click the right pane and select New Package in the pop up menu.
The window for creating the DTS package will appear.
3) Drag a Text File (Source) as data source and drag a Microsoft OleDb
Provider for SQL Server as destination.
4) Drag a Transform Data Task. Click on the Text File as source and click
on the SQL Server as destination.
5) Select Save As from the Package menu.
6) Select Visual Basic File as Location.

It will save the DTS package as an .bas file which can be added as a module
into the Visual Basic project. You can make some modifications to it to
meet your needs.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Fred,

I forgot to mention that the .bas file is for VB6, if you need to use it in
.NET, you need to make some changes to it.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
On Tue, 23 Mar 2004 11:26:07 GMT, (e-mail address removed) (Kevin Yu [MSFT]) wrote:

¤ Hi Fred,
¤
¤ I forgot to mention that the .bas file is for VB6, if you need to use it in
¤ NET, you need to make some changes to it.
¤

Couple of KB articles might help him out:

HOW TO: Use DTS Package Events in Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;321525&Product=vbNET

HOW TO: Create a DTS Custom Task by Using Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;328587&Product=vbNET


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
...
I forgot to mention that the .bas file is for VB6, if you need to use it
Click to expand...
in NET, you need to make some changes to it.

Thanks. That addresses my primary concern here . . .

I didn't understand that Visual Basic was the basis for these DTS packages,
and I was concerned if I tried to take this route, I would eventually wind
up discovering that at some point, a SQL utility or SQL runtime of some
kind, would need to be installed on the server, which would violate my
license.

It may be easier just to use the C# code you posted.
 
Hi Fred,

Since using DTS in .NET requires DTSpkg.dll interop, using the C# code I
posted will be more easier for you to achieve this.

Besides, you can also check the KB articles that Paul provided if you are
insterested in DTS.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
you could also use the OPENROWSET function..

this SQL snippet will allow you to import the C:\Customers.csv file directly into your MSDE Databas

SELECT
INTO myImportTabl
FROM
OPENROWSET('MSDASQL'
'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\;Extensions=CSV;'
'SELECT * FROM Customers.CSV'
 
Back
Top