Match Data

anixon · August 20, 2008

Excel has a vLookup Function which allows you to match a variable to that in a column of data and where there is a match return a value based on the offset you choose in this case lets say the offset is 1 column to the right.

For example: the data you wish to match in a lookup table is "Smith" and the value you want to extract and return is 777777 [a phone number perhaps].

For AutoIT purposes the data would be saved to a *.txt file with each line of data comma delimited.

In Excel the data it would look like this:

Column1 Column2 Column3

Brown 999999 aaaa

Jones 888888 zzzzz

Williams 555555 ccccc

Smith 777777 wwww

Kennedy 333333 eeeee

In the delimited file it would look like this:

Brown,999999,aaaa

Jones,888888,zzzzz

Williams,555555,ccccc

Smith, 777777,wwww

Kennedy,333333,eeeee

Is the correct way to approach this problem to open the text file convert the contents into an array and then read each item in the array until you get a match and then assign the value of the second element to a variable? Or is there a more elegant solution?

Ant..

SmOke_N · August 20, 2008

I did something similar not to long ago. I probably don't need the last loop, but to play it safe I used it anyway.

#include <array.au3>
Local $s_string =   "Name,Phone,Etc" & @CRLF & _
                    "Brown,999999,aaaa" & @CRLF & _
                    "Jones,888888,zzzzz" & @CRLF & _
                    "Williams,555555,ccccc" & @CRLF & _
                    "Smith,777777,wwww" & @CRLF & _
                    "Kennedy,333333,eeeee"
                    
Local $a_matches = _VLookupAut($s_string, "Jone", "Name", "Etc", True)
_ArrayDisplay($a_matches)
Local $a_matches = _VLookupAut($s_string, "Jone", 1, 2, True)
_ArrayDisplay($a_matches)

; $s_string = The delimited file passed with fileread, or just pass the file name, if it exists it will read the file itself
; $s_find_column_value = The text you want to find, can be a partial match
; $v_column = The column header Name or the column header number you want to search
; $v_offset = The column header Name or The column number (left are negative numbers and right are postive numbers) you want to search, this value cannot be zero, 
;    and cannot be the same number negative as the column (if $v_column integer value = 3, $i_column cannot be -3)
; $f_partial_match = Default is exact match (False/0), use True/1 to do a partial match.
; $f_escape_chars = Default = True, meaning it will escape any regular expression escape chars.  If you want to use a regular expression match:
;    Then $f_partial_match should be False, $f_escape_chars should be true, and your $s_find_column_value should have the regular expression you want to use eg.. .*?br\w+n for a match on brown
; $v_casesensitive = Default is caseless search, anything other than -1, "", or Default will make it a case sensitive search for the $s_find_column_value
; $s_delim = The delimeter you are using in that file, all delimeters are escaped for regular expression use
; Return value is an array, [0] = Total number of matches found / [n+] = matched values

