Trouble in macro town!

[mark007]

Hi all

I have just completed Weekly Challenge 20 and while successful, there is a much better solution using a batch macro. But... I can't get it to work and the time spent debugging is bordering on the ridiculous.

I attached the workflow package, including the macro and the original workflow.  I have left the many many regex tools in place to prove they work as act as intended and created a new branch for the macro (for which I have only mirrored two regular expressions for simplicity).  When it comes to the macro, it appears to be running the first run of the macro, but then for some reason is not updating correctly on the second run.  This is driving me crazy and any help would be really appreciated!

Thanks

Mark 

[PhilipMannering · Accepted solution]

What's happening is your batch macro is running twice (for the two regular expressions being fed into the control parameter), but it's assuming the same schema and just vertically stacking the results.

Try changing that output fields will be stacked by name.

But it's still not gonna put the fields side by side. I'm not sure how you would do that tbh.

Check my example as something that could work. You could get the results labelled and stacked and then pivot...maybe ??

[mark007]

That's awesome, thanks @PhilipMannering !  Something new I have learnt.  

Happily, it does create additional columns as well as append results.  What is frustrating is the macro is first designed to parse the expression and then another regex to remove it from the original field to make further identification of items in the field easier.  With the existing design it unfortunately takes a fresh input each time rather than iterate and then appends all the results, not achieving my goal.  How I got around this is by creating an output file to write over the input and replace the macro input with a normal input, thereby iterating the file.  Take a look if you are interested!

Thanks again!

Mark

[PhilipMannering]

@mark007 Niiiice. I never thought of that. Though I dare say the writing and reading from a file isn't that efficient.

There's an alternative with the Dynamic Replace Tool that might be worth a look,

[mark007]

That's really smart, I haven't come across that tool yet, a really interesting application!

The challenge with that method (and why I used a regex parse and replace) is identified by your formula for address.  You can see from below it is picking up both the address and the phone number and then not parsing out the phone number at all.  I thought this might be to do with order of the columns but changing them doesn't seem to make a difference to the result.  The reason for my replace within the macro is so that I could identify phone number, remove it from the field and then repeat the process until only address was left.

Thanks for teaching me a new tool and have a great weekend!

Mark