Most efficient way to read multiple sheets in multiple excel files together?

Try this, two macros; one to extract the file and sheet names, the other to extract the data (using auto configure by Name)

Macro.yxzp

All comments

https://knowledge.alteryx.com/index/s/article/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range-into-a-Workflow-1583461464206

Have a look at this KB article - https://knowledge.alteryx.com/index/s/article/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range-into-a-Workflow-1583461464206

Deano478

Hey @Gandalf_NotGrey When I was doing this before I found this knowledge base article really useful:

aatalai

@Gandalf_NotGrey can you not do it without the black till done and doing it all with one dynamic input (assuming they are the same schema)

Thanks @davidskaife and @Deano478 . That article was very helpful.

I am facing two errors. First one is this.This is inside the Macro that I have built using the article above- "________ has a different schema than the 1st file in the set." - Out of the 10 sheets I have in each file, some excel sheets have a different schema."

Second is this - "Can't find the File....." - This is in the workflow. The Macro is not able to read a single file.

So for your first error it should be a 'relatively' simple fix, have a look at this KB article under the Batch Macro section and how to deal with different schema, it has a macro to download to help as well - https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

You will end up with a macro that has each file name/sheet name fed into it one by one and all the data is output based on column position or name

Another option is to have another Dynamic Input tool, which points to a sheet with the different schema, and process the remaining sheets that way. Only downside is if you have many sheets with many different schema.

For the second error I'm not sure on that one without seeing more. Are you able to either share the workflow (with anything sensitive removed), or, share a screenshot of the config etc

Hello @davidskaife - Yes, I saw that article on Schema Correction Batch Macro. In the below screenshot, I have this Macro, where I have selected one out of ten excel sheets of a file. My doubt is, do I have to create one such Schema Correction Macro for each of the ten sheets? And then include these Macros at what point in my workflow?

Try this, two macros; one to extract the file and sheet names, the other to extract the data (using auto configure by Name)

Macro.yxzp

Thanks @davidskaife for sharing this. I understood how you have used the Formula tool in the Macro to replace the File names. Thanks so much.

Hello @davidskaife - One more question please. The Data Extraction Macro is failing on the sheet that has a different schema. I am getting this error - "The field schema for the output "Output38" changed between iterations." in the main workflow, which is shared below -

Main Workflow.png

Output 38 is the Macro Output tool in the Data extraction Macro.

Hey @Gandalf_NotGrey

I may have missed something obvious....on the Interface Designer settings can you tick 'Output fields change....' if you haven't already

Thanks @davidskaife - I did that. Now, the second Macro that is extracting the data, is running just once and the workflow is ending after that. I am getting the below message in the logs -

"1 iterations were run (ended by a downstream tool)"