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
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;
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
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
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.
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
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
hi
sanjeev
its is not working,here i am attached my exel file
plz find the attachment
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
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
hu sajeevar
very very thank you for helping me..
thanks rohit