1 Reply Latest reply: May 28, 2012 11:14 AM by Gregorio Zerpa RSS

    Loop Through A List for Load

      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

        • Loop Through A List for Load

          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