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+fullpa th. 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(ident ical 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  3183 
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+fullpa th. 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(ident ical 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+fullpa th. 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(ident ical 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 two
above, or personal/company preference. Want to go the most supported and
commonly accepted method for the future.
Thanks!
|
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.) dicts
mapping nodes to neighbor-sets, but if one wants a graph that's
implemented in some other way, this may not be the most convenient (or
abstract) interface to emulate. It might be nice to have the kind of
polymorphic freedom that one has with,...
|
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 BindingFlag that will only get
the Generic one? Here is an example ...
public class UserTest
{
public ArrayList GetCollection()
{ return new ArrayList(); }
|
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 100 for example in my datagrid:
|
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,
chkRemember.Checked )"
.....
........
| | |
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 so how
would I go about it? If there is a better way or perhaps the only way
please let me know that as well.
Prior to answering this question I have already imported data into an
existing structured table using readxml and update.
|
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 Python standard library unfortunately doesn't provide a module that
gives unique names to common keyboard events.
Even Tkinter had to write a complex parser to find out which keyboard
|
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. But this method
isn't very portable (it requires a knowledge of the machine).
Is there any method supported by the language or by standards? (Maybe,
similar to the way variable-length argument lists are handled?)
|
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 movie name and zipcode, and the website
will return all the theaters showing that movie and at what
time, for theaters in THAT zipcode only (for simplicity).
so how do we just start and use a standard method that
can be simple and very accurate...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
