how to convert a html-report into a csv-report

Hello Together
i’m writing a script which send automatically an e-mail to an person which folder use to much space in a directory.
And one part of this script create a html-report but i want to convert this html-report into an csv.

And here is my question how can i convert an html-report to a csv-report

It would help to see the script you’re referring to (in particular, the part the generates an HTML report).

As a general observation, it would be simpler to create both an HTML report and a CSV report from the same objects in memory, rather than having to reconstruct objects based on the HTML. Either way is doable, but reading the HTML is more work.

Param ( [string]$Path = "N:\", [string]$ReportPath = "N:\Test", [switch]$Recurse )

Function AddObject {
Param (
$FileObject
)
$Size = double
$Script:TotSize += $Size
If ($Size)
{ $Size = CalculateSize $Size
}
Else
{ $Size = “0.00 MB”
}
$Script:Report += New-Object PSObject -Property @{
‘Folder Name’ = $FileObject.FullName
‘Created on’ = $FileObject.CreationTime
‘Last Updated’ = $FileObject.LastWriteTime
Size = $Size
Owner = (Get-Acl $FileObject.FullName).Owner
}
}

Function CalculateSize {
Param (
[double]$Size
)
If ($Size -gt 1000000000)
{ $ReturnSize = “{0:N2} GB” -f ($Size / 1GB)
}
Else
{ $ReturnSize = “{0:N2} MB” -f ($Size / 1MB)
}
Return $ReturnSize
}

Function Set-AlternatingRows {
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True,ValueFromPipeline=$True)]
[object[]]$Lines,

    [Parameter(Mandatory=$True)]
   	[string]$CSSEvenClass,
   
    [Parameter(Mandatory=$True)]
    [string]$CSSOddClass
)
Begin {
	$ClassName = $CSSEvenClass
}
Process {
    ForEach ($Line in $Lines)
    {	$Line = $Line.Replace("<tr>","<tr class=""$ClassName"">")
		If ($ClassName -eq $CSSEvenClass)
		{	$ClassName = $CSSOddClass
		}
		Else
		{	$ClassName = $CSSEvenClass
		}
		Return $Line
    }
}

}

cls
$Report = @()
$TotSize = 0
$FSO = New-Object -ComObject Scripting.FileSystemObject

#First get the properties of the starting path
$Root = Get-Item -Path $Path
AddObject $Root

#Now loop through all the subfolders
$ParamSplat = @{
Path = $Path
Recurse = $Recurse
}
ForEach ($Folder in (Get-ChildItem @ParamSplat | Where { $_.PSisContainer }))
{ AddObject $Folder
}

#Create the HTML for our report
$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
.odd { background-color:#ffffff; }
.even { background-color:#dddddd; }
</style>
<Title>
Folder Sizes for “$Path”
</Title>
"@

$TotSize = CalculateSize $TotSize

$Pre = “<h1>Folder Sizes for “”$Path”"</h1><h2>Run on $(Get-Date -f ‘MM/dd/yyyy hh:mm:ss tt’)</h2>"
$Post = “<h2>Total Space Used In “”$($Path)”": $TotSize</h2></body></html>"

#Create the report and save it to a file
$HTML = $Report | Select ‘Folder Name’,Owner,‘Created On’,‘Last Updated’,Size | Sort ‘Folder Name’ | ConvertTo-Html -PreContent $Pre -PostContent $Post -Head $Header | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd | Out-File $ReportPath\FolderSizes.html

#Display the report in your default browser
& $ReportPath\FolderSizes.html

Perfect, you already have your $Report collection full of objects with properties ‘Folder Name’, ‘Created on’, ‘Last Updated’, ‘Size’, and ‘Owner’. You can just pipe $Report to either Export-Csv or ConvertTo-Csv, depending on whether you want to save the file on disk or just generate CSV data as a string in memory.

can you give an examlpe please

$Report | Export-Csv -Path $ReportPath\FolderSizes.csv -NoTypeInformation

thank you
i have one little question…
How can i select only the parts “Owner” and “Size”

I love it when a scripting language is so expressive that the answer to your question can be taken right out of the question itself. :slight_smile:

$Report | Select "Owner","Size" | Export-Csv -Path $ReportPath\FolderSizes.csv -NoTypeInformation

(Normally, I’d have used the full cmdlet name Select-Object, but I couldn’t resist using the word “Select” right out of your question.)

thanks :slight_smile:

hello again a little question…
I want to compare $Size in the script above with a variable $limit = 1, but i don’t know the datatyp of $Size…

In your script $Size starts out as a Double, and is then converted to a String (which happens to contain the trailing characters “GB” or “MB”). This is awkward for comparison, and this topic of early “fixing” of output is the topic of one of the Great Debate posts Don put up following the last Scripting Games: https://powershell.org/2013/08/27/powershell-great-debate-fixing-output/.

An alernative is to have your AddObject function just leave $Size as its raw, Double value. Then you can easily use it in a Where-Object filter of $Report later, if you’d like. Just before you send $Report to either ConvertTo-Html or Export-Csv, you can use Select-Object to convert the property to a human-readable form using your CalculateSize function. Something like this:

Function AddObject {
    Param (
        $FileObject
    )

    $Size = [double]($FSO.GetFolder($FileObject.FullName).Size)
    $Script:TotSize += $Size
    
    # Got rid of the code that converted $Size from a double to a String here

    $Script:Report += New-Object PSObject -Property @{
        'Folder Name' = $FileObject.FullName
        'Created on' = $FileObject.CreationTime
        'Last Updated' = $FileObject.LastWriteTime
        Size = $Size
        Owner = (Get-Acl $FileObject.FullName).Owner
    }
}

# Skipped to the end of the script.  Example of only exporting objects where Size is larger than 1GB, and still
# putting the formatted output from CalculateSize in the file.

$limit = 1GB

$Report |
Where-Object { $_.Size -gt $limit } |
Select-Object "Owner",@{Name='Size'; Expression = { CalculateSize $_.Size } } |
Export-Csv -Path $ReportPath\FolderSizes.csv -NoTypeInformation

thanks you
i searched in about 5 forums for an answer but You are the first person, who gave me a useful solution for my problem

THANK YOU

No problem! :slight_smile:

excuse me but i have again a question…
now i have the variable $Report and i filtered ou the parts size, Owner and Folder Name.
If i want filt out the sizes which are under 5000000000 and the rest should again be in a report
how could i do this

thanks again…

Please post the current version of your code, and I’ll take a look.

Param (
[string]$Path = “N:”,
[string]$ReportPath = “N:\Test”,
[switch]$Recurse
)

Function AddObject {
Param (
$FileObject
)

$Size = [double]($FSO.GetFolder($FileObject.FullName).Size)
$Script:TotSize += $Size

# Got rid of the code that converted $Size from a double to a String here

$Script:Report += New-Object PSObject -Property @{
    'Folder Name' = $FileObject.FullName
    'Created on' = $FileObject.CreationTime
    'Last Updated' = $FileObject.LastWriteTime
    Size = $Size
    Owner = (Get-Acl $FileObject.FullName).Owner
}

}

Function CalculateSize {
Param (
[double]$Size
)
If ($Size -gt 1000000)
{ $ReturnSize = ($Size / 1GB)
}
Else
{ $ReturnSize = ($Size / 1MB)
}
Return $ReturnSize
}

Function Set-AlternatingRows {
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True,ValueFromPipeline=$True)]
[object[]]$Lines,

    [Parameter(Mandatory=$True)]
   	[string]$CSSEvenClass,
   
    [Parameter(Mandatory=$True)]
    [string]$CSSOddClass
)
Begin {
	$ClassName = $CSSEvenClass
}
Process {
    ForEach ($Line in $Lines)
    {	$Line = $Line.Replace("&lt;tr&gt;","&lt;tr class=""$ClassName""&gt;")
		If ($ClassName -eq $CSSEvenClass)
		{	$ClassName = $CSSOddClass
		}
		Else
		{	$ClassName = $CSSEvenClass
		}
		Return $Line
    }
}

}