Func _VLookupAut($s_string, $s_find_column_value, $v_column, $v_offset, $f_partial_match = False, $f_escape_chars = True, $v_casesensitive = -1, $s_delim = ",")
    If $v_casesensitive = -1 Or $v_casesensitive = Default Or Not $v_casesensitive Then
        $v_casesensitive = "(?i)"
    Else
        $v_casesensitive = ""
    EndIf
    
    Local $s_partial_match = ""
    If $f_partial_match Then $s_partial_match = ".*?"
    
    If FileExists($s_string) Then $s_string = FileRead($s_string)
    
    Local $a_split = StringSplit(StringStripCR($s_string), @LF)
    Local $a_column, $i_start_row = 1, $i_column = 0, $i_offset = 0
    
    ; If an integer wasn't passed, then get the integer value of the column header name
    If IsString($v_column) Or IsString($v_offset) Then 
        $a_column = StringRegExp($a_split[1], $v_casesensitive & "(?:(""[." & $s_delim & "]+""|.*?))(?:\z|" & $s_delim & ")", 3)
    EndIf
    
    If IsString($v_column) Then
        For $i = 0 To UBound($a_column) - 1
            If $v_column = $a_column[$i] Then
                $i_column = $i + 1
                $i_start_row = 2
                ExitLoop
            EndIf
        Next
        If $i_column = 0 Then
            If StringIsInt($v_column) = 0 Then Return SetError(1, 0, 0) ; No column specified or found
            $i_column = Int($i_column)
        EndIf
    Else
        $i_column = $v_column
    EndIf
    
    If IsString($v_offset) Then
        For $i = 0 To UBound($a_column) - 1
            If $v_offset = $a_column[$i] Then
                $i_offset = $i + 1
                ExitLoop
            EndIf
        Next
        If $i_offset = 0 Then
            If StringIsInt($v_offset) = 0 Then Return SetError(1, 0, 0) ; No column specified or found
            $i_offset = Int($i_offset)
        Else
            $i_offset -= $i_column
        EndIf
    Else
        $i_offset = $v_offset
    EndIf
    
    ;Escape the escape chars
    Local $s_pattern_escapechars = "(\.|\||\*|\?|\+|\(|\)|\{|\}|\[|\]|\^|\$|\\)"
    If $f_escape_chars Then
        $s_find = StringRegExpReplace($s_find_column_value, $s_pattern_escapechars, "\\\1")
    EndIf
    
    $s_delim = StringRegExpReplace($s_delim, $s_pattern_escapechars, "\\\1")
   
    ;How many delimeters
    Local $n_delimeters = UBound(StringRegExp($a_split[1], "(" & $s_delim & ")", 3))
    
    If $n_delimeters = 0 Then Return SetError(1, 0, 0)
    If $n_delimeters < ($i_offset - 1) Or $i_offset = 0 Or $i_offset = -$i_column Then Return SetError(2, 0, 0)
    If $a_split[0] < $i_column Or $i_column = 0 Then Return SetError(3, 0, 0)
    
    Local $n_total_count = $i_column + $i_offset
    Local $n_ubound = $n_total_count
    If $i_offset < 0 Then $n_ubound = $i_column
    
    ; Prepare the regex pattern
    Local $s_pattern_findvalue = $v_casesensitive, $i
    For $i = 1 To $n_ubound
        If $i_column = $i Then
            If $i_offset < 0 Then
                If $n_total_count = $i Then
                    $s_pattern_findvalue &= "(.*?)" & $s_delim & $s_partial_match & $s_find & $s_partial_match
                Else
                    $s_pattern_findvalue &= $s_partial_match & $s_find & $s_partial_match
                EndIf
            Else
                $s_pattern_findvalue &= $s_partial_match & $s_find & $s_partial_match & $s_delim
            EndIf
        ElseIf $n_total_count = $i Then
            If $i_offset < 0 Then
                $s_pattern_findvalue &= "(.*?)" & $s_delim
            Else
                $s_pattern_findvalue &= "(.*?)"
            EndIf
        Else
            $s_pattern_findvalue &= "(?:""[." & $s_delim & "]+""|.*?)" & $s_delim
        EndIf
    Next
    
    $s_pattern_findvalue &= "(?:\z|" & $s_delim & ")"
    
    ; Could be done with 1 line of regex, but [0] wouldn't hold the array ubound value.
    ; Also, splitting it up since we already did stringsplit, will prove to be pretty fast anyway without a large string to search
    Local $a_match[$a_split[0] + 1], $i_add, $a_sre
    For $i = $i_start_row To $a_split[0]
        $a_sre = StringRegExp($a_split[$i], $s_pattern_findvalue, 1)
        If IsArray($a_sre) Then
            $i_add += 1
            $a_match[$i_add] = $a_sre[0]
        EndIf
    Next
   
    If Not $i_add Then Return SetError(4, 0, 0)
   
    ReDim $a_match[$i_add + 1]
    $a_match[0] = $i_add
    Return $a_match
EndFunc

Edit:

Forgot to add partial match in it, which was my original intentions when doing ti with regular expressions, also added a param for escape regex chars, just in case you want to use your own pattern for the search.

Edit2:

Dale enlightened me on vLookup, hopefully this is closer.

Edited August 20, 2008 by SmOke_N

