Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please refer the attached attendance log file.
In this file I have numbers like (1,2,3,..... up to 31) as Dates and Sheet name is Jan - 17.
Now I have to convert those numbers into date(DD-MM-YYYY). I did Cross table to get all those numbers into a single date field.
The only way to read the sheet-names would be to load the excels per odbc, see: Loading Multiple Excel Sheets Dynamically along with file name and sheet name. There you add the sheet-name as a field and after a transforming of your data into "normal" data-structure per The Crosstable Load you could create a real date from it, probably per:
date(date#(Sheetname & '|' & Day, 'MMM - YY|DD')) as Date
It won't be very easy for you as starter but if you split the various parts - at first loading it without for-loop and without querying the sheetnames with a direct odbc-connect and quite similar the crosstable load to the excel as flatfile until it worked and you understand it - and then you could merge the different parts and add step by step more complexity.
- Marcus
think the other way,wht's the probability load of ur application?
for example if u r running monthly -- u can simple take all the dates from the current month or the month & year from current month and append ur day from ur excel.