topic How to load multiple excel binary files (.xlsb) into qlikview in QlikView

How to load multiple excel binary files (.xlsb) into qlikview

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am stuck with loading multiple .xlsb files in qlikview. I am trying the ODBC connection. I was able to upload the 3 heavy files (.xlsb files, approx ~ 750000 rows each which took good 7-8 mins for each files) in first attempt, now i am not able to load any data from the file.

Not even from the earlier loaded script which worked at first attempt!

Though, the connection string says its connected, am getting the following error:

Also a strange behavior from Qlikview , everytime i click the Connect databse button it popoluates the old connection string on the script. How can i make qlikview forget the old connection and create new ODBC connection?

Quick help 'll be highly appreciated.

Thanks,

Ashar

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Mon, 17 Apr 2017 06:35:55 GMT

If all the files has same data structure then you could simply use the table load rite ?

like \\Path\*.xls

Re: How to load multiple excel binary files (.xlsb) into qlikview

Mon, 17 Apr 2017 06:43:28 GMT

Hi Avinash,

The files are excel binary files (.xlsb), not the plain excel preadsheet (.xls). We cant use table load for the .xlsb files.

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Mon, 17 Apr 2017 07:10:04 GMT

Check this

Loading XLSB file format into Qlikview | Qlik Community

How to get data from xlsb to QlikView

Re: How to load multiple excel binary files (.xlsb) into qlikview

Mon, 17 Apr 2017 07:19:04 GMT

thanks , i had been through the link earlier.

I had tried the same method, My connection get established as seen in first screenshot above. I am getting the script error while running the SQL Select (Did not recognise connector reply).

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Mon, 17 Apr 2017 07:32:18 GMT

Try like this delete your connection script from the DSN and recreate a new one ...Share the complete script your using . One more thing you have installed all the necessary drivers rite ?

Re: How to load multiple excel binary files (.xlsb) into qlikview

Mon, 17 Apr 2017 07:36:49 GMT

'delete your connection script from the DSN and recreate a new one' , how to do that?

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Mon, 17 Apr 2017 07:39:50 GMT

Go to Edit script>Tools> USER DSN > check the connection and remove

Re: How to load multiple excel binary files (.xlsb) into qlikview

Mon, 17 Apr 2017 07:40:21 GMT

This is how my ODBC Data Source admin looks now.

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Mon, 17 Apr 2017 07:46:19 GMT

Create a new ODBC in USER DSN and System DSN and then use the same

Re: How to load multiple excel binary files (.xlsb) into qlikview

Mon, 17 Apr 2017 09:16:11 GMT

I created the new User DSN and System DSN named ODBC_Excel_driver (which looked exactly same like the existing driver names 'Excel Files' as below and got the same error: connected to the .xlsb file but while running the SQL Select, "Did not recognise connector reply".

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Mon, 17 Apr 2017 09:20:32 GMT

Could you share the sample data set file with less data , let me check and get back to you

Re: How to load multiple excel binary files (.xlsb) into qlikview

Mon, 17 Apr 2017 09:29:21 GMT

Appreciate your ask, unfortunately cant share the data as its kinda classified vendor /third party stuffs.You may try with any small excel binary data (excel data .xls or .xlsx saved as .xlsb)

Re: How to load multiple excel binary files (.xlsb) into qlikview

Tue, 18 Apr 2017 05:33:54 GMT

Script Error: Did not recognise connector reply SQL SELECT * From ********

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Tue, 18 Apr 2017 07:13:52 GMT

I feel their is a issue with the script statement .....Do you have special character in the file name/sheet names ?

could you share the complete script ?

Re: How to load multiple excel binary files (.xlsb) into qlikview

Tue, 18 Apr 2017 08:22:58 GMT

I dont have any special character in the file name/sheet.

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\XlSb test1.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\XlSb test1.xlsb`.`Sheet1$`;

CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\2014 Q1 CC.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\2014 Q1 CC.xlsb`.`2014 Q1 Data$`;

As we can see the first query run successfully, fetches the 11 records from the dummy file and the later one fails.

File type is same for both (.xlsb), its just the number of records in first file is 11 only and in later one is ~7550000

Re: How to load multiple excel binary files (.xlsb) into qlikview

Tue, 18 Apr 2017 08:23:31 GMT

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Tue, 18 Apr 2017 09:56:08 GMT

I suspect their might space issue ? because 1 connect statement is working fine

try to add a dis connect statement after the sQL

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\XlSb test1.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\XlSb test1.xlsb`.`Sheet1$`;

DISCONNECT;

First 1000

CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\2014 Q1 CC.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\2014 Q1 CC.xlsb`.`2014 Q1 Data$`;

if this loads then its the issue with Space because it not able to fetch ...

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\XlSb test1.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\XlSb test1.xlsb`.`Sheet1$`;

DISCONNECT;

CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\2014 Q1 CC.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\2014 Q1 CC.xlsb`.`2014 Q1 Data$`;

DISCONNECT;

Re: How to load multiple excel binary files (.xlsb) into qlikview

Tue, 18 Apr 2017 11:07:03 GMT

no luck! i had tried the disconnect approach earlier as well. same error!

How to select First 1000 here?

Re: How to load multiple excel binary files (.xlsb) into qlikview

avinashelite — Tue, 18 Apr 2017 13:46:05 GMT

I have given the script for that

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\XlSb test1.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\XlSb test1.xlsb`.`Sheet1$`;

DISCONNECT;

First 1000

CONNECT32 TO [Excel Files;DBQ=C:\Users\44025515\Desktop\2014 Q1 CC.xlsb];
SQL SELECT *
FROM `C:\Users\44025515\Desktop\2014 Q1 CC.xlsb`.`2014 Q1 Data$`;