SmOke_N · August 20, 2008

I had to edit the above because I was not informed on how vLookup worked. It went pretty in depth, I like the concept (even though I had never heard of it before).

anixon · August 20, 2008

I had to edit the above because I was not informed on how vLookup worked. It went pretty in depth, I like the concept (even though I had never heard of it before).

Wow what else can I say. Perhaps you might like to know the purpose for which it is intended. I have written a process which monitors the health of my desktop to ensure Skype is always active. When I am out and about my application processes any missed Skype calls and turns them into an email which is sent to an email to sms service provider. I end up with the sms on my mobile which has details of who called. This system is to exploit the pricing difference between Skype call forwarding and the cost of the sms based on the mobile phone plan which allows some calls to be made free. To avoid an sms for each and every missed call the routine required to have a list of callers who qualify for an sms which allows the routine to run. And just as an aside you can switch the sms processing off remotely by sending the desktop an task specific email. Thanks for your help which is very much appreciated. Ant..

SmOke_N · August 20, 2008

Wow what else can I say. Perhaps you might like to know the purpose for which it is intended. I have written a process which monitors the health of my desktop to ensure Skype is always active. When I am out and about my application processes any missed Skype calls and turns them into an email which is sent to an email to sms service provider. I end up with the sms on my mobile which has details of who called. This system is to exploit the pricing difference between Skype call forwarding and the cost of the sms based on the mobile phone plan which allows some calls to be made free. To avoid an sms for each and every missed call the routine required to have a list of callers who qualify for an sms which allows the routine to run. And just as an aside you can switch the sms processing off remotely by sending the desktop an task specific email. Thanks for your help which is very much appreciated. Ant..

NP, it was enjoyable... hope it works out well for you.

crystalburner · August 20, 2008

NP, it was enjoyable... hope it works out well for you.

This is truly incredible. I do not wish to sound dramatic but this is one of the most innovative things I have seen since they squeezed 16 bits out of an 8 bit processor with the first IBM 8086 now called x86 generation of PC computers.

I worked with Natiyamo Nakitasho in the 80's on project the abysss later called mineshaft and to hit the streets as an arcade "Dig Dug". Those of us a little older may remember.

It was an impossibility, we had 2 x 1024 Kilo BIT chips to work with, barely enough to load the compiler code into to begin with and we "stole" memory usually reserved for video by interlacing the graphics and therefore effectively doubling the bandwidth and therefore swappable and free memory for game code.

With less than 5 k we achieved what Nintendo only finally managed in 1983 with the release of the basic mario game that was similar to bubble bobble.

Anyway, waffle and blah and completely irrelivent but just an old timer who would like to point out something special, this is, remarkable code.

Well done Smoke_N, whoever you are, I take off my hat.

C

anixon · August 20, 2008

This is truly incredible. I do not wish to sound dramatic but this is one of the most innovative things I have seen since they squeezed 16 bits out of an 8 bit processor with the first IBM 8086 now called x86 generation of PC computers.

I worked with Natiyamo Nakitasho in the 80's on project the abysss later called mineshaft and to hit the streets as an arcade "Dig Dug". Those of us a little older may remember.

It was an impossibility, we had 2 x 1024 Kilo BIT chips to work with, barely enough to load the compiler code into to begin with and we "stole" memory usually reserved for video by interlacing the graphics and therefore effectively doubling the bandwidth and therefore swappable and free memory for game code.

With less than 5 k we achieved what Nintendo only finally managed in 1983 with the release of the basic mario game that was similar to bubble bobble.

Anyway, waffle and blah and completely irrelivent but just an old timer who would like to point out something special, this is, remarkable code.

Well done Smoke_N, whoever you are, I take off my hat.

C

