Join Excel tables across directories

by Nov 2, 2020

Is it possible to join two excel tables from workbooks in separate file locations together? For example, this is possible:

Join these two tables.

C:\LocationA\workbook1, [workbook1].[Sheet1]

to

C:\LocationA\workbook2, [workbook2].[Sheet1]

But I can’t seem to make this join work:

C:\LocationA\workbook1, [workbook1].[Sheet1]

to

C:\LocationB\workbook2, [workbook2].[Sheet1]

Even if I add both directories to the Location section of the server properties. Thank you for your time!

Response

Thomas Conrad over 2 years ago
Hello,

Unfortunately, there is a limitation joining tables in different databases. As you probably already know… “Database” refers to the directory where the Excel file resides. “Schema” refers to the Excel file(Workbook). “Tables” are the worksheets in the Excel file. So you can join across schemas but not databases like:

SELECT b.”Name”, a.”Age” FROM “Olympics”.”OlympicAthletes” a
LEFT JOIN “Contestants”.”Sheet1″ b WHERE a.”Athlete” = b.”Name”
GO
Hope that helps,

Tom