Extracting HTML Table with Powershell

Question

So I was able to use Read-HtmlTable and get the table back with the correct columns and rows but the data itself returns null. Note that data is constantly changing so not sure if this makes a difference. But anyone knows of a way for me to extract the VA value? Sorry I am still fairly new to powershell.

Instantaneous Average

---

VA - -

Answer 1

That is the most screwed up set of tables!

There are 5 tables in that page. Some of them actually work (i.e., there's a header for each column). The rest of them either use a non-breaking space for the 1st column header (which sometimes seems to throw the script into situation where it throws an exception), or puts the column header in one table and then the data in the following table (e.g., tableindex 2 and 3).

Without trying to debug author's script, this is the best I could come up with:

$w = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 0 # -Header 'Instantaneous','Pos Average','Neg Average'  
$x = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 1  
# skip tableindex2 -- it casses an exception  
$y = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 3 -Header '1','2','3','4'  
$z = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 4  -Header '1','2','3'  

Answer 2

If the web page has only one table, and the table has column headers, then this should allow you to get both the name of the column and each rows column data:

.\Read-HTMLTable https://github.com/iRon7/Read-HtmlTable |  
    ForEach-Object{  
        $td = $_  
        $td.psobject.properties.Name |  
            ForEach-Object{  
                $_              # the column name  
                $td.$_          # this row's value for that column  
                "----------------------------"  
            }  
            "=============================="    # end of   
    }  

If the table has no headers you'll have to supply the values to be assigned to each column. I don't know how that would work if there were multiple tables without headers, though.

Answer 3

Working from the HTML file (not the URL), here's one way of extracting the information from all the properties found by using the "id" attribute in the HTML elements. As long as those remain constant this should work. The only thing I can't tell from just the small data sample is whether or not anything other than "k" is used as a suffix for the values -- or whether or not there will ever be a negative value in any of the elements that use decimal fractions.

# $v = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 0 -notrim # -Header 'Instantaneous','Pos Average','Neg Average'  
# $w = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 1  
# #x = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 2 -notrim -header 'blank1','Primary','2','3' # skip tableindex2 -- it casses an exception  
# $y = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 3 -notrim -Header '1','2','3','4'  
# $z = Read-HtmlTable.ps1 -InputObject c:/junk/powerenergy_mhtml.htm -tableindex 4 -notrim #-Header '1','2','3'  
  
# Map HTML elements by "id" to $Table hash keys  
$ids = @{  
    # Power /Real Time  
    INSWAT  = 'W Instantaneous'  
    PAVWAT  = 'W Pos Average'  
    NAVWAT  = 'W Neg Average'  
    INSVAR  = 'VAR Instantaneous'  
    PAVVAR  = 'VAR Pos Average'  
    NAVVAR  = 'VAR Neg Average'  
    INSPF   = 'PF Instantaneous'  
    PAVPF   = 'PF Pos Average'  
    NAVPF   = 'PF Neg Average'  
    INSV_A  = 'VA Instantaneous'  
    AVGV_A  = 'VA Average'  
    # Energy  
    WATHNET = 'Wh Net'  
    WATHREC = 'Wh Delivered'  
    WATHTOT = 'Wh Total'  
    WATHDEL = 'Wh Received'  
    VARHNET = 'VARh Net'  
    VARHPOS = 'VARh Delivered'  
    VARHTOT = 'VARh Total'  
    VARHNEG = 'VARh Received'  
    VAHTOT  = 'VAh Total'  
  
}  
  
# Power and Energy  
$Table = [ordered]@{  
    # Power /Real Time  
    'W Instantaneous'   = '0.000 k'  
    'W Pos Average'     = '0.000 k'  
    'W Neg Average'     = '0.000'  
    'VAR Instantaneous' = '0.000 k'  
    'VAR Pos Average'   = '0.000 k'  
    'VAR Neg Average'   = '0.000'  
    'PF Instantaneous'  = '0.000 k'  
    'PF Pos Average'    = '0.000 k'  
    'PF Neg Average'    = '0.000'  
    'VA Instantaneous'  = '0.000 k'  
    'VA Average'        = '0.000k'  
    # Energy  
    'Wh Net'            = '0.000 k'  
    'Wh Delivered'      = '0.000 k'  
    'Wh Total'          = '0.000 k'  
    'Wh Received'       = '0.000 k'  
    'VARh Net'          = '0.000 k'  
    'VARh Delivered'    = '0.000 k'  
    'VARh Total'        = '0.000 k'  
    'VARh Received'     = '0.000 k'  
    'VAh Total'         = '0.000 k'  
}  
  
$wp = get-content c:/junk/powerenergy_mhtml.htm -Raw  
  
$ids.GetEnumerator()|  
    ForEach-Object{  
        $k = $_.Key  
        $v = $_.Value  
        $r = "(?s)id\=""$k\"">(\d+\.\d+\s?k?|(-?\d+\s?k?))\</td>"  
        if ($wp -match $r){  
            $Table.$v = $matches[1]  
        }  
    }  

There may be problems if the element id's and values are separated by newline breaks. But that looks like it could be easily solved by simply replacing the newline chacters by "nothing" or perhaps a single space. HTML ignores line breaks.