Getting table data from a webpage

[Seminko]

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 November 13, 2014 by Seminko

[jdelaney]

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.

[Seminko]

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 November 13, 2014 by Seminko

[jdelaney]

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 November 13, 2014 by jdelaney

[Kyan]

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?

[Seminko]

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 November 13, 2014 by Seminko

[jdelaney]

are those supposed to be decimals?

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

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

[Seminko]

 

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

[Seminko]

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

[jdelaney]

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 November 13, 2014 by jdelaney

[Seminko]

Setting $i to zero. Clever!!!

[Seminko]

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 November 13, 2014 by Seminko