Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to load a table where it only loads the rows from a .xls file that has a column that matches another column in a different .mdb table. Normally I feel I could easily do this with an SQL WHERE EXISTS statement. But I am running into the error "External table is not in the expected format". I suspect an issue with loading the data with ODBC driver.
What is my problem here?
In order for my code to work the field name would have to match exactly.
So you would need
AccessDatabaseTable:
Load
PlayerID as PlayID
;
SQL SELECT from 'YourDBTable';
ExcelFileTable:
Load
PlayID
From YourExcelFile.xlsx
where exists(PlayID);
Hi Beck,
Thanks for replying. Both the excel file and the access database are not password protected. The error message is also in reference to the ODBC Excel Driver. I know you do not need a driver to load .xls files but I did so I could edit the load in SQL.
Can you use OLEDB instead?
Or post your script.
Is there an OLEDB driver for Excel? Will that solve the problem? I know for Access there is.
LOAD ...
SQL SELECT *
FROM ... Player
WHERE EXISTS (SELECT Var1 FROM ... Place WHERE Player.PlayerID = Place.PlayerID);
Place is a .mdb table and Player is a .xls file.
This results in the error listed above "External table is not in the expected format".
You shouldn't need ADBC or OLEDB for Excel.
Your script will need to look more like.
AccessDatabaseTable:
Load
PlayerID
;
SQL SELECT from 'YourDBTable';
ExcelFileTable:
Load
PlayerID
From YourExcelFile.xlsx
where exists(PlayerID);
The problem is then that I only want to pull the rows from the excel file that have an existing PlayerID in the Access Database Table. Which is why I figured I need the WHERE EXISTS SQL clause so as to reference the other table in a subquery.
That's what the bolded part does.
AccessDatabaseTable:
Load
PlayerID
;
SQL SELECT from 'YourDBTable';
ExcelFileTable:
Load
PlayerID
From YourExcelFile.xlsx
where exists(PlayerID);
I apologize that is not working so I must not be explaining things incorrectly.
AccessDatabaseTable:
Load
PlayerID
;
SQL SELECT from 'YourDBTable';
ExcelFileTable:
Load
PlayID
From YourExcelFile.xlsx
where exists(PlayerID);
That variable is different. In the access database there are multiple instances of those ID's but the range is smaller (ie 1-20 but multiple for each), whereas the excel file (PlayID) is a unique key with a larger range (1-75 but I only want the information for 1-20) .
In order for my code to work the field name would have to match exactly.
So you would need
AccessDatabaseTable:
Load
PlayerID as PlayID
;
SQL SELECT from 'YourDBTable';
ExcelFileTable:
Load
PlayID
From YourExcelFile.xlsx
where exists(PlayID);