locked
How to analyze the PowerShell console output with tools such as Excel and Access etc. RRS feed

  • Question

  • In order to find out why system drive used up so fast, I modified the script described in https://blogs.technet.microsoft.com/heyscriptingguy/2012/05/25/getting-directory-sizes-in-powershell/  to something as follows

    
    


    Save the script into Get-DirStats.ps1 , and run with command as follows:
    .\get-dirstats.ps1 -Path C:\ -ErrorAction SilentlyContinue -FormatNumbers -Every|format-table @{label="path";expression={$_.path};width=160},@{label="files";expression={$_.files};width=20;},@{label="size";expression={$_.size};width=20}

    My question is, how can I redirect the PowerShell console output to something that can be easily used in data analysis tools such as Excel and Access. Currently, I just format the string output with special separator character such as "|", copy and paste the result PowerShell console output to a text file and then import and the data into MS Access (Access let you specify the separator character).

    But I think there should be better approach, at least I don't need to manually copy and paste.




    • Edited by GuYuming Friday, October 12, 2018 5:55 AM
    Monday, October 8, 2018 2:17 AM

Answers

  • help export-csv -online


    \_(ツ)_/

    • Proposed as answer by SWamsley Monday, October 15, 2018 9:02 PM
    • Marked as answer by Richard MuellerMVP Friday, October 19, 2018 1:49 PM
    Monday, October 8, 2018 2:21 AM

