topic Re: Loading XLSB file format into Qlikview in QlikView

Loading XLSB file format into Qlikview

tamilarasu — Tue, 25 Aug 2015 05:47:50 GMT

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.

Re: Loading XLSB file format into Qlikview

kaushiknsolanki — Tue, 25 Aug 2015 06:04:39 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Loading XLSB file format into Qlikview

tamilarasu — Tue, 25 Aug 2015 06:16:45 GMT

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.

Re: Loading XLSB file format into Qlikview

kaushiknsolanki — Tue, 25 Aug 2015 06:17:49 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Loading XLSB file format into Qlikview

jagan — Tue, 25 Aug 2015 06:19:44 GMT

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.

Re: Loading XLSB file format into Qlikview

tamilarasu — Tue, 25 Aug 2015 06:22:04 GMT

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

Re: Loading XLSB file format into Qlikview

kaushiknsolanki — Tue, 25 Aug 2015 06:30:16 GMT

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

Re: Loading XLSB file format into Qlikview

tamilarasu — Tue, 25 Aug 2015 06:50:29 GMT

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.

Re: Loading XLSB file format into Qlikview

jonathandienst — Tue, 25 Aug 2015 07:01:05 GMT

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.

Re: Loading XLSB file format into Qlikview

tamilarasu — Tue, 25 Aug 2015 07:06:56 GMT

Jonathan,

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

Re: Loading XLSB file format into Qlikview

jagan — Tue, 25 Aug 2015 08:27:20 GMT

Hi Tamil,

I do not have a XLSB file can you attach a sample file.

Regards,

Jagan.

Re: Loading XLSB file format into Qlikview

jagan — Tue, 25 Aug 2015 08:32:39 GMT

Hi,

Can you attach the script you are using to load single xlsb file, I will update the code with loop.

Regards,

Jagan.

Re: Loading XLSB file format into Qlikview

tamilarasu — Tue, 25 Aug 2015 08:50:52 GMT

Jagan,

Attached some sample files.

Code:

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (3)\1.xlsb];

Table:

SQL SELECT *

FROM `C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (3)\1.xlsb`.`'Sheet1$'`;

Need to create a ODBC connection.

Kindly have a look. Thank you.

Re: Loading XLSB file format into Qlikview

jagan — Tue, 25 Aug 2015 08:53:58 GMT

Hi,

I asked you to attach the code for loading single XLSB file, so that I will update the code with the For loop.

Regards,

Jagan.

Re: Loading XLSB file format into Qlikview

tamilarasu — Tue, 25 Aug 2015 09:11:07 GMT

Please check now.

Re: Loading XLSB file format into Qlikview

jagan — Tue, 25 Aug 2015 09:46:09 GMT

Hi,

Try this script

//to read each file from the specified folder

FOR EACH file in FileList('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (3)\*.xlsb');

//In order to get the file information from SQLtables command making use of the ODBC connection format

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

Table:

SELECT *

FROM '$(file)'.'Sheet1$';

Regards,

jagan.

Re: Loading XLSB file format into Qlikview

jagan — Thu, 14 Jan 2016 05:56:07 GMT

HI,

If you got the answer close this thread by giving Correct Answer to the post which helps you in getting answer.

Regards,

jagan.

Re: Loading XLSB file format into Qlikview

tamilarasu — Thu, 14 Jan 2016 06:04:55 GMT

Jagan,

I was just checking my old threads and closing some of them. The solution which you have provided was really helpful but it did not read any xlsb files. I found an alternate solution and implemented the same in my application (Long back).

Re: Loading XLSB file format into Qlikview

jagan — Thu, 14 Jan 2016 06:11:43 GMT

Tell how you solve this and mark your post as Correct Answer, so that it will be really helpful for people who are looking for this type of solution.

Regards,

Jagan.

Re: Loading XLSB file format into Qlikview

tamilarasu — Thu, 14 Jan 2016 06:39:23 GMT

I have lots of qvw files in my system and I don't know the correct one. Anyways, I just did a check of your code and found this is working (And marked your answer as correct too).

For Each vFile in Filelist('$(vFolder)*.xlsb')

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFolder)\vFile.xlsb];

Consolidated:

SQL SELECT *

FROM `$(vFile)`.`Sheet1$`;