topic Re: Load Data from Excel Binary File - ODBC in QlikView

Load Data from Excel Binary File - ODBC

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Load Data from Excel Binary File - ODBC

PrashantSangle — Fri, 17 Aug 2018 12:56:13 GMT

.xlsb file was not supported by qlik.

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

Regards,

Re: Load Data from Excel Binary File - ODBC

Anonymous — Fri, 17 Aug 2018 12:58:29 GMT

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

Re: Load Data from Excel Binary File - ODBC

breno_morais — Fri, 17 Aug 2018 13:04:32 GMT

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

PrashantSangle — Fri, 17 Aug 2018 13:07:07 GMT

Yes through ODBC / OLEDB it work.

see below thread

How to get data from xlsb to QlikView

Regards,

Re: Load Data from Excel Binary File - ODBC

Anonymous — Fri, 17 Aug 2018 13:14:11 GMT

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!

Re: Load Data from Excel Binary File - ODBC

marcus_sommer — Fri, 17 Aug 2018 14:18:06 GMT

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$`;

...

- Marcus

Re: Load Data from Excel Binary File - ODBC

Anonymous — Mon, 20 Aug 2018 11:49:52 GMT

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.

Re: Load Data from Excel Binary File - ODBC

marcus_sommer — Mon, 20 Aug 2018 11:53:58 GMT

And this one?

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

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

Re: Load Data from Excel Binary File - ODBC

Anonymous — Mon, 20 Aug 2018 11:56:42 GMT

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?

Re: Load Data from Excel Binary File - ODBC

Anonymous — Mon, 20 Aug 2018 12:15:40 GMT

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