Trying to create a Dynamic RegEx Macro

I have a Fixed Width text file that was downloaded from our mainframe.

There could be up to 5 different "Record Types" in this one text file, they are defined by the first field [Record Type].

Record Type Field_2 *

PA22109 0024057202402140346AN9A 2024021320240213PO

DE0E9D 3XS000001663250 01 0877

Each record type has a different set of columns. My goal is the get this flat text file into a readable Excel file so our staff can answer questions the client has regarding the data.

I THINK I want to use the RegEx Tool to help parse out the data in [Field_2] for each Record Type. I have no experience with RegEx so I've been scouring google looking for help to understand what I'm doing.

I got my PA line to parse beautifully when I manually enter the Format to Convert Regular Expression text box to: (.{2})(.{24})(.{7})(.{8})(.{4})(.{24})(.{8})(.{8})(.{1})(.{1})

My question is: Can I use a dynamic formula created field to enter this expression?

I've attempted to create a macro that has two input nodes, one for [Field_2] and the other for the formula field. I have one output node to display the parsed fields.

I'm getting two errors on the macro when I run the workflow:

1. UpdateRegEx (15) Record #1: Tool #2: 11 Marked Groups were found, but 1 fields were defined.

2. UpdateRegEx (15) The output connection "Output18" was not valid

I'm open to ideas to make this work. I can manually enter the string but I was hoping to make this more dynamic so the end user could define any file structure by supplying a separate schema document.

Thanks for your ideas!

Dynamic_Parsing.yxmd

UpdateRegEx.yxmc

Regex

Parse

Batch Macro

Accepted answers

EJ5916

I wasn't able to get your macro to work so I went a different route and I got that route to work for now. It's not as dynamic as I would like but the output is good. My next task is to try to automation it using an app. Wish me luck!

All comments

apathetichell

you have one output field in your regex tool. you have 11 marked groups. Alteryx does not know what to name your 11 marked fields.

apathetichell

This gets you a little closer - but you should look at a nested batch macro and a logic so you know which regex to apply to which recordid.

Dynamic_Parsing.yxmd

EJ5916

I don't understand what you did here or how this flow is better/different than mine. Can you explain?

However, your comment about it not knowing the names was very interesting. My layout document has the field names included, perhaps I need to pass that list to the macro as well to apply to the parsed out fields? But I don't think the parsing is working based on my passed in variable so I might not get too far with it.

apathetichell

Sure. Mine works because:

1) I do not crosstab/summarize join my matched group. I feed 1 matched group at a time into the macro - this is because your regex tool is set up to match 1 group. It will not match multiple groups - it will match 1 group. Your regex tool can be set up to match any number of groups - but this is coded at the time you set up your regex tool. When you use your action tool to change your match logic - the tool does not also change the number of output fields. That would have to be changed via editing the raw xml - and using some kind of generate rows, and creating a multi-layered matched batch macro. and it would be a huge pain.

2) To get around this - I have each row as a specific matched group. One matched group. One regex output (!!)

3) To get around the issue of how we know which group we are matching for. I use a running total and then a multi-row formula tool. This sets the specific matched group as a successor to the prior matched group (and ignores what came before).

To get this to match multiple fixed width rows - with differing schemas - you would need to assume they all have the same number of final columns - or you are o.k.with nulls in some of the columns. You would put this outer macro into ANOTHER macro.

You would create a map between regex matched groups (that feed into your batch group) in your outer workflow.

You would add a filter to both inputs to the batch macro in this workflow.

The filter would determine which rows/matched fields were being matched.

apathetichell

Are we solid here - can you mark my solutoin as correct because:

1) It works.

2) I explained what you would need to do to scale this for other solutions.

3) I explained why a multi-marked group regex cannot replace a single marked group regex (and vice versa)

EJ5916

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335