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

[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 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/

[Rich P]

Hi James,

I think the ETL expression is kind of a catch-all phrase where the ETL
work I have done was with data warehousing. Do you have any specific
tasks at hand that needs a solution?

As far as using data from Excel in a relational system like Access or
Sql Server, the difficulty of this depends on your professional
background. I say the following respectfully - if you are a database
programmer there are a plethora of tools at your disposal - VBA with com
ADO, automation with VBA, ADO.Net with VB.Net, and a wealth of sample
projects on the Net.

On the other hand, if the core of your professional background is not
focused on database programming and coding, then your tasks may be a
little more challenging.

The assistance I will offer is on how to narrow down the scope of your
question(s) and what you need. For example:

Say you have an Excel file with a few sheets of data and you need to
transfer that data to Access so that you can run queries against this
data. The easiest way to do this - if it is not a repetitive task is to
select the data with your mouse, copy it to the clipboard, and just
paste in the Access database window (in the table tab). This will
automatically paste what you selected from Excel and give it a table
name like Sheet1.

But say with the example above you have hundreds of excel files each
with dozens of sheets - the copy and paste method will get old real
fast. Now you will have to write some code to let the computer do the
work. If you are not a coder this could be challenging.

I have no idea what you need specifically, so rahter than waste my time
with an example that may have nothing to do with what you need, I will
ask if you have any specific tasks at hand that you need a solution for.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[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
knoew of a desktop application useable as an ETL tool for performng
the transofrmations.

James Igoe

http://code.comparative-advantage.com/
On Mar 5, 3:07 pm, "[e-mail]" <[e-mail]>
wrote:

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 fairly robustETLprocedures, 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 polishedETLproducts 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/

[Rich P]

I haven't seen any commercially available products or heard of any
vendors producing any products to perform ETL operations at the
Microsoft Office level. If you need to perform some custom ETL
operations between Access and Excel, I think you are stuck with writing
your own procedures.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Vassil Kovatchev]

On Mar 6, 6:49 pm, Rich P <[e-mail]:

I haven't seen any commercially available products or heard of any
vendors producing any products to perform ETL operations at the
Microsoft Office level. If you need to perform some custom ETL
operations between Access and Excel, I think you are stuck with writing
your own procedures.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

I posted this on another forum too, but I though it would make sense
to post it here 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

[Susie DBA [MSFT]]

ETL is a catch-all... because there are ENTERPRISE LEVEL TOOLS

use SQL Server, DTS or SSIS

Access MDB is for groupies and friggin losers


On Mar 5, 2:56 pm, Rich P <[e-mail]:

Hi James,

I think the ETL expression is kind of a catch-all phrase where the ETL
work I have done was with data warehousing. Do you have any specific
tasks at hand that needs a solution?

As far as using data from Excel in a relational system like Access or
Sql Server, the difficulty of this depends on your professional
background. I say the following respectfully - if you are a database
programmer there are a plethora of tools at your disposal - VBA with com
ADO, automation with VBA, ADO.Net with VB.Net, and a wealth of sample
projects on the Net.

On the other hand, if the core of your professional background is not
focused on database programming and coding, then your tasks may be a
little more challenging.

The assistance I will offer is on how to narrow down the scope of your
question(s) and what you need. For example:

Say you have an Excel file with a few sheets of data and you need to
transfer that data to Access so that you can run queries against this
data. The easiest way to do this - if it is not a repetitive task is to
select the data with your mouse, copy it to the clipboard, and just
paste in the Access database window (in the table tab). This will
automatically paste what you selected from Excel and give it a table
name like Sheet1.

But say with the example above you have hundreds of excel files each
with dozens of sheets - the copy and paste method will get old real
fast. Now you will have to write some code to let the computer do the
work. If you are not a coder this could be challenging.

I have no idea what you need specifically, so rahter than waste my time
with an example that may have nothing to do with what you need, I will
ask if you have any specific tasks at hand that you need a solution for.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Jack]

http://457a9b9c07fd2e896beca5561c065b74-t.qhzrpz.info <a
href="http://457a9b9c07fd2e896beca5561c065b74-h.qhzrpz.info">457a9b9c07fd2e896beca5561c065b74</a>
http://457a9b9c07fd2e896beca5561c065b74-b1.qhzrpz.info
457a9b9c07fd2e896beca5561c065b74
http://457a9b9c07fd2e896beca5561c065b74-b3.qhzrpz.info
a587e23529cc2ef5e6e7833effd186f2