Standard Method for Importing/Exporting to DB

Snozz

The short of it:
If you needed to import a CSV file of a certain structure on a regular
basis(say 32 csv files, each to one a table in 32 databases), what
would be your first instinct on how to set this up so as to do it
reliably and minimize overhead? There are currently no constraints on
the destination table. Assume the user or some configuration specifies
the database name, server name, and filename+fullpath. The server is
SQL Express, but there is the possibility that the databases may be
moved to Oracle in the future, so any solution that would be easily
ported would be ideal, but is not required.

Mainly I want to know conceptually in terms of tools or APIs how this
would be done, so I know what I should focus on learning. e.g. I
create a DTS package to import one file, use dynamic properties to set
database+server+filename from global properties, and load the DTS via
VB code and pass the database and filenames to the package's global
variables. I would prefer as little dependance on tools beyond what is
available in VB.NET and comes standard on the db server.

I'd also like to know your opinion's in regards to exporting to excel
files, and also exporting to CSV files.

The long of it:
I have taken over some disorganized legacy tools that basically take
sets of CSV files, import them into 32 databases(identical schema,
different data in each), process the data, and then export to CSV
files. In the processing however, there are many steps of exporting
the data back out to excel files, then importing again, etc. and
everything from DTS packages to Crystal Reports are used. I would like
to eliminate dependancy first on the crystal reports, and then possibly
elimnate dependancy on the DTS packages as well.

Nothing very complex occurs in the Crystal Reports, and it appears that
my predecessor was using it to build queries as a crutch for his lack
of knowledge with SQL and set theory. The only advantages as I see it
is that the Crystal Reports can be used for the 32 different databases
simply by specifying new connection information, and Crystal reports
has a built in function to export to excel.

The DTS packages mostly do a straight import of CSV files to existing
tables, and again, the main advantage is reusing the same DTS package
for 32 databases and specifying parameters.

My strength is in C#.net, but here at work I only have the VB.net dev
environment, and existing tools are written in VB. And I have never
done database applications development, but I have done applications
development, and managed databases a good bit in the last year. So all
the different acronyms floating around are overwhelming, I'm not sure
what I should focus on learning.

My goal is to narrow down these tools into a single package, try to
eliminate dependance on DTS and Crystal Reports, eliminate the
intermediate file creations so that all processing is done in the
software or database, and only import at the beginning and export at
the end of the process. Still, there are alot of files imported and
exported, so I want to find out what is the most common technique of
doing importing and exporting of CSV and Excel files.

DTS packages aren't completely out of the question, it would just seem
more natural to not rely on them if there is a commonly used progmatic
alternative.

May 20 '06 #1

tommaso.gastaldi

hi snozz,

the problem you describe can be handled completely with vb.net.

I would define once (32) objects which incorporates information about:

- Database Connection
- Extraction rule (a query or sql script)

then would schedule:

- connecting and extracting the data while loading them to target db
(read/insert at same time, use OleDbDataReader)

- performing the processing on the target db

- export to csv / excel (export to csv is straightforwad, export to
Excel is also immediate if you use open XML. Use buffered strategies )

I have done all these tasks in the past. Let me know if can of more
help...

-tom

Snozz ha scritto:

The short of it:
If you needed to import a CSV file of a certain structure on a regular
basis(say 32 csv files, each to one a table in 32 databases), what
would be your first instinct on how to set this up so as to do it
reliably and minimize overhead? There are currently no constraints on
the destination table. Assume the user or some configuration specifies
the database name, server name, and filename+fullpath. The server is
SQL Express, but there is the possibility that the databases may be
moved to Oracle in the future, so any solution that would be easily
ported would be ideal, but is not required.

Mainly I want to know conceptually in terms of tools or APIs how this
would be done, so I know what I should focus on learning. e.g. I
create a DTS package to import one file, use dynamic properties to set
database+server+filename from global properties, and load the DTS via
VB code and pass the database and filenames to the package's global
variables. I would prefer as little dependance on tools beyond what is
available in VB.NET and comes standard on the db server.

I'd also like to know your opinion's in regards to exporting to excel
files, and also exporting to CSV files.

