Need to create import utility

  • Thread starter Thread starter Ivan
  • Start date Start date
I

Ivan

Hello group!

I need to create import utility. My main concern is how will I support
multiple file types. XLS, CVS, etc? Is there any libraries I can use and
distribute (part of framework?) That will help me do that? Or I have to do
all parsing manually?

Thank you,
Ivan
 
I need to create import utility. My main concern is how will I support
multiple file types. XLS, CVS, etc? Is there any libraries I can use and
distribute (part of framework?) That will help me do that? Or I have to do
all parsing manually?
Click to expand...

There is a very good text parser here:
http://www.codeproject.com/KB/database/GenericParser.aspx. It's not perfect,
but it's a good base. I've extended it to include a DbDataReader and a few
other things I felt it was lacking.
 
There are thrid party controls for working with Excel, but you can also
build an importer for both CSV and Excel with the Microsoft data components
in ADO.NET.

Import from multiple formats is not hard. The hard part is mapping to fields
in your own database. If you have set formats, it is fairly straightforward.
If you have multiple clients with multiple formats, it can get rather
difficult.

For the later scenario, get familiar with at least the factory pattern. It
is also wise to look at Microsoft's provider model and how to set up
"adapters" for your software. The concepts are all similar and basically
come down to treating the UI as a means of pushing the file into a class
that handles the parsing for you.

For mapping fields to internal fields, BizTalk is a nice product. Unless
something has changed in the most recent version, you have to buy an adapter
for Excel from a third party. If this is a shrinkwrap product, a requirement
for BizTalk may throw you out of competition, so consider it carefully.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************
 
Yes, it is indeed "later"
I need to import different formats.

It boils down to
1. Specifying type of file (text, XLS)
2. Setting options about delimiters, etc
3. Reading to dataset/datatable
4. Showing UI and allowing user to map stuff
5. Saving "profile" so steps 1-4 would not have to be repeated.

If control like this or library exist - good deal. Otherwise - factory
pattern and so on..
 
Thanks a bunch. Looks promising.

Is there anything new with Excel object model or it's still interop?

Thank you,
Ivan
 
Yes, it is indeed "later"
I need to import different formats.

It boils down to
1. Specifying type of file (text, XLS)
2. Setting options about delimiters, etc
3. Reading to dataset/datatable
4. Showing UI and allowing user to map stuff
5. Saving "profile" so steps 1-4 would not have to be repeated.

If control like this or library exist - good deal. Otherwise - factory
pattern and so on..
Click to expand...

I wrote a program for my current employer that does almost the same
exact thing to some degree. It uses the Microsoft Text ODBC driver to
read CSV and the Microsoft Excel ODBC driver (both come with MDAC) to
read Excel. Both have some shortcomings, for example, the Excel driver
is not capable of reading Excel 2007 format, but there is an optional
OLEDB driver available from Microsoft that can.

Using the ODBC drivers mean that the actual statement that reads the
data from either format is the same, a standard SQL style SELECT
statement. The beauty of that is that it accepts standard SQL WHERE
clauses which make filtering the input very easy.
 
Back
Top