LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only
Back to discussions
Expand all | Collapse all

Extracting a number from a cell

  • 1.  Extracting a number from a cell

    Employee
    Posted 01-13-2016 07:09

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    Hello, Everyone-

    I am relatively new to the Lavastorm scene and am having difficulties reconstructing an integral part of an excel project I have maintained for sometime in Lavastorm. The gist of the problem is that I have column of data has multiple strings and integers in it. I need to pull out the largest whole number string from the cell for use in a cross reference later. The problem is that it doesn't always fall in the same location, and it isn't always in between the same identifiers. Examples Below:


    In each example I need to copy the 123456 (In reality the 123456 is a different whole number ranging in length from 3-7 digits) into a different column:
    ASDFASDF 125-A5 123456(a)
    asd 125a5 123456 (A)
    ASDF 123456 as5a5-0 (B)

    I would appreciate any help you could provide. Thanks!!


  • 2.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 07:48

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    Hi, Andrew! Good to see you on the forums. This is a cool problem. I think the best way is to use regular expressions (or regex). You can match 3-7 consecutive digits. The problem then is that that will also match 125. What I've done in the attachment is look for 3-7 consecutive digits sandwiched between spaces and parentheses. See if that works.
    Attachments:
    digitMatch.brg


  • 3.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 08:06

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    Hi, Roger! Good to hear from you! Thanks for the quick response- one that I would love to try, except that the attachment does not save correctly for me. It comes off as "attachment.php" and will not open. Thoughts?


  • 4.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 08:14

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    Odd.... Try copying and pasting the following onto the BRE canvas.

    node:37_digits_between_spaces
bretype:core::Filter
editor:Label=3-7 digits between spaces
editor:sortkey=56965d4a4d834e72
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_digits = regexMatch(Data,"[\\s)][0-9]{3,7}[\\s(]")[0][0]
_digits = _digits.replace("(","").replace(")","").trim()


emit *,_digits

EOX
editor:XY=340,270
end:37_digits_between_spaces

node:Static_Data
bretype:core::Static Data
editor:sortkey=56965d3a203d0470
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Data
ASDFASDF 125-A5 123456(a) 
asd 125a5 123456 (A)
ASDF 123456 as5a5-0 (B)
EOX
editor:XY=190,180
end:Static_Data

node:37_digits
bretype:core::Filter
editor:Label=3-7 digits
editor:sortkey=56965d4a4d834e72_2
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_digits = regexMatch(Data,"[0-9]{3,7}").str()


emit *,_digits

