Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hallo All,
I have few Excel Sheets with multiple files with Month_Year as the Sheet Name.
How do i load all the sheets once and also save the Sheet Name as Date.
Ex: Data.xlsx has the Following Sheets with some data
Jan 12, Feb 12.....Dec 13
I want to load all the sheets and save the Sheet Name as Jan 2012, Feb 2012...Dec 2013.
Found few links in the community but somehow was not successful. Any pointers would be appraciated.
TIA!!
What are these? Jan 12 and Feb 12 ...??
This technique will load all the sheets from the matching files:
For Each vFileName In FileList(....)
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFileName)];
SpreadsheetData:
SQLTABLES;
DISCONNECT;
For i = 0 to NoOfRows('SpreadsheetData') - 1
Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');
LOAD .... FROM $(vFileName)
(biff, table is [$(zSheet)]);
Next
Next
Fill in the missing details. Hopefully that gets you going.
To include the date modified as requested, use this code
For i = 0 to NoOfRows('SpreadsheetData') - 1
Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');
Let zDate = Num(Date#(zSheet, 'MMM yy'));
LOAD ...,
Date($(zDate, 'MMM yyyyy'),
....
FROM $(vFileName)
(biff, table is [$(zSheet)]);
Next
These are the Sheet Names
Hi Jonathan,
Thanks for the suggestion. I'm able to connect to the Excel folder and the script also runs successfully but, i don't see any fields after the reload.
Also, Attaching a sample excel.
I suggest you post the script you used. What I supplied was a script pattern to help you get to the correct script.You need to adapt the pattern to your specific environment and requirements.