topic Re: how to count no.of sheets in execl file in QlikView

how to count no.of sheets in execl file

rohit214 — Tue, 15 Nov 2011 07:15:49 GMT

hi all

i have execl fine named A..in A there are n no of sheets.

so i want to count that sheets.

is there any function to count no of sheets in execl file data?

regards

rohit

how to count no.of sheets in execl file

Tue, 15 Nov 2011 07:26:27 GMT

Hi Rohit,

An ODBC connection to the excel file can give you the number of sheets. It should look like something below.

ODBC CONNECT TO [Excel Files;DBQ=c:\temp.xls];
tables:
SQLTables;
DISCONNECT;

NoOfRows('tables') will give you the number of sheets.

Regards,

Sajeevan

how to count no.of sheets in execl file

rohit214 — Tue, 15 Nov 2011 07:45:31 GMT

hi Saajeen

thanks for your reply ...

i am not getting u properly will you explain in detail.

in fact i want to load all sheets at a time ,

thanks rohit

how to count no.of sheets in execl file

SunilChauhan — Tue, 15 Nov 2011 07:58:08 GMT

ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\sunil\test.xlsx];

tables:

SQLTables;

change here path of excel it wiill load all sheets from xls.

how to count no.of sheets in execl file

Tue, 15 Nov 2011 08:29:51 GMT

Hi Rohit,

Let us assume that you have an excel file called "c:\temp\abc.xls" which contains multiple sheets. Try the below to load data from all sheets in one go.

ODBC CONNECT TO [Excel Files;DBQ=c:\temp\abc.xls];

tables:

SQLTables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

Data:

LOAD *

FROM c:\temp\abc.xls (biff, embedded labels, table is $(sheetName)) where right('$(sheetName)',1) = '$';

DROP Table tables;

Let me know whether this works.

Regards,

Sajeevan

Re: how to count no.of sheets in execl file

SunilChauhan — Tue, 15 Nov 2011 08:41:09 GMT

please use below code

ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\sunil\test.xls];

tables:

SQLTables;
let var=NoOfRows('tables');
FOR i = 0 to $(var)-1;

next i

see the attached files also

Re: how to count no.of sheets in execl file

rohit214 — Tue, 15 Nov 2011 08:48:44 GMT

sanjeev

its is not working,here i am attached my exel file

plz find the attachment

Re: how to count no.of sheets in execl file

rohit214 — Tue, 15 Nov 2011 09:00:05 GMT

hi sunil

in my execl file fiels are diffrent and i want to reload all sheets data at a time..i.e. all sheets

thanks

rohit

Re: how to count no.of sheets in execl file

Tue, 15 Nov 2011 09:05:26 GMT

Hi Rohit,

The code I have given you is for ".xls" format not for ".xlsx" file. I will try with the file you sent and let you know. For the time being you can open your ".xlsx" file and save as ".xls" file then my code should work.

".xls" files are BIFF format but ".xlsx" file are XML format hence need to rework on the code. I don't have office2007.

Thanks,

Sajeevan

Re: how to count no.of sheets in execl file

rohit214 — Tue, 15 Nov 2011 10:15:14 GMT

hu sajeevar

very very thank you for helping me..

thanks rohit

Re: how to count no.of sheets in execl file

Tue, 15 Nov 2011 10:53:24 GMT

Hi Rohit,

Probably you can do the below and load all sheets.

First, create a table with all possible table names like below

tables:

LOAD * INLINE [

TABLE_NAME

Sheet1

Sheet2

Sheet3

Sheet4

Sheet5

Sheet6

Sheet7

Sheet8

Sheet9

];

Second, add the code below which doesn't show an error when a sheet is not present.

set Errormode = 0;

Third, load the sheets using the code below.

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

Data:

LOAD *

FROM C:\Temp\Security.xlsx (ooxml, embedded labels, table is $(sheetName));

You can use this till the time we find ways of resolving "xlsx" file format.

Regards,

Sajeevan

Re: how to count no.of sheets in execl file

SunilChauhan — Tue, 15 Nov 2011 11:23:51 GMT

please try this code

use your xls path

ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\Security.xlsx];

tables:

SQLTables;

DISCONNECT;
let var=NoOfRows('tables');

FOR i = 0 to $(var)-1
let sheetName=subfield(peek('TABLE_NAME', i,'tables'),'$',1);



$(sheetName):

LOAD *
FROM

(ooxml, embedded labels, table is '$(sheetName)')

;
NEXT i

DROP Table tables;

Re: how to count no.of sheets in execl file

Fri, 11 Jul 2014 13:57:06 GMT

By covering sheetName with [], you code is working..

thanks

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Desktop\0fn38wmkbm19j.xls];

tables:

SQLTables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
Data:

LOAD *
FROM C:\Users\Desktop\0fn38wmkbm19j.xls (biff, embedded labels, table is [$(sheetName)]);

DROP Table tables;