Search for string in text file.

[jrmm]

I love Autoit, but struggling to find the best way to do this.

I have 2 text files....  File1 is Tab Separated and File2 is CSV.  I am attempting to read in a line from File1 and split it, then search the contents of File2 for one of the substrings from that split line.    If I find a match, then I want to split the matching line and grab needed info from it to complete the record and output the combined record to a file.

 

Example line from File1:

    17   123456789     learned          10800     bridging      1/13

Example line from File2:

172.25.17.103,computername.domain,Reservation (active),DHCP,123456789,,Full Access,N/A

From File1, I grab the 2nd column (MAC Address) and search for that in the 2nd file (Column5)

 

I really thought reading file into an array and doing an _arraysearch was the way to go, but that seems to require searching for the whole string, not a sub-string.

What I have been trying to do so far is read in file1 one line at a time, split the string, then read in file 2 one line at a time and check for a matching substring..... handle that.   Then back to file 1 to repeat the process.   This seems so clunky.

So I would love suggestions on what functions might work best.

Thanks so much for any feedback.

- John

[kylomas]

Jrmm,

Can you post examples of the files...20 or so linesof each.  How large are the files on average?

Kylomas

Edit: is the search argument always the second delimited value from file one?

Edited January 14, 2016 by kylomas
question

[jdelaney]

Here is a small example:

#include <Array.au3>
Local $aFile1
Local $aFile1
; this will be populated from _FileReadToArray("yourfile.txt",$aFile1,0)...i'm just populating an array since I don't have the file
Local $aFile1[3] = ["17 123456789   learned     10800   bridging    1/13","18   123456780   learned     10800   bridging    1/13","18   123456783   learned     10800   bridging    1/13"]

; this will be populated from _FileReadToArray("yoursecondfile.txt",$aFile1,0)...i'm just populating an array since I don't have the file
Local $aFile2[2] = ["172.25.17.103,computername.domain,Reservation (active),DHCP,123456789,,Full Access,N/A","172.25.17.104,computername.domain,Reservation (active),DHCP,123456780,,Full Access,N/A"]

; split up the array
For $i = 0 To UBound($aFile1)-1
    $aFile1[$i] = StringSplit($aFile1[$i],@TAB,2)
Next

; split up the array
For $i = 0 To UBound($aFile2)-1
    $aFile2[$i] = StringSplit($aFile2[$i],",",2)
Next

; find matches
For $i = 0 To UBound($aFile1)-1
    $aTemp = $aFile1[$i]
    For $j = 0 To UBound($aFile2)-1
        $aTemp2 = $aFile2[$j]
        If $aTemp[1] = $aTemp2[4] Then
            ConsoleWrite("Match for $sID=[" & $aTemp[1] & "] found in second file...=[" & _ArrayToString($aTemp2) & "]" & @CRLF)
            ExitLoop
        EndIf
    Next
    If $j=UBound($aFile2) Then
        ConsoleWrite("NO Match for $sID=[" & $aTemp[1] & "] found in second file..." & @CRLF)
    EndIf
Next

 

output:

Match for $sID=[123456789] found in second file...=[172.25.17.103|computername.domain|Reservation (active)|DHCP|123456789||Full Access|N/A]
Match for $sID=[123456780] found in second file...=[172.25.17.104|computername.domain|Reservation (active)|DHCP|123456780||Full Access|N/A]
NO Match for $sID=[123456783] found in second file...

As expected, the third item returns nothing, because it wsa not included in the second 'file'

You could also go a regexp route, which would be simpler....although less reliable, since I'm doing broad assumptions on a very simple regexp.

Local $aFile1
Local $aFile1
; this will be populated from _FileReadToArray("yourfile.txt",$aFile1,0)...i'm just populating an array since I don't have the file
Local $aFile1[3] = ["17 123456789   learned     10800   bridging    1/13","18   123456780   learned     10800   bridging    1/13","18   123456783   learned     10800   bridging    1/13"]

; this will be populated from FileRead("yoursecondfile.txt")...
Local $aFile2="172.25.17.103,computername.domain,Reservation (active),DHCP,123456789,,Full Access,N/A" & @CRLF &  _
"172.25.17.104,computername.domain,Reservation (active),DHCP,123456780,,Full Access,N/A"

; split up the array
For $i = 0 To UBound($aFile1)-1
    $aFile1[$i] = StringSplit($aFile1[$i],@TAB,2)
Next

; find matches
For $i = 0 To UBound($aFile1)-1
    $aTemp = $aFile1[$i]
    $aFound = StringRegExp($aFile2,"(.*" & $aTemp[1] & ".*)",1)
    If IsArray($aFound) Then
        For $j = 0 to UBound($aFound)-1
            ConsoleWrite("Match for $sID=[" & $aTemp[1] & "] found in second file...=[" & $aFound[$j] & "]" & @CRLF)
        Next
    Else
        ConsoleWrite("NO Match for $sID=[" & $aTemp[1] & "] found in second file..." & @CRLF)
    EndIf
Next

 

Edited January 14, 2016 by jdelaney

[InunoTaishou]

Here was an interesting topic that was discussed for a few days about StringInStr with files. The original poster was trying to get it to work as fast as possible but maybe you could find some enlightenment in the topic.

 

[jrmm]

Thanks so much for the suggestions.   I am very grateful for the help with this.  

 

@jdelaney, I like your first example and think it will do the trick.    You do things in the code I have never done, so I will have to dig into this tonight or over the weekend.  

Running the first example as is, I get this error...  

 (25) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

If $aTemp[1] = $aTemp2[4] Then
If ^ ERROR

Pointing the script to  the files I get the same error.....    

I'll have to wrap my heard around this better after work.

- John

[jdelaney]

The script was assuming that every line in each file is 'valid', meaning that it  can be broken down into at least a certain amount of values in the stringsplit.

Since that appears to not be the case, you need to check that the bounds of the temp array are valid; if not do a continueloop.

Edited January 15, 2016 by jdelaney

[jrmm]

@jdelaney
Thanks again....  I found the issue.  The first file wasn't really tab delimited as I thought... It was using a bunch of spaces instead of tabs.  

I modified the section where the first split happens to look like below and now it rolling along smoothly....  

; split up the array
For $i = 0 To UBound($aFile1)-1
    $aFile1[$i] = StringReplace($aFile1[$i],":","")  ;Takes colons out of MAC address to match format of other file.
    do
    $aFile1[$i] = StringReplace($aFile1[$i],"  "," ") ;Replaces multiple spaces with a single space
    Local $iReplacements = @extended
    until $iReplacements = 0
    $aFile1[$i] = StringReplace($aFile1[$i]," ",",") ;Replaces single space with coma
    $aFile1[$i] = StringSplit($aFile1[$i],",",2)  ;Split CSV
 Next

The script is working well.  Thank you so much.  I definitely learned some new tricks and sure appreciate the help.

- John