Loading XLSB file format into Qlikview

[tamilarasu]

                We have multiple xlsb format files and I want to load all of them in a single table. I have created ODBC connection but I can not find similar thread on how to load multiple files in single table.

Kaushik.solanki‌  I just see your threads regarding the xlsb format issue. Do you have any idea about this.?

Thank you in advance.

[kaushiknsolanki]

Hi,

Its not possible to load all the xlsb files in one go.

Regards,

Kaushik Solanki

[tamilarasu]

That's sad. Also I am facing an issue while connecting the xlsb by using the ODBC method. I have created a connection string yesterday and it was working fine. When I opened the same yesterday eveing, it showing some error. Again the problem occurs today morning. I just created the connection again and it's working fine. Not sure, whether I need to create a connection every time.

Anyways, Thank you for the reply.

[kaushiknsolanki]

Hi,

Try OLEDB option and see if it works in your case.

Regards,

Kaushik Solanki

[jagan]

Hi Tamil,

You can try something like below, this is for excel you can try the same for XLSB file using ODBC and for loop.

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
NEXT i
Next

Regards,

Jagan.

[tamilarasu]

Kaushik,

I have tried the same by using the "Microsoft OLE DB Provider for ODBC Drivers" but the test connection failed. See below screenshot.

It would be really grateful, If you could guide me.!!

[kaushiknsolanki]

HI,

You can give a try as Jagan has said.

For OLEBD it seems that the driver is corrupted, Try reinstalling the driver.

Regards,

Kaushik Solanki

[tamilarasu]

Jagan,

I have modified the code but I am not sure what could be the format for xlsb. I know that

Biff - for extension xls

Ooxml - for extension xlsx.

Could you guide me.

[jonathandienst]

It is neither format - xlsb is an Excel binary file. You can only open this via ODBC (and possibly OLEDB). You cannot load it using a normal LOAD * FROM myFile.xlsx.

[tamilarasu]

Jonathan,

It seems, I can not loop through the multiple files.!!