Parsing/REGEX Help

[Whodathunkit]

Hey everyone,

I'm pulling data from a data lake and I'm trying to parse it out into fields and values. The fields are separated by commas (,) and the fields and values are separated by equal (=) signs. One field, backofficecomment, often contains commas as it's a manually entered text field from the originating system. 

Parsing out by text to columns doesn't work using comma, as the the backofficecomment field will be split out into many fields sometimes. 

I've tried using REGEX, but either the syntax is different than the systems I've tried it in, or I'm completely missing something. https://www.phpliveregex.com/p/u5a

I've attached a small set of sample data.

If you have any pointers for this please let me know. If you know of a different solution other than REGEX, I'm all ears.

Thanks,

Tyler

@JordyMicheal bonus points if you get to this first.

[MichalM]

@Whodathunkit 

Is it safe to assume that the column names are fixed and will not change?

[DiganP · Accepted solution]

@Whodathunkit You can just use this expression in the regex tool.

(branch=)(.*), (backofficecomment=)(.*)

 

Attached is the workflow.

[Whodathunkit]

@MichalM correct. Column names should always follow a comma and precede an equal sign. In this example branch, backofficecomments, and engadid are columns.

[MichalM]

What I had in mind was whether the actual variable names are fixed. If they are, what @DiganP suggested will work. If not, you'll need a different solution. 

[MichalM]

@Whodathunkit 

I played around with something like the below

([a-zA-Z]+)=(.+),\s([a-zA-Z]+)=(.+),\s([a-zA-Z]+)=(.+)

However it seems that it's can be both commas and equal signs that appear within the backofficecomment field which breaks the logic

[Whodathunkit · Accepted solution]

I think I got it, partially using what @DiganP posted.

Whether or not it's the correct or most efficient method, I'm not sure. But at the end of the day, it seems to be working. 

Thanks to both of you, @MichalM and @DiganP