KimberlyJillPereira Posted April 19, 2017 Posted April 19, 2017 I could only extract the first 20 from table into Microsoft Excel by using Array Extract but I want to extract until the end what I mean is until the second page. How to do it? Please revert. Thanks.
KimberlyJillPereira Posted April 19, 2017 Author Posted April 19, 2017 since there is total of 33 I want to extract from row 0 to row 32. How to do it? please help. Thanks.
KimberlyJillPereira Posted April 19, 2017 Author Posted April 19, 2017 (edited) @water @MaoMao please help Edited April 19, 2017 by KimberlyJillPereira
Subz Posted April 19, 2017 Posted April 19, 2017 (edited) expandcollapse popup#include <Array.au3> #include <IE.au3> #include <String.au3> Local $oIE Local $oDivs Local $aPageCount, $iPageCount Local $aTable, $oTables Local $aResults[0][7] $oIE = _IECreate("http://www.timeview2.net/alarms", 1) $oDivs = _IETagNameGetCollection($oIE, "div") For $oDiv In $oDivs If $oDiv.ClassName = "alarms index" Then $aPageCount = _StringBetween($oDiv.InnerText, "Page 1 of ", ", showing ") If @error Then Exit $iPageCount = Number($aPageCount[0]) EndIf Next For $i = 1 To $iPageCount $oIE = _IECreate("http://www.timeview2.net/alarms/index/group:All%20Groups/page:" & $i, 1) $oTables = _IETagNameGetCollection($oIE, "table") For $oTable In $oTables If $oTable.ClassName = "pageindex" Then $nTable = $oTable.NextElementSibling $aTable = _IETableWriteToArray($nTable, True) If UBound($aTable) - 1 > 0 And UBound($aTable, 2) = 7 Then If $i = 1 Then _ArrayConcatenate($aResults, $aTable, 0) Else _ArrayConcatenate($aResults, $aTable, 1) EndIf ExitLoop EndIf EndIf Next Next _ArrayDisplay($aResults) ;~ Save to CSV Local $hFileOpen = FileOpen(@ScriptDir & "\Results.csv", 2) For $i = 0 To UBound($aResults) - 1 FileWrite($hFileOpen, '"' & _ StringStripWS($aResults[$i][0], 7) & '","' & _ StringStripWS($aResults[$i][1], 7) & '","' & _ StringStripWS($aResults[$i][2], 7) & '","' & _ StringStripWS($aResults[$i][3], 7) & '","' & _ StringStripWS($aResults[$i][4], 7) & '","' & _ StringStripWS($aResults[$i][5], 7) & '"' & @CRLF) Next FileClose($hFileOpen) Edited April 19, 2017 by Subz Added Table Header + Removed unwanted spaces from table results KimberlyJillPereira 1
KimberlyJillPereira Posted April 20, 2017 Author Posted April 20, 2017 Thanks it works but then I want to type in username and password automatically then click submit button and then click on Alarms button to extract all the data but then it is already logged in and then extracts all the data. I copied my code to it and it only automatically types in username and password but then it doesnt click submit button. I used _IEFormSubmit($oDivs) and it doesnt work and it shows warning from function _IEAttach, $_IESTATUS_NoMatch even clicking on alarms button also doesnt work. I used this code _IELinkClickByText($oIE, "Alarms") and it also shows warning Warning from function _IELinkClickByText, $_IESTATUS_NoMatch by the way I used these codes in a seperate program and it worked but when I use my codes together with yours it doesnt work. Please help me. Thanks.
Subz Posted April 20, 2017 Posted April 20, 2017 Can you post your code without username and password and I'll take a look although probably later this evening. KimberlyJillPereira 1
KimberlyJillPereira Posted April 20, 2017 Author Posted April 20, 2017 #include <IE.au3> #include <AutoItCostants.au3> Local $oIE = _IECreate("http://www.timeview2.net/");Opens IE browser Local $oForm = _IEFormGetCollection($oIE,0) _IEFormSubmit($oForm);Click on submmit button _IELinkClickByText($oIE, "Alarms");Click on alarms
Subz Posted April 20, 2017 Posted April 20, 2017 Try this, just replace values for $sUserName and $sPassWord. expandcollapse popup#include <Array.au3> #include <IE.au3> #include <String.au3> Local $oIE Local $oDivs Local $aPageCount, $iPageCount Local $aTable, $oTables Local $aResults[0][7] Local $oUserName, $oPassWord Local $sUserName = "user" Local $sPassWord = "demo" $oIE = _IECreate("http://www.timeview2.net/", 1) $oForms = _IETagNameGetCollection($oIE, "form") For $oForm In $oForms If $oForm.id = "UserLoginForm" Then $oUserName = _IEFormElementGetObjByName($oForm, "data[User][username]") _IEFormElementSetValue($oUserName, $sUserName) $oPassWord = _IEFormElementGetObjByName($oForm, "data[User][password]") _IEFormElementSetValue($oPassWord, $sPassWord) _IEFormSubmit($oForm) EndIf Next $oIE = _IECreate("http://www.timeview2.net/alarms", 1) $oDivs = _IETagNameGetCollection($oIE, "div") For $oDiv In $oDivs If $oDiv.ClassName = "alarms index" Then $aPageCount = _StringBetween($oDiv.InnerText, "Page 1 of ", ", showing ") If @error Then Exit $iPageCount = Number($aPageCount[0]) EndIf Next For $i = 1 To $iPageCount $oIE = _IECreate("http://www.timeview2.net/alarms/index/group:All%20Groups/page:" & $i, 1) $oTables = _IETagNameGetCollection($oIE, "table") For $oTable In $oTables If $oTable.ClassName = "pageindex" Then $nTable = $oTable.NextElementSibling $aTable = _IETableWriteToArray($nTable, True) If UBound($aTable) - 1 > 0 And UBound($aTable, 2) = 7 Then If $i = 1 Then _ArrayConcatenate($aResults, $aTable, 0) Else _ArrayConcatenate($aResults, $aTable, 1) EndIf ExitLoop EndIf EndIf Next Next _ArrayDisplay($aResults) ;~ Save to CSV Local $hFileOpen = FileOpen(@ScriptDir & "\Results.csv", 2) For $i = 0 To UBound($aResults) - 1 FileWrite($hFileOpen, '"' & _ StringStripWS($aResults[$i][0], 7) & '","' & _ StringStripWS($aResults[$i][1], 7) & '","' & _ StringStripWS($aResults[$i][2], 7) & '","' & _ StringStripWS($aResults[$i][3], 7) & '","' & _ StringStripWS($aResults[$i][4], 7) & '","' & _ StringStripWS($aResults[$i][5], 7) & '"' & @CRLF) Next FileClose($hFileOpen) KimberlyJillPereira 1
KimberlyJillPereira Posted April 20, 2017 Author Posted April 20, 2017 Thanks it works but the problem is that there is too many popup window when its on Alarms page then it shows full table extraction. How to avoid too many popup windows? thanks.
Subz Posted April 20, 2017 Posted April 20, 2017 expandcollapse popup#include <Array.au3> #include <IE.au3> #include <String.au3> Local $oIE Local $oDivs Local $aPageCount, $iPageCount Local $aTable, $oTables Local $aResults[0][7] Local $oUserName, $oPassWord Local $sUserName = "user" Local $sPassWord = "demo" $oIE = _IECreate("http://www.timeview2.net/", 1) $oForms = _IETagNameGetCollection($oIE, "form") For $oForm In $oForms If $oForm.id = "UserLoginForm" Then $oUserName = _IEFormElementGetObjByName($oForm, "data[User][username]") _IEFormElementSetValue($oUserName, $sUserName) $oPassWord = _IEFormElementGetObjByName($oForm, "data[User][password]") _IEFormElementSetValue($oPassWord, $sPassWord) _IEFormSubmit($oForm) EndIf Next _IENavigate($oIE, "http://www.timeview2.net/alarms") $oDivs = _IETagNameGetCollection($oIE, "div") For $oDiv In $oDivs If $oDiv.ClassName = "alarms index" Then $aPageCount = _StringBetween($oDiv.InnerText, "Page 1 of ", ", showing ") If @error Then Exit $iPageCount = Number($aPageCount[0]) EndIf Next For $i = 1 To $iPageCount _IENavigate($oIE, "http://www.timeview2.net/alarms/index/group:All%20Groups/page:" & $i, 1) $oTables = _IETagNameGetCollection($oIE, "table") For $oTable In $oTables If $oTable.ClassName = "pageindex" Then $nTable = $oTable.NextElementSibling $aTable = _IETableWriteToArray($nTable, True) If UBound($aTable) - 1 > 0 And UBound($aTable, 2) = 7 Then If $i = 1 Then _ArrayConcatenate($aResults, $aTable, 0) Else _ArrayConcatenate($aResults, $aTable, 1) EndIf ExitLoop EndIf EndIf Next Next _ArrayDisplay($aResults) ;~ Save to CSV Local $hFileOpen = FileOpen(@ScriptDir & "\Results.csv", 2) For $i = 0 To UBound($aResults) - 1 FileWrite($hFileOpen, '"' & _ StringStripWS($aResults[$i][0], 7) & '","' & _ StringStripWS($aResults[$i][1], 7) & '","' & _ StringStripWS($aResults[$i][2], 7) & '","' & _ StringStripWS($aResults[$i][3], 7) & '","' & _ StringStripWS($aResults[$i][4], 7) & '","' & _ StringStripWS($aResults[$i][5], 7) & '"' & @CRLF) Next FileClose($hFileOpen) KimberlyJillPereira 1
KimberlyJillPereira Posted April 20, 2017 Author Posted April 20, 2017 No more popups. It worked. Thank you so much!
KimberlyJillPereira Posted April 20, 2017 Author Posted April 20, 2017 @Subz , Sorry to disturb you again I just only want to extract from column 0 to 5 that means excluding the column 'Actions'. Please help me. Thanks.
Subz Posted April 20, 2017 Posted April 20, 2017 If you look at the results.csv file it already excludes the Actions column, the _ArrayDisplay shows the full table but I only wrote column 0 to 5 in the csv, see code after :~ Save to CSV
KimberlyJillPereira Posted April 20, 2017 Author Posted April 20, 2017 It doesnt have column but it has rows. I dont want 'View'. how to remove rows too for the Column 'Action' Please help. Thanks.
Subz Posted April 20, 2017 Posted April 20, 2017 You can actually remove _ArrayDisplay line it doesn't really mean anything, I just wanted to show that it had grabbed the table, the results that are saved to csv are only Col 0 to Col 5. If you want to keep _ArrayDisplay then just use _ArrayColDelete($aResults, 6) before _ArrayDisplay KimberlyJillPereira 1
KimberlyJillPereira Posted April 20, 2017 Author Posted April 20, 2017 Thank you so much!!! It worked.
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now