How to Read Web page table data

[MaoMao]

Performing statistic for data from internet. The sources are format in tables.

Need help to read some spacific columns and made summation calculation.

Example: http://www.bts.gov/publications/national...rtation_statistics/html/table_

Used to save html pages then read to excel then back to Autoit array for processing.

However for large amount of data excel is too slow.

Try to find direct way to read web page table data to array for calculation.

How to read column 2009 to an array and calculate the sum. (i.e. the 9,671,776 vertically)

;$oIE = _IECreate($Url, 0, 0) ; Working but very slow
;$oIE = InetRead($Url) ; not getting the table??
;$oIE = _INetGetSource($Url)  ; fast but not getting the table
$oIE = _INetGetSource($Url, 'True')  ; fast but not getting the table

Edited January 22, 2012 by MaoMao

[water]

Please check functions _IETableGetCollection and _IETableWriteToArray found in the IE UDF which comes with AutoIt.

[MaoMao]

Thanks.

How come it is not displaying the table in array.

Any where to verify and work on specific column and rows?

#include <IE.au3>
#include <Array.au3>
$oIE = _IE_Example ("http://www.bts.gov/publications/national_transportation_statistics/html/table_01_37.html")
$oTable = _IETableGetCollection ($oIE, 0)
$aTableData = _IETableWriteToArray ($oTable)
$iNumTables = @extended
MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
_ArrayDisplay($aTableData)

[water]

Replace _IE_Example with _IECreate and give it a try.

[MaoMao]

OK. Get the table.

For web site with multiple tables, it is only getting the top table.

Any trick to make it collect the bottom table?

[water]

Something like that:

#include <IE.au3>
#include <Array.au3>
$oIE = _IECreate("http://www.bts.gov/publications/national_transportation_statistics/html/table_01_37.html", 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)

[MaoMao]

Thank you.

For large site data it seems the IETableGetCollection able to array display 230 columns.

Are there maximum rows and columns the IETableGetCollection able to read adn display? (Help file seems didn't tell the limit)

Need to read around 1600 rows.

Are there also way to swap the rows and columns?

[Robjong]

_IETableWriteToArray

--------------------------------------------------------------------------------

Reads the contents of a Table into an array.

$o_object Object variable of an InternetExplorer.Application, Table object

$f_transpose [optional] Boolean value specifying whether to swap the rows and columns in the output array

[MaoMao]

Yes.

The _IETableWriteToArray is able to write the table data to array. Seems there is 230 column limit when display.

Trying to write to Excel using the following to verify and output result but seems not writing any into Excel.

_ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) (error writing to first row to 250 cell)

$oExcel = $aTableData ( didn't write any cell )

$oExcel.Range("A1") = $aTableData ( write A1 cell Only)

How to write the whole array to excel?

Edited January 21, 2012 by MaoMao

[MaoMao]

An early post by Water seem solve the problem.

_ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) and array display seems have difficulity writing cell with large columns.

$aArray2=$oExcel.transpose($aArray)

_ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) and array display fine.

Now trying to find way to calculate the sum of a column.

[water]

If you want to write it to the end of each column calculate the last used row, add 1 and then write a formula into the cell using _ExcelWriteFormula.

How to calculate the last row can be found

[MaoMao]

Working with array may faster. But got problem in calculation.

Got the Web value in Array :

108,326,000

95,585,808

90,883,640

90,880,000

86,986,500

Sum is only calculating the first few digits before the "," ( comma )

For $i= 1 to 10;UBound($aArray2) -1

$Sum = $aArray2[$i][3]+ $Sum

Next

MsgBox(0, "Table Info", "There are Sub Total " & $Sum & " sum on the page")

Result = 108 + 95 + 90 +90 +86 = 469

Are there function to correct this?

Edited January 22, 2012 by MaoMao

[water]

Yes, remove the "," and then change the string to an integer.

$a = Int(StringReplace("108,326,042", "," , ""))
ConsoleWrite($a & @CRLF)

Edited January 21, 2012 by water

[MaoMao]

Good. Sum is calcualted.

The _IECreate is very slow. Not sure why open with IE for the url. But FireFox open very fast on the computer.

Tried alternative but not working.

;$oIE = _IECreate($Url, 0, 0) ; Working but very slow

;$oIE = InetRead($Url) ; not getting the table??

;$oIE = _INetGetSource($Url) ; fast but not getting the table

$oIE = _INetGetSource($Url, 'True') ; fast but not getting the table

Any advice?

[raziel2109]

I guys,

I need help for something related to your problems, as described in this post.

My problem is that the array was fullfilled with informations in the table cells, but what it's in thata array is not what is in the html code but what i see in the page in IE.

the difference is that in the IE Page the tabel cell contain "my pc is a..." and the html code contain "my pc is able to fly".

What was in the array is the first one, "my pc is a..." but i need the second one, "my pc is able to fly"...

Some help?

I think this is caused by a javascript code in html page, limiting the lenght of what to display in a cell... but how can this be bypassed?

thanks a lot...

[guinness]

Stick to your own topic please.

[DanielTyrkiel]

Have you tried requesting the excel file from the statistics office? If this is your country they ought to be able to help you...

Just an optimistic thought

regards

Dan