Parse white space

Hello

I have a set of data (see sample below). I want to parse the data so that there are 6 columns. the 5th and 6th column are either a number or whitespace depending on the position of digit in the string.

1000 All Goodwill ASSETS 20500000
1050 All Amortisation Goodwill ASSETS 20500000

I have the following expression, however I can't seem to add a capturing group in the 5th group to capture either the digit or additional white space, and then the 6th group capturing the digit or nothing.

(\d{4,6})\s*(All)\s(.*)(ASSETS|LIABILITY|OWNERS EQ|INCOME|EXPENSE|BROKER)\s*(\d*)

Any help would be much appreciated

Thanks

Regex

Parse

Accepted answers

davidskaife

Hi @FECL3

This is horrible code, but, try this:

(\d{4,6})\s*(All)\s(.*)(ASSETS|LIABILITY|OWNERSEQ|INCOME|EXPENSE|BROKER)\s{23,27}(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)\s*(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)

It seems to work on the sample you sent, but i've had to make some assuptions based on the number of whitespace you have after the ASSETS/LIABILITIES group.

You can define an upper and lower limits in Regex, to account for variations in whitespace. So what i did was find the number for each row using

REGEX_CountMatches([Field1], '\s')

which returns this:

I plug this into the Regex code you provided and replace the \s+ with \s{23,27} just before the numerical regex code to account for the min/max value before the first set of numerical data (these upper/lower values may need to be changed depending on your whole dataset). Subsequently i repeat the same batch of Regex to find the 6th field but this time i leave the separator as \s*

I also added in |\s* at the end of the numerical code as both field 5 or field 6 can be whitespace.

(\d{4,6})
\s*(All)
\s(.*)
(ASSETS|LIABILITY|OWNERSEQ|INCOME|EXPENSE|BROKER)
\s{23,27}(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)
\s*(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)

There is probably a way of making this a bit tidier...but you do appear to get the right output:

regex exmple data workflow.yxmd

All comments

ShankerV

Hi @FECL3

Please check are you expecting the result like this.

Many thanks

Shanker V

binu_acs

@FECL3 One way of doing this

Parse_013123.yxmd

Raj

i think this is not what he is looking for

as much i understood he is looking for this

FECL3

Hi - thanks for your response

As mentioned I need to parse the data and not use text to columns. The data above is only example and I can't use a space as a delimiter.

The result should be as follows (either Field 5 or Field 6 being a digit or Null (or empty / or otherwise))

davidskaife

Hi @FECL3

Here you go, try this Regex:

(\d{4,6})\s*(All)\s(.*)(ASSETS|LIABILITY|OWNERS EQ|INCOME|EXPENSE|BROKER)\s(\d*|\s*)(\s+\d*|\s*)

I've stuck a Data Cleansing tool on to remove the duplicate whitespace and you get this:

Regex.yxmd

FECL3

Thanks @davidskaife - very useful however I fear I have oversimplified the data. When I add this to my workflow it doesn't work because there is more than one space character (\s) between the 5th and 6th capturing group.

The data actually looks like this and as you can see there is multiple white space characters between "Assets" and the value. However the number of whitespace characters differ (this number also differs across the rest of my data set so can't define a value) depending on whether I want it in the 5th or 6th capturing group.

To hopefully add some more context I have added a snip from Regex101. The yellow highlighted section should come through as the 5th capturing group, whilst the proceeding number should be in the 6th capturing group.

Raj

can you share 5 line of un simplified data , so we can try new logic for same

davidskaife

Hi @FECL3

Ok try this:

(\d{4,6})\s*(All)\s(.*)(ASSETS|LIABILITY|OWNERS EQ|INCOME|EXPENSE|BROKER)\s(\d*|\s+)\s(\s+\d*|\s*)

However i suspect that if there is no structure as to the number of whitespace after ASSETS it's going to be tricky to find a Regex that works in one go.

For the 5th group, will it always be 8 characters/8 whitespace?

How do you know if the final value is in the 5th or 6th based on the structure?

As @Raj said sharing a subset of your data may be helpful for us to troubleshoot for you

FECL3

Thank you for the suggestions. Please see example data below in the attached workflow.

Please note I have changed the regex to as the numbers have commas and full stops in.

(\d{4,6})\s*(All)\s(.*)(ASSETS|LIABILITY|OWNERSEQ|INCOME|EXPENSE|BROKER)\s*(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2})

The 5th group will have a range of 3 to 11 characters in.

I know the final value is in the 6th structure because it sits within a different column in the source data.

regex exmple data workflow.yxmd

davidskaife

Hi @FECL3

Which rows have the numerical data that should sit in field 6?

EDIT: Other than you knowing the data sits in field 6, is there any other way of knowing from the data? If there isn't it's going to be nigh on impossible to translate that into logic..

FECL3

They will sit in rows 2,3,4,6 and 7

It's difficult as ASSETS, EXPENSE will generally be in the 5th capturing group, LIABILITIES, OWNERSEQ, and INCOME will be in the 6th capturing group. However this is not always the case and as sometimes they aren't (for instance row 2 and row 5), hence the desire to try capture them based on their position relative to the amount of whitespace in the data.

davidskaife

Hi @FECL3

This is horrible code, but, try this:

(\d{4,6})\s*(All)\s(.*)(ASSETS|LIABILITY|OWNERSEQ|INCOME|EXPENSE|BROKER)\s{23,27}(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)\s*(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)

It seems to work on the sample you sent, but i've had to make some assuptions based on the number of whitespace you have after the ASSETS/LIABILITIES group.

You can define an upper and lower limits in Regex, to account for variations in whitespace. So what i did was find the number for each row using

REGEX_CountMatches([Field1], '\s')

which returns this:

I also added in |\s* at the end of the numerical code as both field 5 or field 6 can be whitespace.

(\d{4,6})
\s*(All)
\s(.*)
(ASSETS|LIABILITY|OWNERSEQ|INCOME|EXPENSE|BROKER)
\s{23,27}(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)
\s*(\d{1,3},\d{1,3}.\d{2}|\d{1,3}.\d{2}|\d{1,3},\d{1,3},\d{1,3}.\d{2}|\s*)

There is probably a way of making this a bit tidier...but you do appear to get the right output:

regex exmple data workflow.yxmd

FECL3

Thank you very much - a neat and useful solution. Thank you for the explanation too - I have had to edit your code slightly to fit the different permeations across the whole dataset - but it has worked well!

davidskaife

Hi @FECL3

I'm glad it works, and i learnt something new about the upper/lower parameters so it's a win all round

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703