All replies

  • help export-csv -online


    \_(ツ)_/

    • Proposed as answer by SWamsley Monday, October 15, 2018 9:02 PM
    • Marked as answer by Richard MuellerMVP Friday, October 19, 2018 1:49 PM
    Monday, October 8, 2018 2:21 AM
  • # Get-DirStats.ps1
    # Written by Bill Stewart (bstewart@iname.com)
    # Outputs file system directory statistics.
    
    #requires -version 2
    
    <#
    .SYNOPSIS
    Outputs file system directory statistics.
    
    .DESCRIPTION
    Outputs file system directory statistics (number of files and the sum of all file sizes) for one or more directories.
    
    .PARAMETER Path
    Specifies a path to one or more file system directories. Wildcards are not permitted. The default path is the current directory (.).
    
    .PARAMETER LiteralPath
    Specifies a path to one or more file system directories. Unlike Path, the value of LiteralPath is used exactly as it is typed.
    
    .PARAMETER Only
    Outputs statistics for a directory but not any of its subdirectories.
    
    .PARAMETER Every
    Outputs statistics for every directory in the specified path instead of only the first level of directories.
    
    .PARAMETER FormatNumbers
    Formats numbers in the output object to include thousands separators.
    
    .PARAMETER Total
    Outputs a summary object after all other output that sums all statistics.
    #>
    
    [CmdletBinding(DefaultParameterSetName="Path")]
    param(
      [parameter(Position=0,Mandatory=$false,ParameterSetName="Path",ValueFromPipeline=$true)]
        $Path=(get-location).Path,
      [parameter(Position=0,Mandatory=$true,ParameterSetName="LiteralPath")]
        [String[]] $LiteralPath,
        [Switch] $Only,
        [Switch] $Every,
        [Switch] $FormatNumbers,
        [Switch] $Total
    )
    
    begin {
      $ParamSetName = $PSCmdlet.ParameterSetName
      if ( $ParamSetName -eq "Path" ) {
        $PipelineInput = ( -not $PSBoundParameters.ContainsKey("Path") ) -and ( -not $Path )
      }
      elseif ( $ParamSetName -eq "LiteralPath" ) {
        $PipelineInput = $false
      }
    
     # Script-level variable for output file name
     [string] $script:DirStatsOutputfile= $MyInvocation.MyCommand.Definition.Replace(".ps1","output"+[System.DateTime]::Now.Year.ToString()+[System.DateTime]::Now.Month.ToString() + ".csv")
     [string] $script:DirStatsTotalOutputfile= $MyInvocation.MyCommand.Definition.Replace(".ps1","totaloutput"+[System.DateTime]::Now.Year.ToString()+[System.DateTime]::Now.Month.ToString() + ".csv")
    
    
      # Script-level variables used with -Total.
      [UInt64] $script:totalcount = 0
      [UInt64] $script:totalbytes = 0
    
      # Returns a [System.IO.DirectoryInfo] object if it exists.
      function Get-Directory {
        param( $item )
    
        if ( $ParamSetName -eq "Path" ) {
          if ( Test-Path -Path $item -PathType Container ) {
            $item = Get-Item -Path $item -Force
          }
        }
        elseif ( $ParamSetName -eq "LiteralPath" ) {
          if ( Test-Path -LiteralPath $item -PathType Container ) {
            $item = Get-Item -LiteralPath $item -Force
          }
        }
        if ( $item -and ($item -is [System.IO.DirectoryInfo]) ) {
          return $item
        }
      }
    
      # Filter that outputs the custom object with formatted numbers.
      function Format-Output {
        process {
          $_ | Select-Object Path,
            @{Name="Files"; Expression={"{0:N0}" -f $_.Files}},
            @{Name="Size"; Expression={"{0:N0}" -f $_.Size}}
        }
      }
    
      # Outputs directory statistics for the specified directory. With -recurse,
      # the function includes files in all subdirectories of the specified
      # directory. With -format, numbers in the output objects are formatted with
      # the Format-Output filter.
      function Get-DirectoryStats {
        param( $directory, $recurse, $format )
    
        Write-Progress -Activity "Get-DirStats.ps1" -Status "Reading '$($directory.FullName)'"
        $files = $directory | Get-ChildItem -Force -Recurse:$recurse | Where-Object { -not $_.PSIsContainer -and $_.LastWriteTime -ge [system.datetime]::Now.AddDays(([system.datetime]::Now.Day)*(-1))  }
        if ( $files ) {
          Write-Progress -Activity "Get-DirStats.ps1" -Status "Calculating '$($directory.FullName)'"
          $output = $files | Measure-Object -Sum -Property Length | Select-Object `
            @{Name="Path"; Expression={$directory.FullName}},
            @{Name="Files"; Expression={$_.Count; $script:totalcount += $_.Count}},
            @{Name="Size"; Expression={$_.Sum; $script:totalbytes += $_.Sum}},
            @{Name="TimeStamp"; Expression={[system.datetime]::Now}}
        }
        else {
          $output = "" | Select-Object `
            @{Name="Path"; Expression={$directory.FullName}},
            @{Name="Files"; Expression={0}},
            @{Name="Size"; Expression={0}},
            @{Name="TimeStamp"; Expression={[system.datetime]::Now}}
        }
        if ( -not $format ) { $output|where{$_.Files -gt 0 -and $_.size -gt 100000000;}|Export-csv $script:DirStatsOutputfile -Append } else { $output| Format-Output |where{$_.Files -gt 0 -and $_.size -gt 100000000;}|Export-csv $script:DirStatsOutputfile -Append }
      }
    }
    
    process {
      # Get the item to process, no matter whether the input comes from the
      # pipeline or not.
      if ( $PipelineInput ) {
        $item = $_
      }
      else {
        if ( $ParamSetName -eq "Path" ) {
          $item = $Path
        }
        elseif ( $ParamSetName -eq "LiteralPath" ) {
          $item = $LiteralPath
        }
      }
    
      # Write an error if the item is not a directory in the file system.
      $directory = Get-Directory -item $item
      if ( -not $directory ) {
        Write-Error -Message "Path '$item' is not a directory in the file system." -Category InvalidType
        return
      }
    
      # Get the statistics for the first-level directory.
      Get-DirectoryStats -directory $directory -recurse:$false -format:$FormatNumbers
      # -Only means no further processing past the first-level directory.
      if ( $Only ) { return }
    
      # Get the subdirectories of the first-level directory and get the statistics
      # for each of them.
      $directory | Get-ChildItem -Force -Recurse:$Every |
        Where-Object { $_.PSIsContainer } | ForEach-Object {
          Get-DirectoryStats -directory $_ -recurse:(-not $Every) -format:$FormatNumbers
        }
    }
    
    end {
      # If -Total specified, output summary object.
      if ( $Total ) {
        $output = "" | Select-Object `
          @{Name="Path"; Expression={"<Total>"}},
          @{Name="Files"; Expression={$script:totalcount}},
          @{Name="Size"; Expression={$script:totalbytes}},
          @{Name="TimeStamp"; Expression={[system.datetime]::Now}}
        if ( -not $FormatNumbers ) { $output|Export-csv $script:DirStatsTotalOutputfile -Append  } else { $output | Format-Output|Export-csv $script:DirStatsTotalOutputfile -Append  }
      }
    }
    

    Friday, October 12, 2018 5:55 AM
  • You have several options here. The easiest and probably the best for your purposes is Export-Csv as jrv notes above. You could also directly instantiate an excel or access com object and push the data straight in however you will find that this can take a really long time if you have a large dataset. It is somewhat fun to watch it work though.

    With export-csv, if you have characters that are not represented in UTF8 encoding use the -encoding switch.

    Monday, October 15, 2018 9:11 PM