Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I can loop through Excel files to load single sheets and through sheets if I have to - (Post's on this community).
My challenge today is along those lines but possibly will not work that nicely.
I have 4 Excel files (so far)
q1.xlsx
q2.xlsx
q3.xlsx
q4.xlsx
Each file as 3 sheet
Q1
2011_01
2011_02
2011_03
.......
Q4
2011_10
2011_11
2011_12
My hope/guess was that I could have a table with file each filename and sheetname and tell QV to loop through that table for it's $vsheet and $vfile
I am obviously happy to have smarter solutions thrown at me.....
Essentially I want to loop through each file pick up the sheets in it.
Thanks,
Rob
Hi, try that,
SET nXls =0;
SET DirBase ='c:\excel';
for each File in filelist ( DirBase&'*.xls')
LET sFile ='$(File)';
LET nXls=nXls+1 ;
Excel_Data:
LOAD @1 as [Field 1],
@2 as [Field 2],
@3 as [Field 3]
From [$(sFile)]
(biff, no labels, header is 4 lines, table is Q1$);
Concatenate(Excel_Data)
LOAD @1 as [Field 1],
@2 as [Field 2],
@3 as [Field 3]
From [$(sFile)]
(biff, no labels, header is 4 lines, table is Q2$);
next i
next File