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

[#1]

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

[#2]

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

[avinashelite]

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

[#4]

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)

[#5]

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

[avinashelite]

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 ?

[#7]

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

[#8]

[avinashelite]

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

or

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;

[#10]

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

How to select First 1000 here?

[avinashelite]

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