I'm with you crystalburner AutoIT subscribers and supporters are nothing short of fantastic the way that they are prepared to help never ceases to amaze me. You get excellent help when you have had a go and may have got to a sticking point [perhaps something not covered by the available documentation or in a previous forum topic]. Now if I was to date myself IT wise I can remember my first Mainframe [DataGeneral] had two hard drives that looked like washing machines and the disc pack was loaded from the top and the disc's look like a platter of 78 records [you know the ones that were played by HMV or was it the Dog with a metal needle]. Not long after than a guy in the UK called Sinclair released what was claimed to be a home computers [a "Z" something] about the same time as that Tandy released what became affectionately known as the Trash 80 then came along the Commodore. Now I can fit the world on something I can put in my pocket. What ever next.......... I just love IT if you think it can be done it can or has been done. Ant...

Malkey · August 21, 2008

A work around for using a zero v-offset - should the need arise..

That is, to search and return the results of a partial match within one column of data.

An example - using SmOke_N's _VLookupAut( )

#include <array.au3>
Local $s_string = "Name,Phone,Etc" & @CRLF & _
        "Brown,999999,aaaa" & @CRLF & _
        "Jones,888888,zzzzz" & @CRLF & _
        "Williams,555555,ccccc" & @CRLF & _
        "Smith,777777,wwww" & @CRLF & _
        "Kennedy,333333,eeeee"
; http://www.autoitscript.com/forum/index.php?s=&showtopic=78711&view=findpost&p=568161
;The equivalent of zero v-offset
Local $aList
Local $a_matches = _VLookupAut($s_string, "s", 1, 1, True) 
Dim $aRes[$a_matches[0] + 1]
$aRes[0] = $a_matches[0]
For $x = 1 To $a_matches[0]
    $aList = _VLookupAut($s_string, $a_matches[$x], 2, -1, False) ;Exact match
    $aRes[$x] = $aList[1]
Next
_ArrayDisplay($aRes)

; $s_string = The delimited file passed with fileread, or just pass the file name, if it exists it will read the file itself
; $s_find_column_value = The text you want to find, can be a partial match
; $v_column = The column header Name or the column header number you want to search
; $v_offset = The column header Name or The column number (left are negative numbers and right are postive numbers) you want to search, this value cannot be zero,
;    and cannot be the same number negative as the column (if $v_column integer value = 3, $i_column cannot be -3)
; $f_partial_match = Default is exact match (False/0), use True/1 to do a partial match.
; $f_escape_chars = Default = True, meaning it will escape any regular expression escape chars.  If you want to use a regular expression match:
;    Then $f_partial_match should be False, $f_escape_chars should be true, and your $s_find_column_value should have the regular expression you want to use eg.. .*?br\w+n for a match on brown
; $v_casesensitive = Default is caseless search, anything other than -1, "", or Default will make it a case sensitive search for the $s_find_column_value
; $s_delim = The delimeter you are using in that file, all delimeters are escaped for regular expression use
; Return value is an array, [0] = Total number of matches found / [n+] = matched values

