Compare Fail

[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!!

expandcollapse popup

#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

[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 September 25, 2013 by JohnOne

[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?

[JohnOne]

Maybe there are non visible characters in your strings.

Try using StringStripWS() or some RegExp function to clean them, you may have to amend your search strings also.

[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!!