Getting specific tabs from multiple Excel files

[RemcoV]

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?

[binuacs]

@RemcoV @This can be achieved using the batch macro 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

[Tiffyyy · Accepted solution]

The attached solution would achieve your needs:

Let me know if you have any queries.

[binuacs]

@RemcoV one way of doing this

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

[IraWatt]

Hey @suby,

Did you find a solution to this error?

[sylvia0218]

I used this workflow but ran into errors. My goal is to grab tab "A" from 5 files saved in the directory. However, the workflow was reading data from a tab "A" of one file only. Do you happen to know what could potentially cause this error? Thanks!