CSV Read and Compare

[vyperhand]

I am trying to upgrade an existing script, and after some time scouring the forums and the helpfiles, I don't feel any closer to figuring it out.   I wrote a script that allows a user to input a different site number into an InputBox to connect to a remote system instead of their local system - So for example, a manager at site A can launch this script, input site B, and connect to their system instead.  he can then disconnect, re-launch, input site D and connect to their system, etc.  This is all working brilliantly.

What I would like to do now is to make sure that the user only connects to a site in the same district.  In order to enable this, I have available a .CSV of 4 columns - Site,Network, District, Region.

Therefore, I can read the local IP and from there identify the right line for the current site in the .CSV.  I would then need to read the value for district from that line and store it to a variable.  Then I would also need to find the line in the CSV where the Site# matches the value received from the input box, and write that district# to a new variable. Then, I can compare those two variables and generate an error if they do not match, or proceed if they do.

I cannot for the life of me figure out how to parse the .CSV, find the right line, and then make the comparison.  I have something very similar in .vbs, but I cannot figure out an equivalent function in AutoIT - and I would much rather have this in a compiled .exe to make it at least a tiny bit more tamper-proof.  Any help on this, even just a pointer to the right kind of function would be vastly appreciated.

Here is the extraction code in VBS that I am trying to replicate in AutoIT:

 

Set objFS = CreateObject("Scripting.FileSystemObject")
roster = "C:\bin\roster.csv"
Set objFile = objFS.OpenTextFile(roster)
Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    intLength = Len(strLine)
    intZeros = 5 - intLength
    
If InStr(strLine, strIP)> 0 Then
    strinfo = split(strLine, ",")
    siteNumA = strinfo (0)
    siteNumB = string(5 - Len(siteNumA), "0") & siteNumA
    siteIP = strinfo (1)
    siteDist = strinfo (2)
    siteReg = strinfo (3)
End If

(Note that the strIP variable was initally set earlier in the script by reading the IP of the local machine)

TL;DR - I need to compare two values from Column A by making sure the value in Column B is the same in a .CSV.

Edited September 2, 2015 by vyperhand
code formatting

[water]

In the latest version of AutoIt function _FileReadToArray allows to read CSV files into a 2D array. Comparison then should be easy.

[czardas]

If that doesn't work, I wrote a more flexible csv parser a while ago. https://www.autoitscript.com/forum/topic/155748-csvsplit/

[vyperhand]

I will investigate that CSV parser.  In regards to FileReadToArray, the challenge I have had is that the search functions such as _ArraySearch only seem to return positional information rather than the string value at the found position, and I was unable to decipher how to get the value from another column in the same row based on that information.

For the sake of clarity, let's imagine a table like this:

Site,District,Region

1,1,1

2,1,1

3,1,2

4,2,2

5,2,1

The user at site 1 inputs that they want to connect to Site 3 at a prompt.  I need to make sure that the value for District is the same for both lines before proceeding.  In this case, I would need it to evaluate to true/OK if the user at site 1 put in site 1, 2, or 3, and generate an error if they put in site 4 or 5.

[czardas]

You first need to read about multidimensional arrays and how to access the data. Look at this example: https://www.autoitscript.com/wiki/Arrays#Multi_Dimensional_Arrays

It sounds like you want to compare one cell to another to see if they are equal - I'm not sure. The syntax for that could be as follows.

If $aArray[1][1] = $aArray[3][1] Then
    ; Do something.
EndIf

 

Edited September 8, 2015 by czardas

[vyperhand]

That may be the part I am missing, czardas.  It seems like this has to be done in two steps instead of one?  I have to search for my known element, then get positional values instead of data values, and compare them that way?  I hadn't even considered that approach.  I was trying to do something similar to the VBS, where the search and the split of the line into variables were all part and parcel of the same action, and then I could manipulate the variables directly.  I'll mess with that line of attack later today.

[vyperhand]

And as I was chopping through this, I had another thought - if AutoIT doesn't have an equivalent function to what I am looking for, can it instead call out to the .VBS and somehow read back the output into a variable?  Using my original snippet of VBS in the initial question, could I execute that VBS and get back the value of strinfo(3) as a variable in my autoIT script?

If I can do that, I can just call that VBS twice - once with each search parameter - and input back the needed data to compare and take appropriate action.  I don't like having the .vbs out there - I hesitate to put anything in production that can be fubar'ed by a curious user that figured out how to open it in notepad - but if it's the only way, then that's what I'll have to do.

[czardas]

I'm not sure exactly what you want, but once you have your data stored in an array (by whatever means), here's an example of how you might proceed.

expandcollapse popup

#include <Array.au3> ; for _ArrayDisplay()
#include <MsgBoxConstants.au3> ; for MsgBox()

; once you have your data stored in an array, you can access the elements as shown
Local $aArray = _ ; underscore = continue on next line
[['Site','District','Region'], _
[1,1,1], _
[2,1,1], _
[3,1,2], _
[4,2,2], _
[5,2,1]] ; end of array assignment

; let's check the above array contents
_ArrayDisplay($aArray)

; storing the sites as variable names makes the code more flexible
Local $iSender = 1 ; any variable name will do
Local $iRecipient = 3 ; these values can be changed later whenever you want

; check for errors
If $iSender = $iRecipient Then Exit ; this is a special case - what do you want to do?
; the code below will need modification to accomodate this special case

; Search for sender and recipient
Local $iFrom, $iTo, $iCount = 0

For $i = 1 To Ubound($aArray) -1 ; Loop through the elements in the first column
    If $aArray[$i][0] = $iSender Then ; we found a match for sender
        $iFrom = $i ; store the array row index
        $iCount += 1
    ElseIf $aArray[$i][0] = $iRecipient Then ; we found a match for recipient
        $iTo = $i ; store the array row index for recipient
        $iCount += 1
    EndIf
    ; we can stop searching once we have found what we are looking for
    If $iCount = 2 Then ExitLoop
Next

; now check the whether the districts match or not
If $aArray[$iFrom][1] = $aArray[$iTo][1] Then
    MsgBox($MB_OK, '', 'Districts are the same')
Else
    MsgBox($MB_OK, '', 'Districts are not the same')
EndIf

 

Edited September 10, 2015 by czardas