Parse string as row + combine multiple XLS worksheets

Question

To start, here's my data structure. Each month I receive ~20 files that look like the below. 4 tabs, identical schema, 4~25 records.

I need to extract the business unit (QQQ) from each sheet (it's the same for each workbook). In theory I could take this from any string that contains it, including the file name, but it's only in a consistent format in cell A1.

I then need to combine all worksheets/workbooks.

Attached is my workflow that can do either part (combine, or split BU) separately but I can't figure out how to join them together.

This is what I want the combined output to look like (and what Container 2 outputs for a single sheet):

Container 1 combines all workbooks in a specified directory.

test.yxwz

DavidP · Accepted Answer

Hi @271828,  this is how I would do it.

I only made 1 xls test file, so changed the sheets in the text input tool, but built it to work for multiple files and multiple sheets.

You'll see that I modified the formula tool to create 2 versions of the fullpath. The first loads the cell range A3:C from the file, which represents the data section of my test file. The second only loads cell A1.

I then have to extract both the filename and sheetname for the relevant dataset and use both as the join condition to add the Business Unit to all the rows of each dataset for each file and sheet.

Let me know if this works for you.  As before you'll have to change the template file in the dynamic input as well as the folder path in the browse tool.

You'll also have to update the cell range in the formula tool to load your range of data. A3:C basically means cell A3 to the last row in column C.