vyperhand

CSV Read and Compare

8 posts in this topic

#1 ·  Posted (edited)

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 by vyperhand
code formatting

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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.

#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 by czardas

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now