Struggling with regex expression

[jw68937]

Hi everyone,

I've got an SAP report field with column headers in it that I'm trying to delineate out, seems like regex would be best for this but I'm a newbie and struggling with it. I color coded each set of intended marked groups, there is at least one space between each group (with some having more) and I also want to get rid of the pipes "|", anyone willing to help here? :) 

| CoCd G/L acct Short Text Crcy BusA Balance Carryforward Balance,prev.periods Debit rept.period Credit report per. Accumulated balance|

Thanks so much!

Jennifer

[DataNath · Accepted solution]

Hi @jw68937, is it only this field? Or are there more rows with different combinations/formats? If it's just the one, then the following should do the trick:

\s(\w+)\s(G\/L\sacct)\s(\w+\s\w+)\s(\w+\s\w+)\s(\w+\s\w+)\s([A-Za-z,\.]+)\s(\w+)\s([a-z\.]+)\s(\w+\s\w+\s\w+\.)\s(\w+\s\w+)

Not the prettiest but works for your example. Let me know if you have any issues! Workbook also attached.

[ChrisTX]

Unless you resort to hard-coding for the specific example you provided, I don't think you'll be able to split out text like that.  There doesn't seem to be a pattern you can clearly define.

If you know the exact field names, and you just have one example, you could just hard-code the field names and avoid regex.  I'm guessing you have more examples.  But maybe not.

Whoever designs report output like that, using a space as a field name delimiter while knowing that some field names include spaces, should then have to work with that output further downstream.  Would be a valuable lesson to NOT design something like that.

Chris

[jw68937]

Hi there, 

Thanks for the suggestion, it's outputting just blank columns, maybe cause of that pipe at the beginning? Does something need to be added for that? 

I've got that actual report data which has the same delineation that I'm going to join later, for now just this one field. 

THanks

[jw68937]

Haha, preach Chris, tell that to my friends in SAP administration 😂

[ChrisTX]

Reminds me of an experience during a house remodel....

the plumber comes in and installs all the drain lines for the 2nd floor

the AC guy comes in and some of the plumbing is in the way, so he just cuts it out and installs his AC lines

the plumber isn't happy

make the AC guy replace the plumbing lines, or even better PAY the plumber to do the work a second time, and ..... guaranteed the AC guy won't be cutting any more plumbing lines.

If no one ever asks (or tells) your SAP admin friends about the issues they cause, they'll never know.

[DataNath]

Hey @jw68937 looks like that's caused by some tabbing/duplicate whitespace. If you place a data cleanse tool before the regex and select the following option, does that remedy the issue? Cheers!

[jw68937]

Ooooo beautiful, thanks! This did the trick, will mark as solved! :)