Loading Data

[#1]

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?

[beck_bakytbek]

Hi Josept,

look at this:https://www.youtube.com/watch?v=9RyG09H-lzA

i hope that helps

Beck

[#3]

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.

[Anonymous]

Can you use OLEDB instead?

Or post your script.

[#5]

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".

[Anonymous]

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);

[#7]

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.

[Anonymous]

That's what the bolded part does.

AccessDatabaseTable:

Load

PlayerID

;

SQL SELECT from 'YourDBTable';

ExcelFileTable:

Load

PlayerID

From YourExcelFile.xlsx

where exists(PlayerID);

[#9]

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) .

[Anonymous · Accepted solution]

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);