Parsing data from a .txt file

[Jake5]

For the attached .txt file I am trying to have Alteryx output in a structured data table like the other attachment in .xlsx.  The .xlsx output includes the rep's first and last name where the common pattern is a new rep name appears whenever the row begins with DPSREP001.  The output then also includes the corresponding values beneath where the record begins either with DPSREP[until first space is reached] or DPSRPA[until first space is reached].  How would I accomplish this?

[binuacs]

@Jake5 What is the logic of Aaron getting 5 rows in the expected output?

[Jake5 · Accepted solution]

My apologies as the expected output only represents a subset of possible returned values for each rep.  To help clarify, I have added a screen shot and highlighted the desired values to be returned for Aaron which aligns with how the original question  was posed: the return records begin either with DPSREP[until first space is reached] or DPSRPA[until first space is reached]. Thanks.  

[caltang · Accepted solution]

Tried my hand at it with some brute force. Not the best, but based on what I was told and what I see from the patterns, I could get what you want:

[KGT · Accepted solution]

I'm not sure if the mocked up data had some extra spaces or whether it was true. The fields were big enough that the characters out of place were still in the right fields if we are to treat this as fixed width. If not, then there are other ways to identify the records, but would be best done by someone with full data and knowing all the different cases (or working through them).

Example: You could take the first 29 characters, then split the rest on spaces (after removing multiple spaces etc)...

The rest of this workflow would still work regardless of how you split that field, but I imagine it is fixed width.

I'm not entirely sure which records you need, and so this shows how to tag the whole dataset and then you can choose what you want. It's deliberately laid out to show the process. This could be done quicker in a formula with REGEX but then editing it would be more difficult.

[Jake5]

Thank you both!  This was very helpful :)

[caltang]

Hey, you marked your own post as a solution… do you mind marking mine as well?

[Jake5]

Done.  Sorry about that.  Thought I was accepting your solution and inadvertently accepted my own!  Thanks for letting me know.  

[caltang]

No worries, happens to the best of us!