Excel Files Consolidation macro

[dkma]

Hello all,

I wanted to consolidate a number of excel files into one. Each excel file contains several sheets. But I would need to grab the data from a specific sheet from each excel file.

Does any body have the Work flow or Macro for the same.

Any help is  much appreciated

[messi007 · Accepted solution]

@dkma,

there is a macro in the gallery that allow to this:

https://gallery.alteryx.com/#!app/Read_All_Excel_Files/58dd51b3a18e9e18fca64172  

Please take a look on those links as well:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/load-multiple-excel-files-from-a-folde... 

https://community.alteryx.com/t5/Engine-Works/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-ALL-Exc... 

Hope this helps,

Regards,

[ArtApa · Accepted solution]

Hi @dkma - Here is one more article: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi... 

[dkma]

Thank you all. Allt he solutions were very helpful. It did my work.  🙂

[dkma]

Hello all,

i went into a scenario today.

For Ex. There are 100 Excel (xlsx) files and each File has A, B C, D sheets.  The Objective is to pull the data from B Sheet from each File. But in some cases, the B Sheet Name is changed to B1. So. the workflow is throwing an error for this particular excel file. and consolidating the remaining sheets.

is there a way to notify the error message as a separate output. Or is there anyway to handle such scenarios ?

Best Regards,

NIkil

[messi007]

@dkma,

When you filter the sheet name you can use contains([Sheet name],"B") instead of [sheet name]="B".

Hope this helps,

Regards