Jump to content
Sign in to follow this  
jerrbert

Compare Fail

Recommended Posts

jerrbert

I wrote a script that will take a location dump from one application and turn it into a location ID used by a second application.

Turn this:  H~BR~Non-Patient~Pt Fin Svcs~Common Office 2

into this:  01ffef31-3d39-46dd-8e94-b967f0212f28

I noticed that I was getting some wrong values and began investigating. I found that the _ArrayFindAll function cannot be made to only find exact matches (at least I cannot figure it out), so when searching for 'Office 2', it also pulls in 'Common Office 2'.

There are common IDs on the data tab of the attached spreadsheet for both items and when the script picks the first ID match it finds, it's not always the right one.

I decided to add a second piece to match on, the location name.  Everything I can see shows that there are matches in name happening, but the output of the script says there are no matches.

Any help you can give would be great.

Thanks!!

#include <array.au3>
#include <File.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <ProgressConstants.au3>
#include <WindowsConstants.au3>

;***************************************************************************
;Aventura Locations Script - This script is designed to search two Excel
;   files and find out the unique location ID for a specific workstation.
;Created by:  Jerry Erickson
;Created on:  4/13/2013
;Change Log:
;            9/17/2013 je - added a piece to add printers to output
;***************************************************************************

Local $File, $FileOpen, $Line, $Delim, $LocArray, $Result, $DataArray, $PCArray, $HMC, $Search, $NextID, $Processing, $Progress1, $vMsg, $Processing2, $Progress2, $vMsg2
Local $printLastCell, $PrintArray, $LocPrinter

#Region ### START Koda GUI section ### Form=h:\autoit\forms\processinguptime.kxf
$Processing2 = GUICreate("Gathering Data...", 322, 38);, 192, 124)
$Progress2 = GUICtrlCreateProgress(8, 8, 310, 17)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

Do
    $vMsg2 = GUIGetMsg()

    $File = "C:\Aventura\Aventura Locations.xlsx"
    $FileOpen = _ExcelBookOpen($File, 0)
    $Delim = "~"
    $Result = ""
    $HMC = "2e78d6d8-fc06-4b21-817b-c63889e9e4bb"

    _ExcelSheetActivate($FileOpen, "Data")  ;Activete the Data sheet in order to grab some information
    GUICtrlSetData($Progress2, 15)
    $dataLastCell = $FileOpen.Intersect($FileOpen.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByRows, $xlPrevious).EntireRow, _
    $FileOpen.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByColumns, $xlPrevious).EntireColumn) ;This allows us to find the last used cell in the active sheet.
    GUICtrlSetData($Progress2, 20)

    $DataArray = _ExcelReadSheetToArray($FileOpen, 1, 1, $dataLastCell.Row, 3)  ;Write the contents of the Data sheet to an array
    GUICtrlSetData($Progress2, 40)

    _ExcelSheetActivate($FileOpen, "PCs")  ;Activate the PCs sheet in order to grab some information
    GUICtrlSetData($Progress2, 45)
    $pcLastCell = $FileOpen.Intersect($FileOpen.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByRows, $xlPrevious).EntireRow, _
    $FileOpen.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByColumns, $xlPrevious).EntireColumn) ;This allows us to find the last used cell in the active sheet.
    GUICtrlSetData($Progress2, 50)

    $PCArray = _ExcelReadSheetToArray($FileOpen, 1, 1, $pcLastCell.Row, 2)  ;Write the contents of the PCs sheet to an array
    GUICtrlSetData($Progress2, 70)

    _ExcelSheetActivate($FileOpen, "Printers")  ;Activate the Printers sheet in order to grab some information
    GUICtrlSetData($Progress2, 75)
    $printLastCell = $FileOpen.Intersect($FileOpen.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByRows, $xlPrevious).EntireRow, _
    $FileOpen.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByColumns, $xlPrevious).EntireColumn) ;This allows us to find the last used cell in the active sheet.
    GUICtrlSetData($Progress2, 80)

    $PrintArray = _ExcelReadSheetToArray($FileOpen, 1, 1, $printLastCell.Row, 3)  ;Write the contents of the Printers sheet to an array
    GUICtrlSetData($Progress2, 100)

Until GUICtrlRead($Progress2) = 100
GUIDelete($Processing2)
Sleep(1000)

#Region ### START Koda GUI section ### Form=h:\autoit\forms\processinguptime.kxf
$Processing = GUICreate("Processing Aventura Locations...", 322, 38);, 192, 124)
$Progress1 = GUICtrlCreateProgress(8, 8, 310, 17)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

