Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Loading Data

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?

1 Solution

Accepted Solutions
atkinsow
Valued Contributor II

Re: Loading Data

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

10 Replies
beck110979
Valued Contributor III

Re: Loading Data

Hi Josept,

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

i hope that helps

Beck

Highlighted
Not applicable

Re: Loading Data

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.

atkinsow
Valued Contributor II

Re: Loading Data

Can you use OLEDB instead?

Or post your script.

Not applicable

Re: Loading Data

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

atkinsow
Valued Contributor II

Re: Loading Data

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

Not applicable

Re: Loading Data

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.

atkinsow
Valued Contributor II

Re: Loading Data

That's what the bolded part does.

AccessDatabaseTable:

Load

PlayerID

;

SQL SELECT from 'YourDBTable';

ExcelFileTable:

Load

PlayerID

From YourExcelFile.xlsx

where exists(PlayerID);

Not applicable

Re: Loading Data

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

atkinsow
Valued Contributor II

Re: Loading Data

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