Toad World® Forums

Linking multiple named range Excel tabs in same workbook


#1

Hi,

I have an Excel workbook which holds a number of spreadsheet tabs (all with the same set of columns) each tab is a daily extract from a third-party systems (I don’t have direct access to query this database).

What I want to be able to do is create a SQL query that can combine all the data from all the tabs and filter out specific information.

I have created a named range for each tabs, so TDA recognizes these as tables.

But I am unsure how to link these tables together. Must I create a select statement for each and use a UNION ALL, or is there another way?

Also I may in time need to link two or more Excel workbooks containing this data, as we may start a new workbook at the beginning of each financial year.

Any help and advise welcome

Thank you in advance

regards

David


#2

Yes. You will need to union the worksheets then treat them as sub select to do the filter. See below. In the TDA Beta the Query Builder has a new Union operator that makes this easier to build.

select * from (SELECT “Set1”.“Col1” AS a
, “Set1”.“Col2” AS b
FROM “C:\Documents and Settings\dpeabody\My Documents\Fruit”.“Set1” “Set1”
UNION
(SELECT “Set2”.“Col1” AS a
, “Set2”.“Col2” AS b
FROM “C:\Documents and Settings\dpeabody\My Documents\Fruit”.“Set2” “Set2”
UNION
SELECT “Set3”.“Col1” AS a
, “Set3”.“Col2” AS b
FROM “C:\Documents and Settings\dpeabody\My Documents\Fruit”.“Set3” “Set3”))
where a > 6;

Debbie