Loop Through A List for Load

Mon, 28 May 2012 08:56:39 GMT

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

2011_01

2011_02

2011_03

.......

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

Loop Through A List for Load

Mon, 28 May 2012 15:14:17 GMT

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

topic Loop Through A List for Load in QlikView

Loop Through A List for Load

Loop Through A List for Load