Getting specific tabs from multiple Excel files

Hi all,

I am trying to automate a lengthy process. I have a x number of input files with a range of 1-3 worksheets.

As I only need 1 sheet per file, I am trying to combine all those single sheets in a new document. These documents have however different tabs that are needed.

From the attached input files, I'm only needing the tabs called "Needed Tab" and "This one". All other tabs can be disregarded.

As an output, I am trying to combine the data from the three specific needed tabs.

I am unsure how many input files there are, but they are always called "Needed Tab" or "This one".

Is there any way for me to achieve the desired output, based on a x number of input files?

Input

Accepted answers

THQY

The attached solution would achieve your needs:

Let me know if you have any queries.

Solution.yxzp

All comments

binu_acs

@RemcoV @This can be achieved using the batch macro

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

THQY

The attached solution would achieve your needs:

Let me know if you have any queries.

Solution.yxzp

binu_acs

@RemcoV one way of doing this

Multiple_Sheets_09_06.yxzp

suby

Hi Tiffyyy,

On your Macro on the Input Data if i point the input 1.xlsx and with List of sheet Names then I'm getting the below error could you please help.

Error: MAC (19): Record #2: Tool #7: The field schema for the output "Output7" changed between iterations.
Error: MAC (19): Record #3: Tool #7: The field schema for the output "Output7" changed between iterations.
Info: MAC (19): 3 iterations were run

jemma669

Hi Suby

The dynamic rename tool in your solution works where the files are exactly the same, they may look the same in terms of headers however this tool requires all values to be the same format too.

For example Article Number is read as Int16 on the first file but may be read as a VString on the second file, this means you have a different schema between iterations. This can be difficult to correct as Alteryx will guess the data type as it reads the data for the first time and ideally the source data needs to be corrected for this solution to work.

The solution attached below will not require the sheets to be exactly the same between files, this will read the relevant sheets and all data contained regardless of the schema.

Hope this answers your question.

Mutilpe Files Selected Sheets.yxzp

IraWatt

Hey @suby,

Did you find a solution to this error?

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335