Looping through Excel Sheets and Columns

[#1]

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

(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

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

LOAD F1 as Account,

     February as Budget,

     '02' as Period,

     '50040' as Department

FROM

(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

[alexandros17]

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

[jonathandienst · Accepted solution]

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

[#4]

Many thanks - much space saved 🙂