Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
jan_heini
New 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
New Contributor II

Re: Load Data from Excel Binary File - ODBC

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

Re: Load Data from Excel Binary File - ODBC

.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
New Contributor II

Re: Load Data from Excel Binary File - ODBC

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

Partner
Partner

Re: Load Data from Excel Binary File - ODBC

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

Re: Load Data from Excel Binary File - ODBC

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
New Contributor II

Re: Load Data from Excel Binary File - ODBC

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!

MVP & Luminary
MVP & Luminary

Re: Load Data from Excel Binary File - ODBC

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
New Contributor II

Re: Load Data from Excel Binary File - ODBC

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.

MVP & Luminary
MVP & Luminary

Re: Load Data from Excel Binary File - ODBC

And this one?

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

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

jan_heini
New Contributor II

Re: Load Data from Excel Binary File - ODBC

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?