|
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. | |
Share:
|
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. | | |
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. | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
8 posts
views
Thread by Rob Martin |
last post: by
|
25 posts
views
Thread by Magnus Lie Hetland |
last post: by
|
6 posts
views
Thread by Paul Welter |
last post: by
|
3 posts
views
Thread by Lerp |
last post: by
|
2 posts
views
Thread by Benny Ng |
last post: by
|
3 posts
views
Thread by nz87f4 |
last post: by
|
reply
views
Thread by luc.saffre@gmail.com |
last post: by
|
7 posts
views
Thread by David T. Ashley |
last post: by
|
12 posts
views
Thread by Summercoolness@gmail.com |
last post: by
| | | | | | | | | | |