ETL (Extract/Transformation/Loading) Tools for Excel to Access

J

james.igoe

I've been hired to produce a reporting database that takes data from
numerous sources (5 financial products, from three regions, each with
multiple tabs) and although I'm confident I can build a fairly robust
ETL procedures, controlling for all the vagaries of Excel, I was
wondering if there were finished products, e.g., purchaseable
software, that would save the time and energy of building from
scratch.

Are there any polished ETL products for converting Excel to Access, or
possibly converting from Excel to a RDMBS backend (Orcle, Sybase, SQL
Server)?

James Igoe
http://code.comparative-advantage.com/
 
J

james.igoe

I'll try again:


I primarily program VBA for Excel/Access for a living, as well as
write stored procedures, and am certainly familair with a range of
languages.

The issue for me :


Do I program the ETL myself, such that I set up procedures and tables
to transform the trading reconciliation data to match a standard
format, or can I purchase an off-the-shelf product to do it for me?


Automating linking, imports, queries, explicit conversion, ADO/DAO,
etc. are fairly simple if tedious. Rather than build from scratch -
if you are familiar with ETL you should be aware that a primary
consideration is whether to build or buy - I was wondering if anyone
knew of a desktop application useable as an ETL tool for performng
the transofrmations.


James Igoe

http://code.comparative-advantage.com/
 
D

dbahooker

dude you can import from Excel into SQL Server using DTS, SQL Server,
OpenQuery, OpenRowSet or OpenSchema right?

Oh, I forgot to include MS Access and SSIS..

yeah.. why would you BUY something to do this?

you should just throw away Excel; use Access Data Projects.. and
export data into Excel

I mean.. just make ADP the center of your universe instead of XLS

and you can digest sprocs without any coding; i mean-- it's wonderful
wonderful tool
 
J

John Nurick

Hi James,

It sounds as if you know as much about the various considerations as
most people. If the source files are laid out in a reasonably regular
way, products such as Monarch Data Pump (from Datawatch) and TextPipe
(DataMystic) are worth looking into.

Otherwise, the more idiosyncratic the layout of the data files, the less
likely it is that a standard product can help - but you knew that
anyway<g>. But don't confine yourself to VBA with its feeble string
functions and antique arrays and collections: a lot of this work is much
simpler in a higher-level language with built-in regular expression
engine and more flexible data types (I use Perl, but Python and Ruby
seem like alternatives; all three have OLE modules so you can work with
Excel documents as well as text files).
 
V

Vassil Kovatchev

I've been hired to produce a reporting database that takes data from
numerous sources (5 financial products, from three regions, each with
multiple tabs) and although I'm confident I can build a fairly robust
ETL procedures, controlling for all the vagaries ofExcel, I was
wondering if there were finished products, e.g., purchaseable
software, that would save the time and energy of building from
scratch.

Are there any polished ETL products for convertingExcelto Access, or
possibly converting fromExcelto a RDMBS backend (Orcle, Sybase, SQL
Server)?

James Igoehttp://code.comparative-advantage.com/
Click to expand...

I sent this directly to James, but I though it would be beneficial for
the rest of the group too.

One of the products, which targets specifically this problem is called
DataDefractor. It is a custom SQL Server 2005 Integration Services
(SSIS) source component designed to extract and normalize data
captured in complex semi-structured Excel and CSV spreadsheets. The
normalized data is pumped directly into the SSIS pipeline for further
processing. The product is example-driven and rule-based - no coding
is required.
A fully functional 14-day evaluation version can be downloaded from
http://www.datadefractor.com.

Best regards,
Vassil
 
Top