The long of it:
I have taken over some disorganized legacy tools that basically take
sets of CSV files, import them into 32 databases(identical schema,
different data in each), process the data, and then export to CSV
files. In the processing however, there are many steps of exporting
the data back out to excel files, then importing again, etc. and
everything from DTS packages to Crystal Reports are used. I would like
to eliminate dependancy first on the crystal reports, and then possibly
elimnate dependancy on the DTS packages as well.

Nothing very complex occurs in the Crystal Reports, and it appears that
my predecessor was using it to build queries as a crutch for his lack
of knowledge with SQL and set theory. The only advantages as I see it
is that the Crystal Reports can be used for the 32 different databases
simply by specifying new connection information, and Crystal reports
has a built in function to export to excel.

The DTS packages mostly do a straight import of CSV files to existing
tables, and again, the main advantage is reusing the same DTS package
for 32 databases and specifying parameters.

My strength is in C#.net, but here at work I only have the VB.net dev
environment, and existing tools are written in VB. And I have never
done database applications development, but I have done applications
development, and managed databases a good bit in the last year. So all
the different acronyms floating around are overwhelming, I'm not sure
what I should focus on learning.

My goal is to narrow down these tools into a single package, try to
eliminate dependance on DTS and Crystal Reports, eliminate the
intermediate file creations so that all processing is done in the
software or database, and only import at the beginning and export at
the end of the process. Still, there are alot of files imported and
exported, so I want to find out what is the most common technique of
doing importing and exporting of CSV and Excel files.

DTS packages aren't completely out of the question, it would just seem
more natural to not rely on them if there is a commonly used progmatic
alternative.

May 20 '06 #2

Bhup

Snozz wrote:

The short of it:
If you needed to import a CSV file of a certain structure on a regular
basis(say 32 csv files, each to one a table in 32 databases), what
would be your first instinct on how to set this up so as to do it
reliably and minimize overhead? There are currently no constraints on
the destination table. Assume the user or some configuration specifies
the database name, server name, and filename+fullpath. The server is
SQL Express, but there is the possibility that the databases may be
moved to Oracle in the future, so any solution that would be easily
ported would be ideal, but is not required.

Mainly I want to know conceptually in terms of tools or APIs how this
would be done, so I know what I should focus on learning. e.g. I
create a DTS package to import one file, use dynamic properties to set
database+server+filename from global properties, and load the DTS via
VB code and pass the database and filenames to the package's global
variables. I would prefer as little dependance on tools beyond what is
available in VB.NET and comes standard on the db server.

I'd also like to know your opinion's in regards to exporting to excel
files, and also exporting to CSV files.

The long of it:
I have taken over some disorganized legacy tools that basically take
sets of CSV files, import them into 32 databases(identical schema,
different data in each), process the data, and then export to CSV
files. In the processing however, there are many steps of exporting
the data back out to excel files, then importing again, etc. and
everything from DTS packages to Crystal Reports are used. I would like
to eliminate dependancy first on the crystal reports, and then possibly
elimnate dependancy on the DTS packages as well.

Nothing very complex occurs in the Crystal Reports, and it appears that
my predecessor was using it to build queries as a crutch for his lack
of knowledge with SQL and set theory. The only advantages as I see it
is that the Crystal Reports can be used for the 32 different databases
simply by specifying new connection information, and Crystal reports
has a built in function to export to excel.

The DTS packages mostly do a straight import of CSV files to existing
tables, and again, the main advantage is reusing the same DTS package
for 32 databases and specifying parameters.

My strength is in C#.net, but here at work I only have the VB.net dev
environment, and existing tools are written in VB. And I have never
done database applications development, but I have done applications
development, and managed databases a good bit in the last year. So all
the different acronyms floating around are overwhelming, I'm not sure
what I should focus on learning.

My goal is to narrow down these tools into a single package, try to
eliminate dependance on DTS and Crystal Reports, eliminate the
intermediate file creations so that all processing is done in the
software or database, and only import at the beginning and export at
the end of the process. Still, there are alot of files imported and
exported, so I want to find out what is the most common technique of
doing importing and exporting of CSV and Excel files.

DTS packages aren't completely out of the question, it would just seem
more natural to not rely on them if there is a commonly used progmatic
alternative.

Jun 6 '06 #3