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

[Rajat_BNYM]

Hello, 

I am working on a project where I have to read a set of excel files, having 10 sheets each. I have to read all of them.

Currently I am using a sequence of Block Until Done to read one sheet at a time, from each file. 

The attached image shows how I am reading the data. I am creating the Fullpath with the sheet name in the formula tool and then passing this through the Dynamic Input tool.

I believe there could be a much more efficient and quicker way to do this, but I can't seem to find that out. So reaching out to you guys for help.

Thanks.

[DavidSkaife]

Hi @Rajat_BNYM 

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

[Deano478]

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

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

[aatalai]

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

[Rajat_BNYM]

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.

[DavidSkaife]

Hi @Rajat_BNYM 

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

[Rajat_BNYM]

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?

[DavidSkaife · Accepted solution]

Hi @Rajat_BNYM 

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

[Rajat_BNYM]

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.

[Rajat_BNYM]

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 - 

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