anixon Posted August 20, 2008 Posted 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..
Moderators SmOke_N Posted August 20, 2008 Moderators Posted August 20, 2008 (edited) 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.expandcollapse popup#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 EndFuncEdit: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 Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.
Moderators SmOke_N Posted August 20, 2008 Moderators Posted 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). Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.
anixon Posted August 20, 2008 Author Posted 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..
Moderators SmOke_N Posted August 20, 2008 Moderators Posted 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. Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.
crystalburner Posted August 20, 2008 Posted 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 Posted August 20, 2008 Author Posted 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 Posted August 21, 2008 Posted 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( ) expandcollapse popup#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
Moderators SmOke_N Posted August 21, 2008 Moderators Posted August 21, 2008 I didn't do zero offset, because it would be the $s_find_column_value you passed. Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now