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. 2 3011
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rob Martin |
last post by:
Greetings
There seems to be two main ways of creating PDF from java - 1. via HTML to
PDF and 2. using an API (e.g. iText).
What are your thoughts - is there an adopted 'standard' method of the...
|
by: Magnus Lie Hetland |
last post by:
Is there any interest in a (hypothetical) standard graph API (with
'graph' meaning a network, consisting of nodes and edges)? Yes, we
have the standard ways of implementing graphs through (e.g.)...
|
by: Paul Welter |
last post by:
I'm trying to get a method using Type.GetMethod. There are two methods with
that same name, one is a standard method, the other is a Generic method.
How do I get the Generic method? Is there a...
|
by: Lerp |
last post by:
Hi all,
I am building an app that has a lot of one to many relationships and was
curious as to the best method in displaying rows of data from both tables on
the same line.
ie:
row1 of...
|
by: Benny Ng |
last post by:
Hi,all,
Is many developers used the intrinsic forms authentication to do the
user authentication module?
many likes this
//.....
"FormsAuthentication.RedirectFromLoginPage( txtUsername.Text,...
|
by: nz87f4 |
last post by:
I recently created an empty table with absolutely no fields/columns
using ADOX. I have an XML file with both data and a scheme that I
would like to import into this table. Is it possible and if...
|
by: luc.saffre |
last post by:
Hello,
I thought that I should ask here for comments on a blog entry that I
wrote some weeks ago. I am sure that other people have been thinking
about this, but I didn't yet find them.
The...
|
by: David T. Ashley |
last post by:
Is there any portable method for identifying the caller of a function, its
caller, and so on, at runtime, without using a debugger?
Clearly, one can dissect the stack frame and trace it back. ...
|
by: Summercoolness |
last post by:
I wonder instead of just brainstorming, there probably is
a very standard and a simple way to do database schema design.
let's say we are doing a website. the user can go over
and type in the...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
|
by: Naresh1 |
last post by:
What is WebLogic Admin Training?
WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
|
by: Oralloy |
last post by:
Hello Folks,
I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA.
My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
|
by: Ricardo de Mila |
last post by:
Dear people, good afternoon...
I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control.
Than I need to discover what...
|
by: ezappsrUS |
last post by:
Hi,
I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
|
by: DizelArs |
last post by:
Hi all)
Faced with a problem, element.click() event doesn't work in Safari browser.
Tried various tricks like emulating touch event through a function:
let clickEvent = new Event('click', {...
| |