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.

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

[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

[Darren_Line]

Just so I understand it correctly, the examples in the link are appending all sheets into one sheet?

Which is not exactly what I am requiring as the schema between Lot 1 and Lot 2 maybe different (but only subtly) - which is why I needed each workbooks Lot sheets on their own respective Lot sheet in the final workbook.

To clarify, all workbooks Lot 1 sheets will have the same schema, all workbook Lot 2 sheets will have the same schema - but not always the same schema between Lot 1 and Lot 2.

If there was a way to make all schema's the same, then I would be happy with them all in the same sheet.  Which is what I have to do manually currently now anyway.  Once all sheets have been appended, I look at the columns and ensure they are all the same and in the same order (usually by adding dummy columns in the sheets where they are needed and rearranging columns as necessary). 

[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!

[Darren_Line]

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 :-)

[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?