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

This topic contains 24 replies, has 3 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 3 years, 5 months ago.

  • Author
    Posts
  • #11061
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    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

  • #11062
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

  • #11063
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant


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

    Function AddObject {
    Param (
    $FileObject
    )
    $Size = [double]($FSO.GetFolder($FileObject.FullName).Size)
    $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("

    "," ")
    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 = @"

    <br /> Folder Sizes for "$Path"<br />
    "@

    $TotSize = CalculateSize $TotSize

    $Pre = "

    Folder Sizes for ""$Path""

    Run on $(Get-Date -f 'MM/dd/yyyy hh:mm:ss tt')

    "
    $Post = "

    Total Space Used In ""$($Path)"": $TotSize

    "

    #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

  • #11064
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

  • #11065
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    can you give an examlpe please

  • #11067
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator
    $Report | Export-Csv -Path $ReportPath\FolderSizes.csv -NoTypeInformation
    
  • #11068
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    thank you
    i have one little question...
    How can i select only the parts "Owner" and "Size"

  • #11069
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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. 🙂

    $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.)

  • #11070
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    thanks 🙂

  • #11089
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    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.....

  • #11092
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    
  • #11093
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    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

  • #11094
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    No problem! 🙂

  • #11096
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    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.......

  • #11097
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

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

  • #11098
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    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("

    "," ")
    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 = @"

    <br /> Folder Sizes for "$Path"<br />
    "@

    $TotSize = CalculateSize $TotSize

    $Pre = "

    Folder Sizes for ""$Path""

    Run on $(Get-Date -f 'MM/dd/yyyy hh:mm:ss tt')

    "
    $Post = "

    Total Space Used In ""$($Path)"": $TotSize

    "

    #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"

  • #11101
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    
  • #11107
    Profile photo of Peter Kriegel
    Peter Kriegel
    Participant

    [quote=11093]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
    [/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! 🙁
    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

  • #11108
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    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

  • #11176
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    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.

  • #11179
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Well, try this to see if there are any objects in $Report that would pass the Where-Object filter:

    $limit = 5000000000
    
    $props = @(
        @{ Label = 'SizeType'; Expression = { $_.Size.GetType().FullName } }
        'Size'
        @{ Label = 'SizeExceedsLimit'; Expression = { $_.Size -gt $limit } }
    )
    
    $Report | Select-Object -Property $props
    
  • #11187
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    Hello
    The Script above give me a result like this (picture is in the attachment).

  • #11189
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    There you go, then. The sizes were all under your specified limit, so the reports are supposed to be empty.

  • #11190
    Profile photo of Dominique Hofmann
    Dominique Hofmann
    Participant

    mhh now i get it
    You're genius thanks
    But again a question now does $Report contain only the Sizes and if there are true or false, but is it possible to add the owner and the folder names ?

  • #11191
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    The last code I posted was just for debugging purposes, you can remove it from the script any time. It did not actually change the value of $Report, though; just used $Report as a starting point.

You must be logged in to reply to this topic.