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?

Sure! Please find attached 3 of the excel files. these belong to different folders within a parent folder. 1. Contractor Master File February.xlsx 1. Contractor Master File March.xlsx 1. Contractor Master File April.xlsx

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?

Input

Batch Macro

Help

Dynamic Processing

Accepted answers

All comments

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.

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.

multiple excel files.yxzp

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.

Qiu

@HW1
The data scheme/struture can be different but I observe that the data starts from Row#1 and some starts from #2. That imposes the issue.

Anyway, I used the famaous macro for inputting multiple sheets in multiple files and getting something.

1031-HW1.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703