EOX
editor:XY=340,180
end:37_digits


  • 5.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 08:30

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    OK- so I have attached the two nodes to the data and run them after changing the 'data' name to match my own. I am receiving errors on both nodes. The 3-7 digits between spaces node brainscriptVariable.cpp.53 Operator: 'replace', as well as a node execution terminated while processing data by error: value of variable not agreeing with compiled type: Unicode vs. string.

    The second node, "Error while executing pattern: -10" brain.node.JRegex.cpp.147

    Any of this make any sense to you?


  • 6.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 08:33

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    Further analysis shows there was a new data type added that has a hyphen at the end of the number sequence, probably causing the pattern to be thrown off.

    Example is

    ASDFDS 123456-CCTV (H)


  • 7.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 09:08

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    Ah, so you have unicode coming in. I've updated the filters to convert it to string. But you have another problem, as you stated with the 123456-CCTV. So I've changed the logic now to replace any non-alphanumeric data with spaces. But the problem now is that 125-A5 will match (the '-' becomes a space and so ' 125 ' matches my pattern. Not sure how you want to handle this. Perhaps '-CCTV' is a special case and you can replace that with a string (but handle all other -'s separately).

    node:Static_Data
bretype:core::Static Data
editor:sortkey=56965d3a203d0470
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Data:unicode
ASDFASDF 125-A5 123456(a) 
asd 125a5 123456 (A)
ASDF 123456 as5a5-0 (B)
ASDFDS 123456-CCTV (H)
EOX
editor:XY=190,180
end:Static_Data

node:Replace_nonalphanumerics_with_spaces
bretype:core::Filter
editor:Label=Replace non-alphanumerics with spaces
editor:sortkey=569670d571d907f8
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_origData = Data.str()
_Data = _origData.regexSubstitute("[^0-9A-Za-z]", " ") 

emit _origData, _Data

EOX
editor:XY=340,180
end:Replace_nonalphanumerics_with_spaces

node:37_digits_between_spaces
bretype:core::Filter
editor:Label=3-7 digits between spaces
editor:sortkey=56965d4a4d834e72
input:@40fd2c74167f1ca2/=Replace_nonalphanumerics_with_spaces.40fd2c7420761db6
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_digitsList = _Data.regexMatch("\\s[0-9]{3,7}\\s")

_digits = _digitsList.str().regexSubstitute("[{ }]","")

emit *,_digits

EOX
editor:XY=470,180
end:37_digits_between_spaces


  • 8.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 09:27

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    So, that worked to a degree. For some reason there were several odd outputs where it picked out different number strings and output them both, separated by a semicolon. Example below.

    Original Output Ideal Output
    Caf� 300 - 21152(M) 300;21152 21152
    Caf� 330 26997(A) 330;26997 26997
    BAC 135 el bellon 30876(A) 135;30876 30876
    ASDFDAS ASDF Ctr2728(A) BLANK 2728


    It may be ideal to use the X-Reference list that I have and use it to search in the string the data I am trying to extract from....is that possible?


  • 9.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 11:42

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    Originally posted by: ahines
					

    So, that worked to a degree.  For some reason there were several odd outputs where it picked out different number strings and output them both, separated by a semicolon.  Example below.

    
Original //                       Output   //               Ideal Output
    
Caf� 300 - 21152(M)   //   300;21152       //     21152
    
Caf� 330 26997(A)    //     330;26997      //      26997
    
BAC 135 el bellon 30876(A) // 135;30876   //    30876
    
ASDFDAS ASDF Ctr2728(A) //   BLANK    //        2728


    
It may be ideal to use the X-Reference list that I have and use it to search in the string the data I am trying to extract from....is that possible?

    Update to for better viewing. I'm not very proficient at forum posting. Sorry.


  • 10.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 13:56

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    So the reason why you were seeing 300;21152 is due to the problem I mentioned earlier. Since you are looking for 3-7 digits, both 300 and 21152 satisfy that. As you suggested, comparing against a master list of valid reference numbers would be ideal.

    See the attached data-flow. Also copied code here:

    node:Data
bretype:core::Static Data
editor:Label=Data
editor:sortkey=56965d3a203d0470
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Data:unicode
ASDFASDF 125-A5 123456(a) 
asd 125a5 123456 (A)
ASDF 123456 as5a5-0 (B)
ASDFDS 123456-CCTV (H)
Caf� 300 - 21111(M)
Caf� 330 26666(A)
BAC 135 el bellon 30876(A)
ASDFDAS ASDF Ctr2777(A)
EOX
editor:XY=190,180
end:Data

node:Replace_nonalphanumerics_with_spaces
bretype:core::Filter
editor:Label=Replace non-alphanumerics with spaces
editor:sortkey=569670d571d907f8
input:@40fd2c74167f1ca2/=Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_origData = Data.str()
_Data = _origData.regexSubstitute("[^0-9A-Za-z]", " ") 

emit _origData, _Data

EOX
editor:XY=340,180
end:Replace_nonalphanumerics_with_spaces

node:37_digits_between_spaces_2
bretype:core::Filter
editor:Label=3-7 digits between spaces
editor:sortkey=56965d4a4d834e72_2
input:@40fd2c74167f1ca2/=Replace_nonalphanumerics_with_spaces.40fd2c7420761db6
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_digitsList = _Data.regexMatch("[0-9]{3,7}")

_3to7digits = _digitsList.str().regexSubstitute("[{ }]","")

emit *,_3to7digits

EOX
editor:XY=490,180
end:37_digits_between_spaces_2

node:Expand_From_List
bretype:core::Expand From List
editor:sortkey=5696b93855b70458
input:@3ef3761c60777673/=37_digits_between_spaces_2.40fd2c7420761db6
output:@3ed3a410684c59b3/=
prop:Delimiter=";"
prop:ItemField=Center
prop:ListExpr=_3to7digits
editor:XY=600,180
end:Expand_From_List

node:Lookup
bretype:core::Lookup
editor:sortkey=5696b96559aa3fc1
input:@40fd2c746abc6dc7/=Expand_From_List.3ed3a410684c59b3
input:@40fd2c74486e4494/=Remove_Duplicates.40fd2c746a2a3b47
output:@40fd2c7445835585/=
prop:InputKey=<<EOX
Center
EOX
prop:LookupKey=<<EOX
ValidCenters
EOX
prop:Script=<<EOX
emit 1:*

where matchIsFound

# Note: If you want to avoid collisions with fields on the inputs
# prefix the second emit statement with the keyword "default"
# or "override".


EOX
editor:XY=660,310
end:Lookup

node:Remove_Duplicates
bretype:lal1::Remove Duplicates
editor:sortkey=5696b90f10b76491
input:@40fd2c743ebf4304/=Master_List.40fe6c55598828e5
output:@40fd2c746a2a3b47/=
prop:FieldListExpr=<<EOX
ValidCenters
EOX
editor:XY=340,320
end:Remove_Duplicates

node:Master_List
bretype:core::Static Data
editor:Label=Master List
editor:sortkey=5696b8e76c1d4287
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ValidCenters
123456
123456
21111
26666
30876
2777

EOX
editor:XY=190,320
end:Master_List

    Attachments:
    lookupCenterNumber.brg


  • 11.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 14:14

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    Sadly, when I click on the link, it is still asking me to save "attachment.php". After this discussion, I am in agreeance that comparing a master list would be better. It may save a step as well, because the data for comparison is the data that I was needing the numbers in these strings pulled for, so that I can use the lookup node against (vlookup).


  • 12.  RE: Extracting a number from a cell

    Employee
    Posted 01-13-2016 14:23

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    Updated the previous post to include the code.


  • 13.  RE: Extracting a number from a cell

    Employee
    Posted 01-14-2016 11:29

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    Roger- I apologize that it has taken this long to get back on here. I was able to get your code to work, thought it would seem there is one last hurdle. The delimited units duplicated (1 line per option). Some of the duplications are just that, duplications of the correct unit numbers, but some we once again would need to pull the larger number out. I've used the duplicate node before, but isn't this more like a split (remove the duplicates to a separate list to remove the duplicates within that list, then add them back to the other split output, creating the whole list). I thought this project would be an easy task, but it has not been so. Thank you for your help!


  • 14.  RE: Extracting a number from a cell

    Employee
    Posted 01-14-2016 12:44

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: belliveaujd

    Here's a slightly different solution from Roger's, but still capitalizing the use of Regular Expressions:

    Paste the following into a graph. This all could've been written in one filter node, but I broke it into two nodes to make it a bit more readable.

    The first node will find all the numbers within the string (of any length). The next node loops through that list and pulls out the largest numerical value.
    NOTE: I've had the best luck converting unicode to string before using the regular expression functions.

    node:Static_Data
bretype:core::Static Data
editor:sortkey=56965d3a203d0470
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Data:unicode
ASDFASDF 125-A5 123456(a) 
asd 125a5 123456 (A)
ASDF 123456 as5a5-0 (B)
ASDFDS 123456-CCTV (H)
EOX
editor:XY=190,180
end:Static_Data

node:Find_the_numeric_string_matches_2
bretype:core::Filter
editor:Label=Find the numeric string matches
editor:sortkey=5697f2573e500cb5_2
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
# 0-9, 1 or more times until it hits a non "0-9" character
_pattern = "[0-9]+"

# regex matching seems to work better with string
_data = 'Data'.str()

# the matching numeric strings
_tmpMatchingList = _data.regexMatch(_pattern).str()

# remove the curly braces
_matchingList = _tmpMatchingList.regexSubstitute("[{}]","")

emit _matchingList.str() as listMatches
emit *
EOX
editor:XY=310,180
end:Find_the_numeric_string_matches_2

node:Find_largest_number
bretype:core::Filter
editor:Label=Find largest number
editor:sortkey=5697f22269bb0fae
input:@40fd2c74167f1ca2/=Find_the_numeric_string_matches_2.40fd2c7420761db6
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_strNumList = 'listMatches'.split(";")
# the number of string numbers found
_strNumCnt = _strNumList.len()


_i = 0
# initialize the "largest" variable
if( _strNumCnt > 0 ) then{
	_largest = _strNumList.getItem(0).int()
}
else{
	_largest = int(null)
}

# loop the list to find the largest
while( _i < _strNumCnt ){
	if( _largest.isNotNull() and _strNumList.getItem(_i).int() > _largest ) then{
		# the new "largest" number
		_largest = _strNumList.getItem(_i).int()
	}
	_i = _i + 1
}

emit _strNumCnt
emit _largest
emit *

EOX
editor:XY=430,180
end:Find_largest_number


  • 15.  RE: Extracting a number from a cell

    Employee
    Posted 01-14-2016 17:02

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ahines

    Belliveaujd- thank you so much! It works like a dream! I wish I could say I follow most of what you did, but I understand the concept. This will be something that I use several times in different projects so I will probably pick it up sooner rather than later.

    Also, thank you for the note on string conversion. On that topic, if I wanted to convert the fields to something used to manipulating decimal numbers (currency specifically), would .double() be the appropriate conversion function to use?

    Roger- Thanks for your help as well, I saved all your nodes for learning tools later! Your organization makes learning by example very easy.