Iteratively pull in workbooks for one workflow with common output

[JoshuaElphee]

I have 60+ workbooks which I need to run through a workflow, one at a time.  I have attempted utilizing the input  "filepath\*.xlsx" where each of the inputs are currently housed, however this appears to only run the first file through the process, despite the schema being exactly the same for each of the 60+ input files.

Within the workflow I (am attempting to):

1)  total a number of columns within a new row at the bottom

2)  pull in one row of data from a separate input file (added at the bottom of the excel sheet)

3) total these two rows in a third new row of data

4) add percentages for each of the totals within step 3

4.5) Following this, for each individual workbook, I need to iteratively add the totals from step 3 above into a new workbook (replicating the workbook utilized as an input in step 2 above).  I have not identified how to make this possible.

5) Save the new workbook in a new location, save the workbook that has appended information per step 4.5 above

6) Move to the next input workbook and start at step 1 above

I am not certain of how to best pursue these actions.  I have attached an example of what I am looking to do.  NOTE: while in the example these are separate sheets of the same workbook, in actuality the inputs and outputs are all separate workbooks.

A)"Input Example" - an example of one of the 60+ input workbooks as it currently stands

B)"Input 2 example" - an example of the workbook utilized in step 2 above as an input to each individual workbook (input example shows Job "ABC1" in column J; so the workflow reads "Input 2 example" and pulls in the totals in the row cooresponding with Job ABC1")

C)"Indv. output example" - example of the individual workbook outputs after being pulled in through process steps 1-6 above (totals from the current report provided below the data; Previous totals pulled from input 2 example workbook; these two rows totaled; percentages of obligations provided in last row)

D) "Final totals" - example of the workbook referenced in step 4.5 above (pulls the final total from each individual workbook into the spreadsheet as an appended row, saves and closes/waits for the next input workbook

None of this is currently working.  I believe I have errors in my logic throughout the workflow, however the largest of these currently is not being able to iteratively perform the same action for numerous spreadsheets (and save these individually/get the extra "totals" output workbook)

[acarter881]

Hello, @JoshuaElphee.

I believe you can either run the workflow once, or run the workflow 60+ times with a batch macro. It depends on how the workflow is setup. For example, to run it once, you would need to read in all those xlsx files with the same schema. Those data should be unioned together within the Input Data tool. You can group data downstream if you select Output File Name as Field as Yes. Please see the attached png file.

Does this help? If not, perhaps some more clarification is needed.

[geraldo]

@JoshuaElphee 

if you can send your workflow and at least 2 sample files it could help in your logic to restructure your workfow

[JoshuaElphee]

@acarter881 , thank you.  I am not certain I fully understand; I will advise that my goal is not to run my workflow on combined workbook including 60+ individual files; rather I intend to perform the same action on 60 individual files.

This said, the workflow I utilized to give me the 60+ individual files (for this step) looks like the file I am attaching here just before the workflow splits the files based upon "Job" and saves each with its own file name.

Is there a method with which I could (instead of filtering by Job and saving a separate file for each individual job) split/filter based upon the "Job" (column J) and have the process run once per "Job"?

ie. with this additional input example, I would have a final workbook for Job ABC1 (with the workflow described in the question run), one for DEF21, one for XYZ7, etc.?

[acarter881]

You're welcome, @JoshuaElphee.

I know what you mean. You want to loop over and repeat the same action on the workflow.

Please see the attached png files and the yxzp.

The workflow allows you to run the loop for as many unique jobs as you have, outputting the filtered data into its own Excel workbook (one per unique job).

[JoshuaElphee]

@acarter881 

I think I am following; however for batch-2.png I am a bit confused by "[job]='ABC1'"  is this automatically updated with each additional job as I follow this workflow?.  Does the rest of my workflow go in-between the input and control under batch-1.png? or does this occur first?

I am going to play with this a bit to see what I have.  I spent most of my day perfecting the in-between workflow; finally I can confirm that the input method I had absolutely did not work. 

[acarter881]

@JoshuaElphee "[job]='ABC1'" is what I wrote in the batch macro as a test so I could output records into an Excel workbook. It is updated for each job you have. You can think of it as removing the manual effort of updating that reference to the specific job id.

The yxmd (i.e., Alteryx workflow) uses the yxmc (macro, which in this case is a batch macro). The efficiency is gained from pressing the Run button once in the yxmd and the looping happens. I don't know exactly how your current workflow is created, but what I show in the yxmd can be applied anywhere.

I suggest opening the yxzp and running the yxmd and looking at the macro to view how the process works. You can view the outputs in the Results window of the yxmd. It will display links to the Excel workbooks that are created from the batch macro.

[JoshuaElphee]

@acarter881  thank you once again.  I have been testing with this and am getting the hang of it somewhat.  It currently looks like I need to input my workflow within; however I am not certain of whether this would be within the macro itself, outside; I should be able to figure this out.

One issue I am having, is that some "Jobs" from my actual source data include dashes/underscores/slashes... it appears that I cannot pull in these due to these characters.  I will look into a solution, but also appreciate any insight you may have.

[acarter881]

@JoshuaElphee The batch macro serves a specific function. I believe to the extent we can, it's best to keep functions separate. However, if you wanted to add functionality to the batch macro, that would be possible. The order of operations likely matters, but can't tell without reviewing the entire process.

You can't pull in some of the jobs into Alteryx or into the macro? The Summarize tool is grouping by job and not removing any characters from the jobs. The batch macro reads each job and runs the macro through once for each job.

[JoshuaElphee]

@acarter881 it is within the Macro that the additional characters of the "Job" field appear to be of issue.  The Summarize function does not have any issues.  Within the Input for the Macro, I am selecting the same file input as used within your proposed workflow.   I receive the errors "Record #1: Tool #2: Parse Error at char(0): Unknown variable W81K"; "Record #3: Tool#2: Parse Error at char(0): An operator must be between operands"....   It clearly reads each of the jobs, as there are 67 errors - and 67 "Jobs" listed in the input file.

Since I need to perform numerous actions on each file (individually), I assume these actions would be placed within the macro prior to the "output"?  Otherwise, I simply end up with the same output that I originally had.  I am struggling to see where I can put the workflow for needed actions alongside a batch macro and obtain the final with steps 1-5 in my original post.