Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.
The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2
while Doc2 has Sheet1/Sheet2/Sheet3 etc.
I need to create a table of Doc name and Sheets belonging to that Doc.
Then I want to load the sheets from their associated docs in a loop statement.
Anyone any ideas?
Thanks
Thanks for this Sir.
Cud u plz tell wat to do if the excel sheets are crosstables....where & how to use the crosstable prefix here.
Regards
Can you attach one of your excel file?
Sir, i'm attaching the an excel file having 3 sheets for 3 years 2009,2010,2011. As per the current problem we can also have these sheets as 3 diffrnt excel sheets in a single folder.
Regards
Hi,
You can use the below syntax to load all the files from a folder (e.g. "C:\Temp") and all the sheets with each file and each sheet is assumed as a crosstable structure.
for each file in FileList('C:\Temp\*.xlsx')
ODBC CONNECT TO [Excel Files;DBQ=$(file)];
tables:
SQLTABLES;
DISCONNECT;
for i = 0 to NoOfRows('tables')-1
let sheetname = purgeChar(peek('TABLE_NAME', i, 'tables'), '$');
data:
CrossTable(Month, Quantity)
LOAD Salesman,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM '$(file)' (ooxml, embedded labels, table is $(sheetname));
next i
drop table tables;
next file
Regards,
Sajeevan
thanks for d reply......will try this & get back to u if i've any issues
Dear Sir,
This worked perfectly fine, but it was actually a dummy file created by me.
The actual excel file has two sheets (2011 & 2012). 2011 is fully filled with 12
mnths data while 2012 has data only for Jan.
The sheets are in crosstable format.
when i use ur script the first sheet loads fine but stucks on the 2nd sheet saying :-
Unknown file format specifier:table is '2012'_xlnm#_FilterDatabase.
Plz help
Can you send the excel file where you are getting this error for me to have a look at it?
Thanks for the reply Sir,
Actually i'm unable to share the actual data with you due to fidelity issues.
I'll try to build a dummy file again.
But where do you think the problem could be..any areas which i can rectify
Regards
There could be some issue with the file structure. Please open the sheet and check whether the columns are in order or not.
Dear Sir,
I just copied all the data from the excel file & created a brand new excel file sheet & now its working fine.
Now my next issue is how can i link the year number(2011 & 2012) to the excel sheet names & create a list box for selecting the year. Do i've to add a column for year in the sheet as well?
Regards