Array is not matching with the second array

[Hermes]

I have an html table that displays data along with an excel spreadsheet that has the same data as the html table. I am wanting to only match the Title column in my html table with the Title column in my Excel spreadsheet. If the titles match, click on the Edit hyperlink and continue to loop to next row. The issue I'm experience is its not matching correctly. So far  i've written the codes below:

expandcollapse popup

<table border="1" class="test">
    
    <tr>
        
        <th> UniqueID</th>
        <th> Title</th>
        <th> UserID</th>
        <th> Address</th>
        <th> Gender </th>
    </tr>
    
    <tr>
        
        <td> 1 </td>
        <td> Title1 </td>
        <td> 12345 </td>
        <td> Manila </td>
        <td>
            
            <span> Male </span>
            
        </td>
    </tr>
    <tr>
        
        <td align="center" colspan="5">
           <a href="#" class="testlink">Edit</a>
        </td>
    </tr>
    <tr>
        
        <td> 2 </td>
        <td> Title2 </td>
        <td> 67891 </td>
        <td> Valenzuela </td>
        <td>
            
            <span> Female </span>
            
        </td>
    </tr>
    <tr>
        
        <td align="center" colspan="5" >
           <a href="#" class="testlink">Edit</a>
        </td>
    </tr>
    <tr>
        
        <td> 3 </td>
        <td> Title3 </td>
        <td> 88888 </td>
        <td> Ohio </td>
        <td>
            
            <span> Male </span>
            
        </td>
    </tr>
    <tr>
        
        <td align="center" colspan="5" >
           <a href="#" class="testlink">Edit</a>
        </td>
    </tr>
    <tr>
        
        <td> 4 </td>
        <td> Title4 </td>
        <td> 77777 </td>
        <td> California </td>
        <td>
            
            <span> Female </span>
            
        </td>
    </tr>
    <tr>
        
        <td align="center" colspan="5" >
           <a href="#" class="testlink">Edit</a>
        </td>
    </tr>
    <tr>
        
        <td> 5 </td>
        <td> Title5 </td>
        <td> 33333 </td>
        <td> Arizona </td>
        <td>
            
            <span> Male </span>
            
        </td>
    </tr>
    <tr>
        
        <td align="center" colspan="5" >
           <a href="#" class="testlink">Edit</a>
        </td>
    </tr>
    
</table>

expandcollapse popup

#Include "Chrome.au3"

#Include "wd_core.au3"

#Include "wd_helper.au3"

#Include "Excel.au3"

#Include "_HtmlTable2Array.au3" 

#Include "Array.au3"  

Local $sDesiredCapabilities, $sSession

SetupChrome()

_WD_Startup()

$sSession = _WD_CreateSession($sDesiredCapabilities)

_WD_LoadWait($sSession)

_WD_Navigate($sSession, "index.html")

Sleep(6000)

Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, "test.xlsx")

; Get the table element
$sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='test']")

; Retrieve HTML
$sHTML = _WD_ElementAction($sSession, $sElement, "Property", "outerHTML")

;Local $aTable = _HtmlTableGetWriteToArray($sHTML)

Local $aArray1 = _Excel_RangeRead($oWorkbook,1,$oWorkbook.ActiveSheet.Usedrange.Columns("B:B"))
Local $aArray2 = _HtmlTableGetWriteToArray($sHTML)

;_ArrayDisplay($aArray1)
;_ArrayDisplay($aArray2)

For $i = UBound($aArray1) - 1 To 0 step - 1
    For $j = UBound($aArray2) - 1 to 0 step - 1
        If  $aArray1[$i][1] == $aArray2[$j][1] Then
            _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]")
            $test1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]")
            _WD_ElementAction($sSession, $test1, 'click')
            ;_ArrayDisplay($aArray1)
           ;_ArrayDelete($aArray1 , $i)
           ;exitloop
        EndIf
    Next
Next

_WD_Shutdown()

 

Func SetupChrome()

    _WD_Option('Driver', 'chromedriver.exe')

    _WD_Option('Port', 9515)

    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')

    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'

EndFunc   ;==>SetupChrome

Would appreciate if anyone can provide tips, or point me in the right direction in doing it.

 

test.xlsx

Edited March 17, 2021 by Hermes

[Nine]

I think the problem is that double loops.  It should be a single comparing the same indice of each of the arrays.  I am supposing that both arrays have always the same dimension (in your example it is 5).  Also your loop should not be in inverse way.  And your find element should use the previous found element.

 

[Hermes]

@NineThanks for responding! I modified the script and removed the extra for loop:

expandcollapse popup

#Include "Chrome.au3"

#Include "wd_core.au3"

#Include "wd_helper.au3"

#Include "Excel.au3"

#Include "_HtmlTable2Array.au3" 

#Include "Array.au3"  

Local $sDesiredCapabilities, $sSession

SetupChrome()

_WD_Startup()

$sSession = _WD_CreateSession($sDesiredCapabilities)

_WD_LoadWait($sSession)

_WD_Navigate($sSession, "index.html")

Sleep(6000)

Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, "test.xlsx")

; Get the table element
$sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='test']")

; Retrieve HTML
$sHTML = _WD_ElementAction($sSession, $sElement, "Property", "outerHTML")

;Local $aTable = _HtmlTableGetWriteToArray($sHTML)

