Powershell - Search for big files and output to Excel

Here is a Powershell script that you can run to search for the ten biggest files in your current file directory and display them in Excel. My version of Powershell is 5.1 according to the built in Powershell variable $PSVersionTable.PSVersion, so if you run the script in earlier versions of Powershell, the script must be changed a bit.

Write-Host 'Look for big files in current directory' 

$alternatingOddRowColor = 19
$alternatingEventRowColor = 20

$filename = ''

#$filename = Read-Host 'Directory to output filelist? (c:\temp) default'


if ([string]::IsNullOrEmpty($filename)){
 $filename = "c:\temp\\" 
}

$filename = $filename + 
 ([TimeSpan](Get-Date).ToLongTimeString()).Ticks.ToString() + '_Bigfiles' + '.csv'

Write-Host $filename


gci -r | sort Length -desc |
Select-Object @{Name='Filesize(MB)'; Expression = { [int]$($_.Length /1MB) }}, Name, FullName, LastWriteTime -First 10 | 
Export-Csv $filename -NoTypeInformation -Encoding ASCII -UseCulture

$excelFileName = $filename.Replace('.csv', '.xlsx')


$excel = New-Object -ComObject Excel.Application 
$excel.Visible = $true
$excel.Workbooks.Open($filename)

$excel.DisplayAlerts = false

$objWorksheet = $excel.Workbooks.Item(1)

$activeRange = $excel.ActiveWorkbook.ActiveSheet.UsedRange

$activeRange.EntireColumn.AutoFit()

For ($i = 1; $i -le $activeRange.Rows.Count; $i++) { 
 $themeColorIndex = $(If ($($i %2) -eq 0) { $alternatingOddRowColor } 
   Else { $alternatingEventRowColor });

 $currentRow = $excel.ActiveWorkbook.ActiveSheet.UsedRange.Rows($i).EntireRow
 $currentRow.Font.Name = 'Comic Sans MS'
 $currentRow.Interior.ColorIndex =  $themeColorIndex;
 if ($i -eq 1){
  $currentRow.Font.Bold = true 
  $currentRow.Font.Size = 14
 } 
}


$excel.SaveAs($excelFileName,51)

#$excel.Quit()

A timestamp can be created in Powershell using the Get-Date cmdlet, converting the Get-Date DateTime object to a string with ToLongTimeString(), then casting that string into a timestamp and performing a ToString. Like this:

 ([TimeSpan](Get-Date).ToLongTimeString()).Ticks.ToString()

It is nice to have a file size in megabyte with Get-ChildItem That can be achieved using a calculated property in Powershell. This is done with the following construct ${ .. }

@{ Name : 'PropertyName', Expression { $( ..calculation here .. ) }}

#Like this! 

gci -r | sort Length -desc |
Select-Object  @{Name='Filesize(MB)'; Expression = { [int]$($_.Length /1MB) }} , Name, FullName, LastWriteTime -First 10 | 
Export-Csv $filename -NoTypeInformation -Encoding ASCII -UseCulture

After running the Powershell script I could find out why my source code repository seemingly had grown so much in size. The repo was not increased, but the folder Test Results contained almost a gigabyte of disk space after running some web tests and load tests. My .hgignore file ignore these files anyways. The picture below shows how the generated file looks. Of course with a Comic Sans MS. Font

Share this article on LinkedIn.