473,714 Members | 2,152 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Standard Method for Importing/Exporting to DB

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.

May 20 '06 #1
2 3181
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.


May 20 '06 #2

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.


Jun 6 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
4999
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!
25
3790
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,...
6
17171
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(); }
3
1352
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:
2
1201
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 )" ..... ........
3
1952
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.
0
1930
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
7
2081
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?)
12
1502
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...
0
8713
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,...
0
9187
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9080
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9033
tracyyun
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...
0
7960
agi2029
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...
1
6638
isladogs
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...
0
5961
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();...
0
4467
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...
0
4730
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.