Directory search and append files

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.

Input

Output

Parse

Help

Join

Accepted answers

MichelleL

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:

Directory+DynamicInput-Relative Paths-INSPIRE2022-TEST.yxmd

All comments

sbatra116

@Darren_Line

Use "Dynamic input" Tool.

read below for more details

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220

Darren_Line

Thank you for responding.

I did look at using Dynamic Input tool, but I came to a grinding halt when it was asking for a template. I didn't know what it was asking or what format is required etc.

Let me review the link you kindly provided.

sbatra116

@Darren_Line

use an empty xls file as template

gabrielvilella

Hi @Darren_Line, if you do not want to have one single table with all the data (all the Lot numbers together) and have a final table for each Lot (on a single Excel file, for instance) you will need a batch macro. This way you can read each Lot number at a time and process them by number. If you could provide some same files I can build a working workflow for you.

Christine_Assaad

Hello @Darren_Line ,

I built a workflow that looks at a specific folder, look for specific sheet(s), group data by lot # and then output data for each lot.

I added comments to the WF to explain the logic. The 2 files I used for the test as input files are also attached.

Hopefully this is what you are looking for or at least help inspiring you.

Cheers!

Thank you to everyone who has responded with ideas and suggestions. I'm tied up in meetings most of today, so when I get chance I will review the comments and spend some time playing with Alteryx based on the information above.

I'm sure I'll be back in touch soon

MichelleL

Hi @Darren_Line - not to overwhelm you with responses, but since you are new, I thought I'd share a workflow with you that gives you a variety of ways to bring in multiple Excel files (or any other type of files) with either the Input tool or the Directory tool. I believe one (or more) of the scenarios within covers what you are trying to do. Please check it out (attached) & let me know if it helps or if you have any questions :-)

Directory+DynamicInput-Relative Paths-INSPIRE2022.yxzp

Darren_Line

@MichelleL

Thank you for the examples you have posted above. I have spent a lot of time reviewing and trying to understand each of the examples provided.
Although they are very good examples, what I am struggling with is keeping the sheet name and filename in the final output.

I also have to use the directory tool because of the 256 character limit of the Input tool (we have very deep folders on our server) which is adding more complexity.

One thing I am struggling with is the output of the Dynamic Input tool, it is using the filename of the input template in the output instead of the actual filename the data came from.

Any ideas?

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

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

Configuration:

Results:

Directory+DynamicInput-Relative Paths-INSPIRE2022-TEST.yxmd

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.

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.
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.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335