Union specific tab from multiple excels in a folder

Question

Hello all,

I have multiple excels in a folder. Most of the excels have a tab called 'summary' along with other tabs.

I want to take all the files from a folder - take the data in 'summary' tab from all the excels and union the data.

The schema for all the 'summary' tabs is almost similar( meaning - there could be few less and extra columns but most of the times. That's okay, its allowed to have NULL values if a particular column is not present while performing a union)

This is what I tried

Main Workflow: I tried starting with a Directory tool.

Macro1: Then created a macro which brings only the list of sheet names.

Then I concatenated the Full Path with "|||<List of Sheet Names>" (Already applied a filter to get only 'summary' from Sheet Names column)

Macro 2: iterative:  I tried to send the new full path( Full path plus summary sheet name) as parameter into the new macro which had a a union tool.

Can someone please help as I am getting the data from same file repeatedly instead of all the files.

binu_acs · Answer

@sunilbabu not sure why you are using the iterative macro, the batch macro is able to do the union for you. Attaching a sample workflow, just update sheet1 to summary in the filter tool

Summary_211123.yxzp

CoG · Answer

* Is the output of Macro 1 correct?
* Why do you have a Union Tool inside an iterative macro? Each iteration should be pulling in data from a single full path and sending that to the macro output, which Alteryx will union on the back end after the macro completely runs.
* Where exactly is your output failing (extension of #1)?

rzdodson · Answer

I think your initial approach makes sense - bringing in the Directory tool. What I would do from there is not to bring in the list of sheet names, as it is irrelevant for this use case. I would go Directory tool to Formula tool. The Formula tool will just add the "|||'Summary$' that you need to support bringing in that sheet. From there, you should have a full file name + sheet name to support a batch macro that will go through each workbook, grab the data you need, appending the data to the bottom of the data set, and then going to the next file name to repeat the process. In this use case, you do not need an iterative macro.

If you have a sample of the file paths, that would be helpful in creating a workflow that supports this process for you.