How to import data from mulitple worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
By running sequential TransferSpreadsheet actions... each one used to import
a single worksheet.
 
Each named range in Excel is a separate table.
You may be able to open a union query against
several 'tables' at the same time: you may be
able to join several 'tables' together (or you
may not...)

Normally when you import, you have a choice of
creating a new table, or appending to an existing
table. Perhaps you need to look at your import
method, and see if you can change it to do append
instead of create.

(david)
 
david said:
Each named range in Excel is a separate table.
Click to expand...

Either an Excel TABLE or a SYSTEM_TABLE may be used in a query using
the ODBC driver (the same applies to the OLE DB providers, accept they
cannot differentiate between TABLE or SYSTEM_TABLE).

There is only one definition for TABLE, being a workbook-level defined
Name ('named range'), defined using a simple formula to return the
range e.g. =Sheet1!A1:B4. I assume the driver/provider reads the
address used in the formula (but at a low level). Cells are not
calculated when data is accessed using Jet e.g. you can change a
precedent but the formula cell's value will not reflect the change
until it is opened and calculated in the Excel UI (also, a formula cell
cannot be changed using Jet). Therefore, a 'dynamic range' which relies
on the result of a formula will not be seen as a TABLE.

There are three definitions for SYSTEM_TABLE

1) a worksheet-level defined Name, again defined using a simple formula
to return the range e.g.

SELECT * FROM [MySheet$MyDefinedName];

2) a worksheet e.g.

SELECT * FROM [MySheet$];

The UsedRange (at a lower level than VBA) determines the table bounds.

3) an absolute range address, either with an explicit sheet name e.g.

SELECT * FROM [MySheet$A1:B4];

or with the sheet name omitted e.g.

SELECT * FROM [A:B];

in which case the worksheet at position Worksheets(1) will be used
regardless of visibility.
You may be able to open a union query against
several 'tables' at the same time: you may be
able to join several 'tables' together (or you
may not...)
Click to expand...

Here's one I made earlier:

INSERT INTO MyTable
(key_col, data_col)
SELECT DT1.key_col, DT1.data_col
FROM (
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkb­ook.xls;].[Sheet1$]
UNION
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkb­ook.xls;].[Sheet2$]
UNION
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkb­ook.xls;].[Sheet3$]
) AS DT1;

Jamie.

--
 
Back
Top