Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
jan_heini
Contributor II
Contributor II

Load Data from Excel Binary File - ODBC

Hi

Am trying to load data from an Excel Binary File .xlsb

I used the following code

ODBC CONNECT32 TO [Excel Files;DBQ=C:\temp\Filename.xlsb];

SQL Select *

FROM ;

When I run the script I get the following error message:

ODBC error

Connector reply error: SQL##f - SqlState: S0002, ErrorCode: 4294965991, ErrorMsg: [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'C:\temp\Filename.xlsb'. Make sure the object exists and that you spell its name and the path name correctly. If 'C:\temp\Filemane.xlsb' is not a local object, check your network connection or contact the server administrator.

Is there something wrong with my script? The file is saved locally.

Regards,

Jan

1 Solution

Accepted Solutions
jan_heini
Contributor II
Contributor II
Author

Hi

Thank you for your help!

I found the error:

ODBC CONNECT32 TO [Excel Files;DBQ=C:\temp\Test.xlsb];

SELECT * FROM `C:\temp\Test.xlsb`.`Tabelle1$`;   

Need to add a "$"

View solution in original post

10 Replies
PrashantSangle

.xlsb file was not supported by qlik.

You can use it by converting it into .xlsx or .xls or .csv

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
jan_heini
Contributor II
Contributor II
Author

I would prefer not to do that since the live data is store in these files. These is no way to get around this? I read that through an ODBC Connection it should work...

breno_morais
Partner - Contributor III
Partner - Contributor III

Hi Jan!

You need to save in other format supported like Prashant says, I know this link to convert: xlsb to xls | FileZigZag - Free online document file converter

PrashantSangle

Yes through ODBC / OLEDB it work.

see below thread

How to get data from xlsb to QlikView

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
jan_heini
Contributor II
Contributor II
Author

Prashant

That is what I tried. The ODBC connection seems to work.

This part connecting to the xlsb file with ODBC runs without problem:

ODBC CONNECT32 TO [Excel Files;DBQ=C:\temp\Filename.xlsb];

This part loading the data seems to be the problem:

SQL Select *

FROM ;

Do you know what I did wrong?

Thank you for your help!

marcus_sommer
MVP & Luminary
MVP & Luminary

I think you missed to specify the table respectively sheet which should be loaded. Here an example-snippet which runs successfully in my environment:

...

for each file in filelist('Path ...\$(vRevisor)\$(vPeriod)??_????.xlsb');

            ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

            RevisionsprotokollODBC:

            Load *;

            SELECT * FROM `$(file)`.`RevisionsProtokoll$`;

next   

...

- Marcus

jan_heini
Contributor II
Contributor II
Author

Thank you Marcus

I tried the following:

ODBC CONNECT32 TO [Excel Files;DBQ=C:\temp\Test.xlsb];

SELECT * FROM `Test.xlb`.`Tabelle1`;    

It gave me the error:

ODBC error

Connector reply error: SQL##f - SqlState: 42000, ErrorCode: 4294965487, ErrorMsg: [Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

marcus_sommer
MVP & Luminary
MVP & Luminary

And this one?

ODBC CONNECT32 TO [Excel Files;DBQ=C:\temp\Test.xlsb];

SELECT * FROM `C:\temp\Test.xlsb`.`Tabelle1`;    

jan_heini
Contributor II
Contributor II
Author

It gives me:

ODBC error

Connector reply error: SQL##f - SqlState: S0002, ErrorCode: 4294965991, ErrorMsg: [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Tabelle1'. Make sure the object exists and that you spell its name and the path name correctly. If 'Tabelle1' is not a local object, check your network connection or contact the server administrator.

Table is the Tab in the Excel sheet. Or do I need to give the column name here?