Load Data from Excel Binary File - ODBC

[Anonymous]

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

[PrashantSangle]

.xlsb file was not supported by qlik.

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

Regards,

[Anonymous]

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]

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,

[Anonymous]

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]

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

[Anonymous]

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]

And this one?

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

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

[Anonymous]

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?