Consolidating and Formatting Data from Multiple Files into One Table

[Jory_Bishop]

Hi Alteryx Community!

I am trying to figure out the best way to approach this - here are some details:

- I have 300+ files (all identical excel templates) but they all have different data.  Looks like a form you would fill out anywhere, has a variety of questions the users need to respond to.  

- The files all have different names, but are saved in the same location.

- The files are all the exact same format.  For example, in cell C9 in every file, the users would fill out their company name.  And in C10 would be their responses.

- I need to combine them all into one file.

- Ideally, the final product would be an organized table. Ie, Row Headers for all the questions on the templates, and columns would be the responses from each of the 300+ templates. 

I have attached a visual example for reference.

Any help would be greatly appreciated!

Thanks

Jory

[3I_ATLAS · Accepted solution]

Hi Jory,  you are going to have to play with it a little to adjust to the exact format of your excel sheets, but you should be able to use something similar to this workflow.

You can even skip the macro and use wildchars in the Input tool to read from all excels, provided they have the exact same format.  Alteryx will skip files with the slightest of differences though, so watch your log pannel.

Hope it helps!