Convert HTML to Excel keeping structure

Question

_{Friday, January 30, 2015 10:40 AM}

Hi guys!

I run a script of mine on a fileserver 2008 R2; the script return a report in html but it's huge (50000 KB) so it takes a lot of time to load and i can't order it.

The function that create the html report is this:

Function Get-Report{
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory=$True,Position=0)]$Drives,
        [Parameter(Mandatory=$True,Position=1)][string]$Kind,
        [Parameter(Mandatory=$True,Position=2)]$Source
    )

    $Today = Get-Date -Format "dd-MMM"

    $Head = @"
    <Title>$Kind Report</Title>
    <style>
        body { 
            background-color:#FFFFFF;
            font-family:Verdana;
            font-size:12pt; 
        }
        td, th { 
            border:1px solid blue; 
            border-collapse:collapse; 
        }
        th {
            color:white;
            background-color:green; 
        }
        table, tr, td, th { padding: 5px; margin: 0px }
        table { margin-left:50px; }
    </style>
"@

    [xml]$html = $Drives | ConvertTo-Html -fragment

    for ($i=1;$i -le $html.table.tr.count-1;$i++) {
        $class = $html.CreateAttribute("class")
    }

    $Body = @"
<H1>$Kind File Report for $Source</H1>
$($html.innerxml)
"@

    $Log = $Kind + "Report" + $Today
    
    if(!(Test-Path -Path "C:\Powershell Reports")){
        New-Item -Path "C:\Powershell Reports" -ItemType directory
    }

    ConvertTo-HTML -head $head  -PostContent "<br><i>$(Get-date)</i>" -body $body | 
    Out-File "C:\Powershell Reports\$Log.html" -Encoding ascii
}

And this is the output.

Does anyone knows some easy function to convert and save the html output to an excel file keeping this structure?

Thanks!

A

All replies (1)

_{Friday, January 30, 2015 12:07 PM ✅Answered}

Hi Albiz,

try this:

# Defaults to comma delimiter
$Drives | Select PSChildName, PSDrive, PSprovider, PSIsContainer | Export-Csv "output.csv" -NoTypeInformation

# Add custom Delimiter
$Drives | Select PSChildName, PSDrive, PSprovider, PSIsContainer | Export-Csv "output.csv" -NoTypeInformation -Delimiter ";"

Depending on your region you may want to change the delimiter, so your Excel automatically detects the format and presents it as a table. By Adding just the names of the properties you want in the Select Block you can cut down on total data and chose the order in which they are exported.

Cheers,
Fred

There's no place like 127.0.0.1