Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rohit214
Contributor III

how to count no.of sheets in execl file

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

1 Solution

Accepted Solutions
SunilChauhan
Esteemed Contributor

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

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;

12 Replies
Not applicable

how to count no.of sheets in execl file

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

rohit214
Contributor III

how to count no.of sheets in execl file

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

SunilChauhan
Esteemed Contributor

how to count no.of sheets in execl file

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.

Not applicable

how to count no.of sheets in execl file

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) = '$';

  NEXT

  DROP Table tables;

Let me know whether this works.

Regards,

Sajeevan

SunilChauhan
Esteemed Contributor

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

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

rohit214
Contributor III

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

hi

sanjeev

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

plz find the attachment

rohit214
Contributor III

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

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

Not applicable

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

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

rohit214
Contributor III

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

hu sajeevar

very very thank you for helping me..

thanks rohit