Extracting all files and all tabs data saved on the drive

Question

Hi...I am looking for a solution on my query.

In a parent folder, I have different sub folders with one excel file each with multiple tabs. My requirement is to consolidate the data from all the excel files, tabs wise.

For example :-

My Parent folder name is - Module 2

Name of the Excel file - Mod 2 Template

Sub folder names- 1) Amit 2) Jyoti 3) Divya 4) Sagar etc

Tab name in these files:- 1) Controls 2) Counterparty level 3) Activity Level 4) N&G 5) Activity List

My requirement is to have the tab wise consolidation data. So it means all the Controls data from all the sub folder should be at one place and Counterparty level data at one place and so on.

Felipe_Ribeir0 · Answer

Hi @akumar2609

You can take advantage of the batch input macro (Here is some explanation about if you never used it before: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480)

Adapting it to your use case:

1-It can take all sheet names of your excel files

2-It can import them to the workflow

3-After that, you can dynamically output it as you wish

Batch Macro Workflow.yxzp

mceleavey · Answer

Hi @akumar2609 ,

I built a couple of tools to handle this rather neatly.

The workflow uses the Directory tool with the sub-folder option checked to pull in all files that meet your naming criteria in a given network folder hierarchy. It then loads in all tab names, appends this with the "|||" to the filepath, then uses this to loads them all in and union into a single dataset:

Simply point the Directory to your parent folder, change the naming convention you need, and change the filter to be the tab name you want (you will need to copy and paste this and subsequent steps to accommodate the other tabs).

There are two Bulien tools in there so I have packaged them with the workflow (.YXZP), however I have also attached them separately for you to save into your macro folder.

I hope this helps,

M.

mceleavey_solution.yxzp

mceleavey_solution.yxmd

Dynamic Multi File Sheet Names V2.yxmc

Import Multiple Schema Flat Files.yxmc