cls
$Report = @()
$TotSize = 0
$FSO = New-Object -ComObject Scripting.FileSystemObject

#First get the properties of the starting path
$Root = Get-Item -Path $Path
AddObject $Root

#Now loop through all the subfolders
$ParamSplat = @{
Path = $Path
Recurse = $Recurse
}
ForEach ($Folder in (Get-ChildItem @ParamSplat | Where { $_.PSisContainer }))
{ AddObject $Folder
}

#Create the HTML for our report
$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
.odd { background-color:#ffffff; }
.even { background-color:#dddddd; }
</style>
<Title>
Folder Sizes for “$Path”
</Title>
"@

$TotSize = CalculateSize $TotSize

$Pre = “<h1>Folder Sizes for “”$Path”"</h1><h2>Run on $(Get-Date -f ‘MM/dd/yyyy hh:mm:ss tt’)</h2>"
$Post = “<h2>Total Space Used In “”$($Path)”": $TotSize</h2></body></html>"

#Create the report and save it to a file
$HTML = $Report | Select ‘Folder Name’,Owner,‘Created On’,‘Last Updated’,Size | Sort ‘Folder Name’ | ConvertTo-Html -PreContent $Pre -PostContent $Post -Head $Header | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd | Out-File $ReportPath\FolderSizes.html

$limit = 1GB

$Report | Where-Object { $.Size -gt $limit } | Select-Object “Owner”,@{Name=‘Size’; Expression = { CalculateSize $.Size } } | Export-Csv -Path $ReportPath\FolderSizes.csv -NoTypeInformation

$Report = $Report | Select-object “Folder Name”,“Size”,“Owner”

Try changing the last few lines to this, see if it produces what you’re looking for. I broke the pipelines out into multiple lines (a new line after each pipe character) to help with readability, but the only functional change is that I assigned a new value to $limit, and added the filtering / conversion code to both the CSV and HTML pipelines.

#Create the report and save it to a file

$limit = 5000000000

$Report |
Where-Object { $_.Size -ge $limit } |
Select-Object 'Folder Name', 'Owner', 'Created On', 'Last Updated', @{ Name = 'Size'; Expression = { CalculateSize $_.Size } } |
Sort-Object 'Folder Name' |
ConvertTo-Html -PreContent $Pre -PostContent $Post -Head $Header |
Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd |
Out-File $ReportPath\FolderSizes.html

$Report |
Where-Object { $_.Size -ge $limit } |
Select-Object 'Owner', @{ Name = 'Size'; Expression = { CalculateSize $_.Size } } |
Export-Csv -Path $ReportPath\FolderSizes.csv -NoTypeInformation

[quote=11093]thanks you<br> i searched in about 5 forums for an answer but You are the first person, who gave me a useful solution for my problem
THANK YOU
[/quote]

@Dominique
We have replied at the same day you have posted with the same Answer!
Show us your code!
But you did not gave us a chance in the German PowerShell Forum! :frowning:
http://social.technet.microsoft.com/Forums/de-DE/9f885114-86b6-45cd-9dac-8ab225ee1bb3/powershell-wie-man-ein-html-report-in-einen-csvreport-umwandeln-kann?forum=powershell_de

Your forum isn’t included in the 5 forums i searched…
the problem was i couldn’t find my Thread anymore sry.
Next time i will aks you also in your forum.
It was a misunderstanding

Hello Dave Wyatt
The Script you send me didn’t work.
And it generate the csv and the html document but the documents are empty.