Do
    $vMsg = GUIGetMsg()

    For $i = 1 To $PCArray[0][0]
        $LocArray = StringSplit($PCArray[$i][2], $Delim)  ;Take each HERO Location value and split it into its parts using the "~" as the delimiter
        $NextID = "2e78d6d8-fc06-4b21-817b-c63889e9e4bb"  ;Location ID for "HMC"
        For $x = 1 To $LocArray[0]
            If $LocArray[$x] = "H" Then
                $Result = "HMC (" & $HMC & ")" & @CRLF  ;This allows the script to skip the top layer, as it's always the same
            Else
                $DArray = _ArrayFindAll($DataArray, $LocArray[$x], 0, 0, 1, 1, 3)  ;Find every item in the array that has the same value as the current item in column 3 (Location Names)
                For $a = 0 To UBound($DArray) - 1  ;Search through the array containing like results
                    $DIndex = $DArray[$a]
                    $SearchResult = $DataArray[$DIndex][2]
                    If $SearchResult = $NextID Then  ;When a match between the Item ID matches the Parent ID of the current item, we have the match we need.  Record the new ID and exit the loop
                        If $LocArray[$x] = $DataArray[$DIndex][3] Then
                            $Result = $Result & $LocArray[$x] & " (" & $DataArray[$DIndex][1] & ")" & @CRLF
                            $NextID = $DataArray[$DIndex][1]
                            ExitLoop
                        EndIf
                    EndIf
                    If $a = UBound($DArray) - 1 Then
                        $NextID = "Unable to find location."
                    EndIf
                Next
            EndIf
            $FinalID = $NextID  ;Record ID
        Next
        For $p = 1 to $PrintArray[0][0]
            If $PCArray[$i][2] = $PrintArray[$p][1] Then
                If $FinalID = "Unable to find location." Then
                    $LocPrinter = "No location, no printer."
                Else
                    If $PrintArray[$p][3] = "\\AVPrint\SCMListenQueue" Then
                        $LocPrinter = "Already set up in Aventura."
                    Else
                        $LocPrinter = $PrintArray[$p][3]
                    EndIf
                EndIf
            EndIf
        Next
        _ExcelSheetActivate($FileOpen, "Output")
        _ExcelWriteCell($FileOpen, $PCArray[$i][1], $i, 1)  ;Write PC name
        _ExcelWriteCell($FileOpen, $FinalID, $i, 2)  ;Write Location ID
        _ExcelWriteCell($FileOpen, $LocPrinter, $i, 3)  ;Write Printer path
        $Result = ""
        GUICtrlSetData($Progress1, $i/($PCArray[0][0]) * 100)
    Next

Until GUICtrlRead($Progress1) = 100
GUIDelete($Processing)
Sleep(1000)

_ExcelBookClose($FileOpen)  ;Close Excel Workbook

Aventura Locations.xlsx

Share this post


Link to post
Share on other sites
JohnOne

$DArray = _ArrayFindAll($DataArray, $LocArray[$x], 0, 0, 1, 1, 3)

$DArray = _ArrayFindAll($DataArray, $LocArray[$x], 0, 0, 1, 0, 3)

1 executes a partial search (StringInStr)

Edited by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites
jerrbert

That definitely had an effect on the script. Unfortunately, I am getting data that is way out of whack now. In the example above:

H~BR~Non-Patient~Pt Fin Svcs~Common Office 2

The script will find the a single match for BR, but finds no matches for Non-Patient, Pt Fin Svcs, or Common Office 2.

Oddly enough, it finds matches for Office 2, but because it can't find matches for some of the stuff before, the final ID output is wrong.

If I reverse the change that you suggested, it will find good matches 100% of the time (I have verified this manually), but it will not make it through the following nested if statement ever.

If $SearchResult = $NextID Then  ;When a match between the Item ID matches the Parent ID of the current item, we have the match we need.  Record the new ID and exit the loop
                        If $LocArray[$x] = $DataArray[$DIndex][3] Then
                            $Result = $Result & $LocArray[$x] & " (" & $DataArray[$DIndex][1] & ")" & @CRLF
                            $NextID = $DataArray[$DIndex][1]
                            ExitLoop
                        EndIf
                    EndIf

I need the second level of validation for the script to be accurate.

Any other advise?

Share this post


Link to post
Share on other sites
jerrbert

John, that was perfect. For the small subset I am testing on, this change made the difference.  I will continue to test, but I'm calling this a win.  Thansk!!

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
Sign in to follow this  

×