Pulling specific columns from several excel sheets to create merged output

[Luca]

Hi All, 

I need to run a weekly report in excel. The information I need in one workbook but on several sheets (between 15 and 20).

The sheets are different in numbers of columns, headers (even if containing the same information) and so on. 

I am new to Alteryx and I have tried to browse solutions in this community but so far I could not solve the issue. The closest solution I have found was discussed here.

At this stage, I presume I will need to go through each sheet and clean the data manually. 

Here's my question.

Is there a way to pull the information from each sheet without using the Input Data tool for each tab that I need to amend?

Using the Input Data 20 times does not sound right. 

Thank you in advance,

Luca

[BenMoss · Accepted solution]

Hi @Luca,

It is possible to input multiple sheets using the dynamic input tool but from within this tool you must set a 'template' with which the scema of the sheets Alteryx brings in must match.

I often point people to this post too when talking about bringing in multiple sheets. https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-...

This macro, irrespective of the schema will stack tables on top of each other.

Both tools require the filepath of each sheet to be generated and passed into the macro. This can be done with relative ease within Alteryx.

Use an input tool and from within this browse to your desired excel file. On selection you will recieve a prompt to select a sheet. At the top you will see '<List of Sheet Names>'. Select this option.

In the input tool also select the 'Output File Name as Field' option and change this too 'Full Path'

On running you will receive a two column data stream, one specifies the sheet name and one has the file path. You will get a row for each sheet.

You can then use a formula too to bring these fields together to generate your path which includes the sheet. [FileName]+[Sheet Names]. This can then act as the input for the batch macro or dynamic input.

Ben