Directory search and append files

[Darren_Line]

Good afternoon,

I am new to Alteryx so please bear with me.

Let me explain my situation which I hope Alteryx can provide a solution.

I have multiple directories and subdirectories where I need to find all excel files that have the string "Appendix 6 Additional Lines" in the title.

Each of the files identified will have any number of sheets, however the ones I am interesting in are the ones that have the word "Lot x" in the sheet name.  The x could potentially be from 1 to 100.

I would like to create a new workbook which appends all Lot 1 sheets together, Lot 2 together, Lot 3 together and so on.  Essentially combining them all in their own respective Lot.  Finally, remove all the blank lines.  In each of the Lot sheets, Row 8 will have titles with row 9 being the start of data.

Now I have already use the Directory tool with RegEx (to remove any silly characters in the filenames) and Filter to provide a list of all the filenames I am interested in into a field, the problem is I don't know where to go from there.

Any help and suggestions you can provide would be greatly appreciated.

Regards

Darren.

[gabrielvilella]

I encourage you to use the action to change the entire file path on the Dynamic Rename. You just need to make sure that the field you are using has the entire file path for each file.

[MichelleL · Accepted solution]

Hi @Darren_Line - To get the filename and sheet name in the final output, edit the Dynamic Input's 'template', and choose Full Path for option #5.  This will give you the Full Path in the output, including the sheet name.  From there you can parse the Full Path output if you need to split out the Filename and sheet name.  Is this what you're looking for? 

I've updated the attachment for containers 4 and 5.  Below screenshots are from container 5.

Configuration:

Results:  

[Darren_Line]

Good morning everyone,

Over the weekend, I was giving some thought to my problem and felt that I needed to get input from experienced Alteryx users.

Thinking about how to join different schemas in one final output, I thought of using the Union tool.  So my first question is, can the Union tool merge multiple sheets within the same workbook?  If not, then each of my combined "Lot" sheets would need to be output separately and then Unioned in the final stage.

However, this solution needs to be dynamic as I will be running it multiple times, each time the number of lots may differ.

So how would I take each sheet from within a multiple worksheet workbook and combine them into their own respective output (again, just to clarify, each sheet of the same name in each of the workbooks will have the same schema, but the schema could change between each sheet within the workbooks) and then apply the Union on all the outputs generated?

With that being said, if there is an easier way of achieving this that I am not aware of, I am happy to look at alternative solutions.

[gabrielvilella]

There are basically two solutions that will involve different steps based on what you need as a final result.

1. You can read all the files and sheets and combine into a single table, even when the schema does not match. You can do this by reading with a macro. From this full table, if you need to output multiple sheets (one for each original Lot) you will need another macro to mainly remove the null columns. 
2. You can read all the files but the sheets in batches. You can have a macro that will read the same Lot from all files, combine into a single table and output the table for that Lot. It will repeat the process until all Lots are done.

[MichelleL]

@Darren_Line - To answer your question, yes a Union tool can merge multiple sheets in the same workbook.  From your description of what you're trying to do, apply a solution to sets of files that have different schemas, your best bet will be a macro.  See container #7 in previously attached solution.  This will show you how to set up both the workflow as well as the macro.