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

Compressing files in a MVC environment

This article will present a way to compress files in a MVC environment. For compression, we will use the DotNetZip Nuget package, which is an open and free compression library hosted on Codeplex and supported also by Xceed. The DotNetZip produces of course Zip files. DotNetZip website We install this compression library by initiating the following Nuget command: Install-Package DotNetZip We then define a simple view in MVC that has got a file upload input and a submit button:

@model ZipAndMvc.Models.HomeViewModel
@{
    ViewBag.Title = "Home Page";
}

<div class="jumbotron">
    <h2>Test out zipping a file</h2>   
</div>

<div class="row">

    @using (Html.BeginForm("ZipIt", "Home", FormMethod.Post, new {  enctype = "multipart/form-data" }))
    {
        <div class="col-md-3">@Html.Label("Zip password") @Html.TextBoxFor(m => m.ZipPassword) </div>
        <div class="col-md-3"><input type="file" name="FileUpload" /> </div>
        <div>  <input type="submit" id="Submit" value="Upload and zip file" /> </div>
    }
</div>

This view allows the user to type in a password for the file to compress, where the user also selects the file to compress. The user then hits the submit button. The HomeViewModel is very simple with a simple property for setting the zip password. Then we define the following code in the MVC controller:

        public FileStreamResult ZipIt(HomeViewModel viewmodel)
        {
            if (Request.Files.Count > 0)
            {
                using (var zip = new ZipFile())
                {
                    zip.Encryption = EncryptionAlgorithm.PkzipWeak;
                    zip.Password = viewmodel.ZipPassword;
                    zip.CompressionLevel = Ionic.Zlib.CompressionLevel.Default; 
                    var memoryStream = new MemoryStream();
                    zip.AddFile(Request.Files[0].FileName, "");
                    zip.Save(memoryStream);
                    memoryStream.Position = 0;
                    return new FileStreamResult(memoryStream, contentType: "application/zip")
                    {
                        FileDownloadName = Path.ChangeExtension(Request.Files[0].FileName, "zip")
                    };
                }
            }
            return null;
        }

The client posts the file to compress. The controller then inspects the Request.Files collection and selects the first file if there is present any files there. Here we return a FileStreamResult where the compressed data inside the memorystream is returned to the client. We use DotNetZip to do the compression. The benefit of DotNetZip compared to .Net built-in support for compression is more functionality. The code above should be sufficient for basic compression scenario in MVC. Feel free to experiment with DotNetZip. As you can see, you can specify compression level. You can also choose to add directories and much more. The reason for the second argument in AddFile method is to ensure that the file to be added to the zip package is put in the root folder of the zipped file. Also, set the values of Encryption and Password before adding files or directories (Folders) in the ZipFile. You can actually use different passwords also in the Zip file.

Finding old Git Branches with WSL and Bash

Finding old branches in Git

I had to find out which branches in a Git repository was old and output it to a file. An old branch is defined to have no commits the last four months. Here is the bash script I ended up with.

#!/bin/bash
resolveOldBranches(){
branchfile="oldbranches.txt"
declare -i branchiteration=0
branchcount=$(git branch -a | wc -l)

if [ ! -e $branchfile ] ; then
 touch $branchfile
fi

#empty the oldbranch file
: > $branchfile

for k in $(git branch -a | sed /\*/d); do


 if [ -z "$(git log -1 --since='4 months ago' -s $k)" ]; then
  echo $k | cut -d/ -f3 >> $branchfile
 fi
 branchiteration=$branchiteration+1
 percentage= bc <<< "scale=2;($branchiteration/$branchcount)*100"


 read -n 1 -t 0.1 input                  # so read doesn't hang
   if [[ $input = "q" ]] || [[ $input = "Q" ]]
   then
      echo # to get a newline after 
echo -e "XXX\n$($percentage)\nAnalyzing $branchiteration of $branchcount $(bc <<< "scale=2;($branchiteration/$branchcount)*100") % done. \n(Exit: Q/q)... \nXXX"

done | whiptail --title "Resolving OpPlan 4 branch ages" --gauge "Analyzing.. (Press Q or q to exit)" 10 60 0


}

resolveOldBranches
cat $branchfile