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