Func _VLookupAut($s_string, $s_find_column_value, $v_column, $v_offset, $f_partial_match = False, $f_escape_chars = True, $v_casesensitive = -1, $s_delim = ",")
    If $v_casesensitive = -1 Or $v_casesensitive = Default Or Not $v_casesensitive Then
        $v_casesensitive = "(?i)"
    Else
        $v_casesensitive = ""
    EndIf

    Local $s_partial_match = ""
    If $f_partial_match Then $s_partial_match = ".*?"

    If FileExists($s_string) Then $s_string = FileRead($s_string)

    Local $a_split = StringSplit(StringStripCR($s_string), @LF)
    Local $a_column, $i_start_row = 1, $i_column = 0, $i_offset = 0

    ; If an integer wasn't passed, then get the integer value of the column header name
    If IsString($v_column) Or IsString($v_offset) Then
        $a_column = StringRegExp($a_split[1], $v_casesensitive & "(?sad.gif""[." & $s_delim & "]+""|.*?))(?:\z|" & $s_delim & ")", 3)
    EndIf

    If IsString($v_column) Then
        For $i = 0 To UBound($a_column) - 1
            If $v_column = $a_column[$i] Then
                $i_column = $i + 1
                $i_start_row = 2
                ExitLoop
            EndIf
        Next
        If $i_column = 0 Then
            If StringIsInt($v_column) = 0 Then Return SetError(1, 0, 0) ; No column specified or found
            $i_column = Int($i_column)
        EndIf
    Else
        $i_column = $v_column
    EndIf

    If IsString($v_offset) Then
        For $i = 0 To UBound($a_column) - 1
            If $v_offset = $a_column[$i] Then
                $i_offset = $i + 1
                ExitLoop
            EndIf
        Next
        If $i_offset = 0 Then
            If StringIsInt($v_offset) = 0 Then Return SetError(1, 0, 0) ; No column specified or found
            $i_offset = Int($i_offset)
        Else
            $i_offset -= $i_column
        EndIf
    Else
        $i_offset = $v_offset
    EndIf

    ;Escape the escape chars
    Local $s_pattern_escapechars = "(\.|\||\*|\?|\+|\(|\)|\{|\}|\[|\]|\^|\$|\\)"
    If $f_escape_chars Then
        $s_find = StringRegExpReplace($s_find_column_value, $s_pattern_escapechars, "\\\1")
    EndIf

    $s_delim = StringRegExpReplace($s_delim, $s_pattern_escapechars, "\\\1")

    ;How many delimeters
    Local $n_delimeters = UBound(StringRegExp($a_split[1], "(" & $s_delim & ")", 3))

    If $n_delimeters = 0 Then Return SetError(1, 0, 0)
    If $n_delimeters < ($i_offset - 1) Or $i_offset = 0 Or $i_offset = -$i_column Then Return SetError(2, 0, 0)
    If $a_split[0] < $i_column Or $i_column = 0 Then Return SetError(3, 0, 0)

    Local $n_total_count = $i_column + $i_offset
    Local $n_ubound = $n_total_count
    If $i_offset < 0 Then $n_ubound = $i_column

    ; Prepare the regex pattern
    Local $s_pattern_findvalue = $v_casesensitive, $i
    For $i = 1 To $n_ubound
        If $i_column = $i Then
            If $i_offset < 0 Then
                If $n_total_count = $i Then
                    $s_pattern_findvalue &= "(.*?)" & $s_delim & $s_partial_match & $s_find & $s_partial_match
                Else
                    $s_pattern_findvalue &= $s_partial_match & $s_find & $s_partial_match
                EndIf
            Else
                $s_pattern_findvalue &= $s_partial_match & $s_find & $s_partial_match & $s_delim
            EndIf
        ElseIf $n_total_count = $i Then
            If $i_offset < 0 Then
                $s_pattern_findvalue &= "(.*?)" & $s_delim
            Else
                $s_pattern_findvalue &= "(.*?)"
            EndIf
        Else
            $s_pattern_findvalue &= "(?:""[." & $s_delim & "]+""|.*?)" & $s_delim
        EndIf
    Next

    $s_pattern_findvalue &= "(?:\z|" & $s_delim & ")"

    ; Could be done with 1 line of regex, but [0] wouldn't hold the array ubound value.
    ; Also, splitting it up since we already did stringsplit, will prove to be pretty fast anyway without a large string to search
    Local $a_match[$a_split[0] + 1], $i_add, $a_sre
    For $i = $i_start_row To $a_split[0]
        $a_sre = StringRegExp($a_split[$i], $s_pattern_findvalue, 1)
        If IsArray($a_sre) Then
            $i_add += 1
            $a_match[$i_add] = $a_sre[0]
        EndIf
    Next

    If Not $i_add Then Return SetError(4, 0, 0)

    ReDim $a_match[$i_add + 1]
    $a_match[0] = $i_add
    Return $a_match
EndFunc   ;==>_VLookupAut

SmOke_N · August 21, 2008

I didn't do zero offset, because it would be the $s_find_column_value you passed.

Sign In

Match Data

Recommended Posts

anixon

Link to comment

Share on other sites

SmOke_N

Link to comment

Share on other sites

SmOke_N

Link to comment

Share on other sites

anixon

Link to comment

Share on other sites

SmOke_N

Link to comment

Share on other sites

crystalburner

Link to comment

Share on other sites

anixon

Link to comment

Share on other sites

Malkey

Link to comment

Share on other sites

SmOke_N

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta