Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Looking workflow through each file and append each outcome to final master doc

QF
5 - Atom

I am a newbie to Alteryx and after a week of looking to solve this problem on my own i realized that I need some help.

I am looking to run a process for each file and then have a final output appending all the outcomes of each individual file run.

 

  • On my first workflow I need to change the Input Data connection for each file to run,
  • After some online searching I found out about the batch macros that technically would solve my problem, then I added the Incoming Control Parameter to specify the location of the files I need to process individually and Outcoming Control Parameter with the location for the outputs.
  • The issue is that the process is bringing all the files at once to process instead of processing one file at a time and output the result, look for the following, process and output…. (Looping the process through all the files).

Anyone to help this poor soul?

 

Thanks

8 REPLIES 8
JoeS
Alteryx
Alteryx

@QF 

 

I think you are very close to what you need. I think you will want to add a macro output as well. This will roll them all together and pass the records outside the batch macro.

 

The other part looks like it is looping OK. Could you attach your workflow as well?

QF
5 - Atom

Hi Joe,

 

Thanks for your help, please find the workflows in the attachment.

 

The first one "Macro" run successfully but the second one  i get the flowing error  ' Macro (9) Record #1: Tool #14: You must specify a sheet name.)'

 

Regards,

 

Quidna

JoeS
Alteryx
Alteryx

Ah, I missed it in your macro first time looking through. 

 

Your action tools aren't quite set up right, and you also had the input tool set to have a wildcard, so that itself will read in all the files.

 

In the action tool you need to select the part you are wanting to change. 

 

I can see that you are passing in via the control parameter the file name (I do wonder if you want to do the full path?)

 

You then need to select that part in the action tool in which you want to change, so the first action tool should look like:

 

ActionTool.jpg

 

You can see I have the file path selected at the top, and then at the bottom I am saying I only want to change "*.xlsx" as that's the file name part.

 

You'll need to do similar for the output tool. On this note I can see you are actually passing the same value. Do you want each batch to overwrite the file that it has read in?

 

In my attached workflow and macro I have made a change to rename the output file OrignalName_Output.xlsx.

 

I have also updated the action tools within the macro.

 

Hopefully that helps, feel free to ask more questions though!

QF
5 - Atom

I've done the corrections in the action tool and change it to only take one file.

 

Also added the output formula to the Macro (the one you sent me).

 

Still get the flowing error when i run the macro 'Error: Macro (9): Record #1: Tool #12: No sheet specified, you must specify a sheet'  not sure when to correct' :S

 

Do you know how can i solve it?

 

Thanks

smoskowitz
12 - Quasar

I have done things like this before. You might want to explore using the directory tube to bring in all the files in a particular directory and then filtering out the files you don’t need.  You can then add the sheet name using the formula tool and bring that into your batch macro. 

 

 I can look at your workflows on Monday and try to provide guidance but like I said that’s my process and it works pretty well. 

 

Seth

JoeS
Alteryx
Alteryx

In Excel the Input tool needs to know the name of the workbook and the name of the sheet.

 

For example you input tool has:

 

C:\Users\DataAnalytics\Desktop\Quidna\*.xlsx|||`Timeline$

 

The parts at the end, 3 pipes, "|||" denote that the sheet name is then going to follow, "Timeline", the dollar symbol denotes that its a sheet name and not a named range within Excel.

 

I think you'll need to double check your Action tools are updating the right parts.

 

You need to have the bit highlighted on the left that you are wanting to change and then the part at the bottom with the string you want to replace.

 

ActionTool2.jpgActionTool3.jpg 

 

In both of your action tools this isn't quite lined up right.

 

 

QF
5 - Atom

Hi Joe, It's working now, thank you very much for the help. 🙂

JoeS
Alteryx
Alteryx

Great stuff, I am glad you have managed to get your solution working 🙂

Labels