Local $aArray1 = _Excel_RangeRead($oWorkbook,1,$oWorkbook.ActiveSheet.Usedrange.Columns("B:B"))
Local $aArray2 = _HtmlTableGetWriteToArray($sHTML)

;_ArrayDisplay($aArray1)
;_ArrayDisplay($aArray2)

For $i = 0 To UBound($aArray1) - 1
        If  $aArray1[$i][1] == $aArray2[$i][1] Then
            _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'options-list-item') or contains(text(),'Edit')]")
            $test1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'options-list-item') or contains(text(),'Edit')]")
            _WD_ElementAction($sSession, $test1, 'click')
            ;_ArrayDisplay($aArray1)
           ;_ArrayDelete($aArray1 , $i)
           ;exitloop
        EndIf
Next 

_WD_Shutdown()

 

Func SetupChrome()

    _WD_Option('Driver', 'chromedriver.exe')

    _WD_Option('Port', 9515)

    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')

    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'

EndFunc   ;==>SetupChrome

And now i'm getting this error message:

==> Array variable has incorrect number of subscripts or subscript dimension range exceeded

When you say "And your find element should use the previous found element.", not really sure what you mean by that.

[Nine]

You should read your excel sheet starting at row 2 since you have a title.

Look at _WD_FindElement there is a starting element (4th parameter), I think you will need it...

[Hermes]

Hi @Nine I updated code below:

expandcollapse popup

#Include "Chrome.au3"

#Include "wd_core.au3"

#Include "wd_helper.au3"

#Include "Excel.au3"

#Include "_HtmlTable2Array.au3" 

#Include "Array.au3"  

Local $sDesiredCapabilities, $sSession

SetupChrome()

_WD_Startup()

$sSession = _WD_CreateSession($sDesiredCapabilities)

_WD_LoadWait($sSession)

_WD_Navigate($sSession, "index.html")

Sleep(6000)

Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, "test.xlsx")

; Get the table element
$sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='test']")

; Retrieve HTML
$sHTML = _WD_ElementAction($sSession, $sElement, "Property", "outerHTML")

;Local $aTable = _HtmlTableGetWriteToArray($sHTML)

Local $aArray1 = _Excel_RangeRead($oWorkbook,1,$oWorkbook.ActiveSheet.Usedrange.Columns("B:B"))
Local $aArray2 = _HtmlTableGetWriteToArray($sHTML)

;_ArrayDisplay($aArray1)
;_ArrayDisplay($aArray2)

Global $Skipline = 0 ;0==> first line

Local $temprf

For $i = 0 To UBound($aArray1, $aArray2) - 1
   If $Skipline = $i Then ContinueLoop

       $temprf &= $aArray1[$i]
        If  $aArray1[$i] == $aArray2[$i] Then
            _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'options-list-item') or contains(text(),'Edit')]")
            $test1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "//a[contains(@class,'options-list-item') or contains(text(),'Edit')]", $aArray1[$i])
            _WD_ElementAction($sSession, $test1, 'click')
            ;_ArrayDisplay($aArray1)
           ;_ArrayDelete($aArray1 , $i)
           ;exitloop
        EndIf
Next 

_WD_Shutdown()

 

Func SetupChrome()

    _WD_Option('Driver', 'chromedriver.exe')

    _WD_Option('Port', 9515)

    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')

    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'

EndFunc   ;==>SetupChrome

I managed to skipped the first row, but it doesn't click the element (edit) link.

[Nine]

Here what your code could be if I understand you correctly :

expandcollapse popup

#Include "wd_core.au3"
#Include "wd_helper.au3"
#Include <Excel.au3>
#Include <Array.au3>

Local $sDesiredCapabilities = SetupChrome()
_WD_Startup()
Local $sSession = _WD_CreateSession($sDesiredCapabilities)
_WD_Navigate($sSession, "file://C:\\Applications\\AutoIt\\WebDriver\\HTML Examples\\Table.html")
_WD_LoadWait($sSession)

Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='test']")
ConsoleWrite ("Table " & $sElement & @CRLF)
Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(.,'Title')]", $sElement, True)
For $i = 0 to UBound($aArray1) - 1
  $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text')
Next
_ArrayDisplay($aArray1)
Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[@class='testlink']", $sElement, True)
_ArrayDisplay($aElement)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
Local $aArray2 = _Excel_RangeRead($oWorkbook, Default,"B2:B6")
_Excel_Close($oExcel)
_ArrayDisplay($aArray2)

; search from Excel array cells into Chrome table
Local $iIdx
For $i = 0 To UBound($aArray2) - 1
  $iIdx = _ArraySearch($aArray1, $aArray2[$i])
  If @error Then ContinueLoop
  ConsoleWrite ("Found at " & $iIdx & @CRLF)
  _WD_ElementAction($sSession, $aElement[$iIdx], 'click')
Next

;_WD_DeleteSession($sSession)
;_WD_Shutdown()

Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH)
Sleep(2000)
For $i = 1 To $aDir[0]
  DirRemove($aDir[$i], $DIR_REMOVE)
Next

Func SetupChrome()
    _WD_Option('Driver', 'chromedriver.exe')
    _WD_Option('Port', 9515)
    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')
    Return '{"capabilities":{"alwaysMatch":{"goog:chromeOptions":{"w3c":true,' & _
    '"excludeSwitches":["enable-automation"],"useAutomationExtension":false}}}}'
EndFunc   ;==>SetupChrome

 

[Hermes]

@Nine Yep, it is! Thank you so much!