This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi! I have a workflow that has two streams. Both of the input tools use the same file just different tabs. For example A1, A2, A3 are supposed to go through Stream 1 and B1, B2, B3 are supposed to go through Stream 2. How do I make the workflow into a macro where I can feed it one file and direct the A type files to go into one stream and the B types files to go into another?
Additionally, how can I create an output where it will put the A1 & B1, A2 & B2, A3 & B3 outputs together in the same file?
There are 2 main reasons to use macros in your workflows. The 1st is to do something that just can't be done in a standard workflow, i.e. iteratively or recursively processing the records until some condition is met. The 2nd is to package a group of controls to be reused. Correct me if I'm off base here, but i think this is the case that applies to your situation. You have 2 inputs that are similar and you'd like to reduce this to a macro that you can then add into your workflow twice, once for each input, thereby reducing the number of controls in the main workflow and tidying it up.
The answer to whether to use a macro or not depends on the amount of duplication in your main workflow. If you have 2 chains of tools that are identically configured and differ only by the input, then moving the duplication into a macro will make your workflow easier to maintain and avoid the types of errors that can occur when you're trying to make identical updates to both streams. On the other hand if the only duplication is the input tool and then each file is processed differently, adding a macro only increases the tool count and complexity of the workflow. You'll need to feed file name inputs to macro which will take some additional controls.
There may also be another way to deal with your situation. If your two input streams really are identical, you can include the filename as field in the input tools and the union the two record sets. Then process the combined record set through a single tool chain, grouping by filename where required.
For your output question, if you have a small fixed number (N) of columns you can have N Select tools which then route to N Output tools. Each select tool will configure to select one of the pairs of columns, A1&B1, etc. Of course for this to work you'll have to join the 2 sets on some common key. If you have have a union of the 2 sets the use a filter to separate them by filename and then join them on this key. If you have a large or varying number of columns, then there are ways to transpose the data and create a workflow that deals with this variety.
Hope this helps. Let me know if you need more assistance. Adding your workflow and some sample data will make it easier for us to help.
Sounds like you'd be using a File Browse tool from Interactive, which will connect to 2 inputs. The input files should be set up to read the different sheets in the Excel file.
Configure the tools to replace just the file name, as below. In each Action tool, choose to Update the File Value, and tick off the Replace a Specific String box. Take out the sheet name, so you're just left with the name of the Excel file.
Macro will look something like this: The Union tool here will stack the data. If you need it side by side, you could use the join instead.
@danilang Thanks for your reply! I have one common file with several tabs that I want to first separate and organize and then send through two different workflow stream via a macro. I cannot upload the workflow since it contains confidential information but I've attached screenshots of what I tried to do. Let me know if I'm going down the right path or totally off. Thanks!
@jillian Thank you for your reply. I misread your question.
You can try a batch macro that feeds in a list of sheet names from the file (so long as it's an xlsx). That will then drive the sheet name (so in my example, instead of replacing the whole file name, you'd be replacing the sheet name with a Control Parameter. Let me know if you want to see what that looks like).
You might also look at the Wildcard input from Crew Macros (shout out to @MarqueeCrew ). That one could work for you too, and all you need to do is install it. (chaosreinswithin.com)
Your macro could have 2 inputs, each bringing in one tab from the file. If you need to have something more dynamic, then you'd need a different macro.
What I'd do is save the workflow you have as a macro, changing the inputs into Macro inputs (right-click them). Then try it in a new workflow and see if it works. Two input tools would feed the macro, each referencing the different sheet.
Does that make sense, or do you need something more dynamic?
Not sure if I'm understanding your solution. I guess I would want something more dynamic. One workflow will read the sheets and direct it to which stream and it would work as a macro until all the sheets are read.