Import data from multiple sheets from multiple excel files in multiple different folders

[HW1]

I have a parent folder. Within that parent folder, there are multiple folders and within that folder, I have to extract all the sheets data into one big dataframe

This is what I mean:

Parent Folder - Multiple folders - Multiple Sheets- Different Schema

Extract file names

Within these excel files, there are multiple sheets.

e.g.

• "1. Contractor Master File March.xlsx" would contain the sheets "Contractor A", "Contractor B", etc.
• "1. Contractor Master File February.xlsx" would contain the sheets "Contractor B", "Contractor C", etc.
• "1. Contractor Master File June.xlsx" would contain the sheets "Contractor A", "Contractor C", etc.

I want to extract all the sheets within one table with a column containing full sheet path so that i can then union the respective sheets to create a master information for "Contractor A", "Contractor B", "Contractor C", etc.

I have got the file names. Now, I am aware of this example that can read multiple sheets however, every sheet has a different structure and different columns with a very few common columns and column types and thus, I am getting error when I use the Dynamic input tool.

How can I get what I want? 

[alexnajm]

Sounds like you need the batch macro option! It is attached and detailed here: The Ultimate Input Data Flowchart (alteryx.com)

[HW1]

Thank you for the direction however all the files using the macro are in the same folder. Also, the sheet structure is the same. I cannot use this case for my purpose.

[caltang]

You can still use it.... there's no harm using a Batch Macro. It just helps when you have a different structure too, this way it's dyanmically capable of handling the files. @alexnajm 's link still stands usable for your use case. :) 

[HW1]

I tried as per the example:

Macro Config

Configured the macro as per the example

Workflow

Ran the workflow after saving the macro

I get the error:

Error: Batch Correct (12): Record #1: Tool #1: No sheet specified, you must specify a sheet

I dont understand what's the best foot forward.

[caltang]

Do you mind providing some data? Let me fix it for you, please.

[HW1]

Sure!

I am not sure how I can share the folder structure however I have exported the workflow so let me know if that helps or if any other way I can provide the workflow.

[caltang]

No, as in, can you provide 3 of your XLSX files? Then I can show you how. Not the Directory tool.

[alexnajm]

@HW1 you said in the original post that "every sheet has a different structure" so this would be the solution - It just requires a few more tools beforehand. You can read in the sheet names from each file with the Directory + Dynamic Input tool with the Output File Path option selected, get those sheet names added to those paths, and use that batch macro to read in the data.

[HW1]

Sure!

Please find attached 3 of the excel files. 

these belong to different folders within a parent folder.