Loading an Excel worksheet...

[The Frog]

Hi Everyone,

Just wondering if anyone knows of a module / add-in that handles ETL
for an Excel spreadsheet into Access. There is one specific
requirement, that values in the source Excel file are 'mapped' to
values in the database. There is a table structure that defines the
values available in the target table, so the 'mappings' need to come
from multiple source tables.

Just a shot in the dark, but it cant hurt to ask.....

Cheers

The Frog

[Tom van Stiphout]

On 10 May 2007 08:07:05 -0700, The Frog
<an**************@eu.effem.comwrote:

What is ETL?
-Tom.

>Hi Everyone,

Just wondering if anyone knows of a module / add-in that handles ETL
for an Excel spreadsheet into Access. There is one specific
requirement, that values in the source Excel file are 'mapped' to
values in the database. There is a table structure that defines the
values available in the target table, so the 'mappings' need to come
from multiple source tables.

Just a shot in the dark, but it cant hurt to ask.....

Cheers

The Frog

[The Frog]

Hi Tom,

ETL is Extraction, Transformation and Loading. It is a term used
usually for database tools and processes. As an example SQL Server
2000's DTS is an ETL type tool.

The Frog

[Tom van Stiphout]

On 11 May 2007 02:25:19 -0700, The Frog
<an**************@eu.effem.comwrote:

The closest you can get to that is Access' import wizard. Import
specifications can be setup and saved for a later day. It's a far cry
from DTS, but so is the price of Access versus SQL Server...
Heck if you REALLY wanted to, Access can be setup as a linked server
for SQL Server; then you CAN use DTS to do your ETL work.

There may be some generic ODBC-based packages to suit your needs.

-Tom.

>Hi Tom,

ETL is Extraction, Transformation and Loading. It is a term used
usually for database tools and processes. As an example SQL Server
2000's DTS is an ETL type tool.

The Frog

[The Frog]

Hi Tom,

Thanks for having a crack at it. Unfortunately the use of SQL server
or a third party tool is out of the question. Totally sux or I would
have done it all in SQL Server. I guess I will just have to write the
VBA and make the forms to do the processing. Bugger of a thing really.
If I can make it flexible enough then I might post it here, I'll see
how it works out. I may have to make it specific to this application
for the sake of time and come back to the 'general case' at a later
stage.

Thanks for your help Tom, it is greatly appreciated.

Cheers

The Frog