how to count no.of sheets in execl file

[rohit214]

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

[#2]

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));

  NEXT

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

Regards,

Sajeevan

[SunilChauhan · Accepted solution]

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;

[#4]

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)]);

NEXT

DROP Table tables;