3 Replies Latest reply: Feb 6, 2013 9:16 AM by Robert Webb RSS

    Looping through Excel Sheets and Columns

      Hi,

       

      I have our company budget in the 'prescribed' tabular format of account number in Column A; and each period of 2013 extending from columns B thorugh M

       

      Each sheet is a department which I need to tag and reference later.

       

      My basic script on edited is.

       

      LOAD F1 as Account,

           January,

           February,

           March,

           April,

           May,

           June,

           July,

           August,

           September,

           October,

           November,

           December,

           Year

      FROM

      [M:\Budgets & Estimates\Budget 2013\Submission\V3\Upload Sheets (Includes Total by Product).xlsx]

      (ooxml, embedded labels, table is [R - 50040]);

       

      I was thinking that at very least I would need to do something along the lines.

       

      LOAD F1 as Account,

           January as Budget,

           '01' as Period,

           '50040' as Department

      FROM

      [M:\Budgets & Estimates\Budget 2013\Submission\V3\Upload Sheets (Includes Total by Product).xlsx]

      (ooxml, embedded labels, table is [R - 50040]);

       

      LOAD F1 as Account,

           February as Budget,

           '02' as Period,

           '50040' as Department

      FROM

      [M:\Budgets & Estimates\Budget 2013\Submission\V3\Upload Sheets (Includes Total by Product).xlsx]

      (ooxml, embedded labels, table is [R - 50040]);

       

      I have 92 sheets with 12 months in each - even recycling the code via copy and paste etc would be poor design (at best).

       

      I am actually looking to expand my knowledge and best practice rather than just create a working but downright ugly script.

       

      I would welcome contribution please and alternate smarter approaches.

       

      Many thanks,


      Rob

        • Re: Looping through Excel Sheets and Columns
          Alessandro Saccone

          If I have understood you have a lot of sheets containing same data (columns name);

          If this is exact, try to define a variable that conatins sheets name and then cycle on the variable,

           

          you can read (with peek) the variable from a table for instance

          • Re: Looping through Excel Sheets and Columns
            Jonathan Dienst

            Rob

             

            There are a couple of steps here.

             

            First of all, to decompose the tabular structure, use the CrossTable keyword. This will transform the multiple columns into two fields, one containing the column name (ie month) and one containing the cell value. Use the cross table wizard to set this up. For example:

             

            CrossTable(Tenor, Data, 3)
            LOAD [Funding Area], 
                 [Sub Funding Area], 
                 [Total Limit], 
                 ON, 
                 [1M], 
                 [3M], 
                 [6M], 
                 [12M], 
                 [2Y], 
                 [5Y], 
                 [10Y], 
                 [>10Y], 
                 indtmt
            FROM [.......xlsx]
            (ooxml, embedded labels, table is [.....]);
            

             

             

            The result table will have the columns: [Funding Area],  [Sub Funding Area],  [Total Limit], Tenor, Data. Tenor will contain the values 'ON', '1M', '3M' etc, and Data will contain the corresponding values from the fields [ON], [1M] etc.

             

            Now to loop over files and sheets. Use a script similar to this:

             

            For Each zFile In FileList('$(zFilepath).xlsx')
            
                ODBC CONNECT32 TO [Excel Files;DBQ=$(zFile)];
                SpreadsheetData:
                SQLTABLES;
                DISCONNECT;
            
                   //.... subfield zFile here to analyse components for dates etc
            
                For i = 0 to NoOfRows('SpreadsheetData') - 1
                    Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');
            
                     //.... subfield zSheet here to analyse components for dates etc
            
                    Data:
                    LOAD ...
                               ...
                        FileBaseName() As SourceFile,
                        '$(zSheet)' As SourceSheet
                    FROM [$(zFile)]
                    (ooxml, no labels, table is [$(zSheet)]); 
                Next
            
                DROP Table SpreadsheetData;
            Next
            

             

            The outer loop iterates over all the files matching the filepath in FileList. The ODBC connection gets the sheet names with the SQLTABLES command and returns the list of sheetnames in the table SpreadsheetData. The inner loop iterates over all the sheets in the file.

             

            If you need to get information such as dates from the file or sheet names, you can break them up using subfield and extract the information. The comments show where you could do this. When I do this type of load, I like to know where each row came from, so I add the SourceFile and SourceSheet fields. If the sheets all contain the same structure, they should auto concatenate in Data, creating a single table.

             

            Hope the gets you started

            Jonathan