7 Replies Latest reply: Feb 25, 2015 10:25 AM by Saradhi Balla RSS

    How to load all files in different folders.

    Neena Bhattarai

      This is my folder structure. I want to load all files in both folders.

      -Distribution:

      • PFP
        • 2014
          • Jan.xlsx
          • Feb.xlsx
          • Mar.xlsx
          • etc...
        • 2015
          • Jan.xlsx
          • Feb.xlsx
          • Mar .xlsx
          • etc..

       

      The script I am currently using is grabbing the 2014 data....

       

      FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philly'
      Tables:
      LOAD Year,
      Facility,
      Month,
      Associate,
      Attendance,
      Defects,
      [In/Out Pool Indicator],
      [Payout - Attend],
      [Payout - Defects],
      Payout
      ,'$(vSheet)'
      as Source,
      FileName() as FileName
      FROM
      [L:\Distribution\PFP\2014\*.xlsx]
      (
      ooxml, embedded labels, header is 3 lines, table is $(vSheet));
      NEXT

      LET vSheet = Null();
      NoConcatenate
      Final:
      LOAD * Resident Tables;
      DROP Table Tables;

      LET vSheet = 'Call Center';

      Concatenate
      LOAD Year,
      [Call Center],
      Month,
      Associate,
      Attendance,
      Quality,
      Product,
      [In/Out Pool Indicator1] ,

      [Payout - Attend],
      [Payout - Quality],
      [Payout - Product],
      Payout
      ,
      FileName() as FileName, '$(vSheet)' as Source
      FROM
      [L:\Distribution\PFP\2014\*.xlsx]
      (
      ooxml, embedded labels, header is 4 lines, table is [Call Center]);

      LET vSheet = 'Client Services';

      Concatenate
      LOAD Year,
      [Client Services] ,
      Month,
      Associate,
      Escalations,
      Credits,
      Defects,
      [In/Out Pool Indicator1],

      [Payout - Escal],
      [Payout - Credits],
      [Payout - Defects],
      Payout
      ,
      FileName() as FileName,'$(vSheet)' as Source
      FROM
      [L:\Distribution\PFP\2014\*.xlsx]
      (
      ooxml, embedded labels, header is 4 lines, table is [Client Services]);