Parse HTML into Clean XML

Question

I have a file that is brought in as a ".EXCEL" file (fake excel, but that's beyond our control). It's actually HTML, but I'm having a hard time converting it to XML.

The HTML looks like this:

<table class="c41">
    <tr class="c5">
        <td valign="top" class="c6"><p class="c7"><span class="c8">Cash Activity </span> 
        </p>
        </td>
        <td valign="top" class="c9"><p class="c10"><br/><span class="c2">FRIDAY&nbsp;&nbsp; </span><br/><span class="c2"> </span></p>
        </td>
    </tr>
    <tr class="c5">
        <td valign="top" class="c6"><p class="c11"><br/></p>
        </td>
        <td valign="top" class="c9"><p class="c10"><br/><span class="c2">05-JAN-18</span><br/><span class="c2"> </span></p>
        </td>
    </tr>
    <tr class="c12">
        <td valign="top" class="c13"><p class="c7"><span class="c14">Prior Day Available Balance</span></p>
        </td>
        <td valign="top" class="c15"><p class="c10"><span class="c16">6,472,679.45 
        </span></p>
        </td>
    </tr>
</table>

Which looks like this:

Cash Activity               | Friday 05-JAN-18
______________________________________________
Prior Day Available Balance | $123,456.58

Is there anyway I can parse this in Powershell to an output XML looking like this:

<?xml version="1.0" encoding="utf-8" ?>
<Cash Activities>
    <Cash Activity>
        <Activity>Prior Day Available Balance</Activity>
        <Balance>123456.58</Balance>
    </Cash Activity>
</Cash Activities>

So far, the Powershell that I have only pulls it off of an E-mail, and saves it as an HTML file:

$account = "[email protected]"
#date to append to new file name
$date = Get-Date -Format yyyyMMdd
$searchDate = Get-Date -Format M/dd/yyyy
Write-Host $searchDate
#file to save attachment as
$newFileName = "Balance_Import_$date.xml"
$newFilePath = "C:\MyDirectory\\"

#Go into Outlook and get the MAPI
$mail = New-Object -ComObject outlook.application
$mailNS = $mail.GetNamespace("MAPI")


#get the account and Inbox we want
$myAcount = $mailNS.Folders | ? {$_.Name -eq $account}
$myInbox = $myAcount.Folders | ? {$_.Name -eq "Inbox"};
$myItems = $myAcount.Items | ? {$_.ReceivedTime.Date -eq $searchDate};

#loop through the Inbox and get any Attachments with the extension of .EXCEL
foreach ($f in $myInbox)
{
    foreach($i in $f.Items)
    {
        Write-Host "Checking "$i.Subject"..."

        if($i.ReceivedTime.Date -eq $searchDate)
        {
            Write-Host "---"
            Write-Host $i.Subject
            Write-Host "---"

            foreach($a in $i.Attachments)
            {
                if($a.FileName -like "*.EXCEL")
                {
                    #Move the attachment to the desired directory
                    $a.SaveAsFile((Join-Path $newFilePath $newFileName))
                    Write-Host $a.FileName " Saved as HTML"

                    #TODO: PARSE HTML INTO XML

                }
            }
        }

    }
} 

Answer 1

Parsing the fake Excel / HTML input may have some issues:

1. HTML is not well-formed.
2. HTML Entities like   will break the XML Parser.

Assuming your HTML example above takes care of the first issue, you can brute force the second issue by decoding the input like this:

[xml]$html = [System.Net.WebUtility]::HtmlDecode(@'
<table class="c41">
    <tr class="c5">
        <td valign="top" class="c6"><p class="c7"><span class="c8">Cash Activity </span> 
        </p>
        </td>
        <td valign="top" class="c9"><p class="c10"><br/><span class="c2">FRIDAY&nbsp;&nbsp; </span><br/><span class="c2"> </span></p>
        </td>
    </tr>
    <tr class="c5">
        <td valign="top" class="c6"><p class="c11"><br/></p>
        </td>
        <td valign="top" class="c9"><p class="c10"><br/><span class="c2">05-JAN-18</span><br/><span class="c2"> </span></p>
        </td>
    </tr>
    <tr class="c12">
        <td valign="top" class="c13"><p class="c7"><span class="c14">Prior Day Available Balance</span></p>
        </td>
        <td valign="top" class="c15"><p class="c10"><span class="c16">6,472,679.45 
        </span></p>
        </td>
    </tr>
</table>
'@);

Now it's just a matter of some simple XPath to select the nodes you want to get the desired XML you specified above (tested and working):

$xml = @'
<?xml version="1.0" encoding="utf-8" ?>
<Cash Activities>

'@;
$rows = $html.DocumentElement.SelectNodes('//tr');
foreach ($row in $rows) {
    if ($row.GetAttribute('class') -eq 'c12') {
        $xml += "`t<Cash Activity>`n";
        $spans = $row.SelectNodes('.//descendant::span[@class]');
        if ($spans.Count -eq 2) {
            $xml += "`t`t<Activity>$($spans[0].InnerText.Trim())</Activity>`n"; 
            $xml += "`t`t<Balance>$($spans[1].InnerText.Trim())</Balance>`n"; 
        }
        $xml += "`t</Cash Activity>`n";
    }
}

$xml += @'
</Cash Activities>
'@;