Jump to content

Array is not matching with the second array


Recommended Posts

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:

<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>
#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 by Hermes
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

#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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

Hi @Nine I updated code below:

#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.

Link to comment
Share on other sites

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

#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

 

Link to comment
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
 Share

×
×
  • Create New...