Sign in to follow this  
Followers 0
Seminko

Getting table data from a webpage

12 posts in this topic

#1 ·  Posted (edited)

Hey fellas,

the time has come to seek your help once more :).

#FluffOn

Our company recently had an attendance system build but the developers forgot to add one critical thing - the value of your current overtime.

And as the fiddler that I am I decided to make a tool that would make it possible. I already made and excel version that does that but it requires the user to export an excel file from the site, then open it and then activate the calculation. Which is two steps more than I would like and hence I'm turning to you.

#FluffOff

Basically, there is data in a table on the site which I want to grab. I checked the forums and found this:

#include <IE.au3>
#include <Array.au3>

$oIE = _IECreate("<a href='http://www.bts.gov/publications/national_transportation_statistics/html/table_01_37.html' class='bbc_url' title='External link' rel='nofollow external'>http://www.bts.gov/publications/national_transportation_statistics/html/table_01_37.html</a>", 0, 0)
$oTable = _IETableGetCollection ($oIE) ; Get number of tables
$iNumTables = @extended
MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
$oTable = _IETableGetCollection ($oIE, $iNumTables-1) ; Get last table
$aTableData = _IETableWriteToArray ($oTable)
_ArrayDisplay($aTableData)

It works great, it shows me how many tables there are and the shows me the contents of the table.

But, my site is a https site ending aspx so I cannot just _IECreate it. So I tried _IEAttach("partial name", "URL").

 

Now it tells me there is 397 tables on the site :-D How do I find the one I'm looking for?

OK got it:

$oIE  = _IEAttach("bpowebtarget", "URL")
$oTable = _IETableGetCollection ($oIE) ; Get number of tables
$iNumTables = @extended
MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
For $i = 1 To $iNumTables+1
    $oTable = _IETableGetCollection ($oIE, $i) ; Get last table
    $aTableData = _IETableWriteToArray ($oTable)
    _ArrayDisplay($aTableData)
Next

I'm fine for now, will report back once I get stuck again.

Edited by Seminko

Share this post


Link to post
Share on other sites



Do the tables have ID's, or are they under elements with ID's, or is there something specific to trigger off of?

Need to see the table, and a few of it's parent nodes.


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

OK, I got a huge table with over 9rows and 30columns. I'm interested only in one row, how do I get it?

I tried:

$oTable = _IETableGetCollection ($oIE, 26) ; Get the table
$aTableData = _IETableWriteToArray ($oTable) ; Transform the table into an array
_ArrayDisplay($aTableData)
$iCols = Ubound($aTableData, 2) ; Check the number of columns
MsgBox(1, "", $iCols) ; 31
Local $aSmallArray[$iCols+1]
For $i = 1 To $iCols ; hoping this would go through values from row 9 and assign them to a new array
    $aSmallArray[$i] = $aTableData[9][$i]
Next
_ArrayDisplay($aSmallArray)

But I get Array variable has incorrect number of subscripts or subscript dimension range exceeded

 

EDIT: alright it has to be For $i = 1 To $iCols -1 as we are starting at 1 and not 0

Edited by Seminko

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

try this

$oTable = _IETableGetCollection ($oIE, 26) ; Get the table
$aTableData = _IETableWriteToArray ($oTable) ; Transform the table into an array
Local $aSmallArray[UBound($aTableData,2)]
If UBound($aTableData) >= 9 Then
    For $i = 1 To UBound($aSmallArray)-1
        $aSmallArray[$i] = $aTableData[9][$i]
    Next
EndIf
_ArrayDisplay($aSmallArray)
Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

can't you use, inetread... + searchinstr for the start the table, and using the output, search for the next </table>, then with stringmid extract the table?


Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Alright got the table filtered.

Now I need to add the numbers together.

Let's array $aSmallArray consists of these numbers:

0,07
-0,01
-0,12
0,18
-0,06

I tried Execute("$aSmallArray[5] + $aSmallArray[6]") but that doesn't work.

First thing that comes to mind is the comma as here in the EU we use comma as a period.

Do you think if I replace comma with period the above formula would work?

 

EDIT: Indeed it would! I'm on fire today :-D

 

EDIT2: how do I replace all " , " with " . " in an array?

Edited by Seminko

Share this post


Link to post
Share on other sites

are those supposed to be decimals?

Do a string replace of , to ., before performing the calculation

$i = StringReplace($aSmallArray[5],",",".") + StringReplace($aSmallArray[6],",",".")

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

 

are those supposed to be decimals?

Do a string replace of , to ., before performing the calculation

$i = StringReplace($aSmallArray[5],",",".") + StringReplace($aSmallArray[6],",",".")

Nice, thank you.

I did:

Local $aSmallArrayComma[$iCols]
For $i = 1 To $iCols-1
    $aSmallArrayComma[$i] = StringReplace($aSmallArray[$i],",",".")
Next

Share this post


Link to post
Share on other sites

How do I add all numbers in the array without writing it down one string after another?

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Local $a[5]=[1,2,3,4,5]
$i = 0
For $j = 0 To UBound($a)-1
    $i += $a[$j]
Next
ConsoleWrite($i & @CRLF)

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

Setting $i to zero. Clever!!!

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

I'll be damned, it works!!!

But, the website is complete bogus, different ppl have the dates in different columns and rows.

Alright then, now to damage control.

 

Thanks fellas, appreciate your help and keeping up with my fast paced (for forum standards) thread ;)

Edited by Seminko

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  
Followers 0