Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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;