PowerShell

Organize Camera Photos and Videos Using PowerShell

I was searching for a quick and easy way to organize the contents of my cameras SD card when I transfer the files off to my computer.  It would be great to have the time to organize all the photos per event (Grandpa’s Birthday, Birth of Child Number 2, etc), but this never works.  I always put off that task and I end up with a monster SD card with an unmanageable amount of photos and videos.  The problem is compounded by the additional directories that the camera uses for its organization.

Well I found a simple PowerShell script that did this online (see here, a blog post from Kim Oppalfens), but there were some problems.

  1. The directories were too granular (one directory for each day of photos).
  2. It only handled photos.
  3. My camera doesn’t embed the date in the jpg the way that this script was expecting, resulting in some null ref errors when running the script.

So I wrote my own which I’ll share with you here, you can also download a text version here.

$PhotosDirectory="d:\Photos & Movies"

Function CopyFiles([System.IO.FileInfo[]]$Files)
{
    $currentCount = 0
    $percent = 0;

    Write-Host "  0% Complete"
    foreach ($file in $Files)
    {
        $currentCount++
        if ($currentCount -gt ($Files.Length / 10))
        {
            $percent += 10
            $percentString = $percent.ToString().PadLeft(3, ' ')
            Write-Host "$percentString% Complete"
            $currentCount = 0
        }

        $year = $file.CreationTime.Year.ToString()
        $month = $file.CreationTime.Month.ToString().PadLeft(2, '0')
        $monthName = $file.CreationTime.ToString("MMMM") # <-- Month Name

        $TargetDirectory = "$year\$month - $monthName"
        $TargetPath = "$PhotosDirectory\$TargetDirectory"

        if (!(test-path $targetpath))
        {
            new-item $targetpath -type directory >> log.txt
        }
        xcopy /y $file.fullname "$targetpath" >> log.txt
    }
    Write-Host "100% Complete"
}

Write-Host "Copying JPG's"
$files = Get-ChildItem -recurse -filter *.jpg
CopyFiles($files)
Write-Host "Copying MOV's"
$files = Get-ChildItem -recurse -filter *.mov
CopyFiles($files)

Couple key points

  1. Copy contents of above script and past into a new file.  I named mine Photos.ps1 and put in my C:\Users\<user name>\Documents\WindowsPowerShell directory.  The name doesn’t matter, but the extension of ps1 does.
  2. Launch PowerShell.
  3. Insert you SD card, and navigate to the directory where your photos or stored.  I did this by inserting the SD card into my reader, but the usb cord worked as well.
  4. Run your ps1 file by typing the full path and file name.

The result is a well organized directory of your digital photos and videos.

Using PowerShell to run SQL

This morning I read Scott Hanselman’s 2011 Ultimate Developer Power Tools and I was again reminded that PowerShell is an essential development tool that I haven’t learned.  I’ve given PowerShell a try on a couple different occasions all of them unsuccessful.  Today I have vowed to give PowerShell two hours for one final try.  The first thing I’d need is some concrete problem that I can use PowerShell for to improve my productivity on a daily basis.  Thinking it through I noticed that a couple times a day I find myself wanting to execute a simple piece of SQL.  Typically that workflow will look like.

  1. Launch SQL Management Studio
  2. Write and execute some SQL or run a stored procedure
  3. Close SQL Management Studio

That is pretty heavy handed way to just run some simple SQL, so I decided to give this a try doing using PowerShell instead of SQL Management Studio.

Install PowerShell

Windows 7 comes pre-loaded with PowerShell so I can skip that step.  If you’re not on Windows 7 you can find the download guide on the Widows PowerShell Blog.

Install PowerShell Community Extensions

Scott had a link to the the PowerShell Community Extensions CodePlex project, and their installation instructions were pretty straight forward.  However, I did run into one gotcha on this step.  After downloading the zip file for the installation I didn’t change the security attribute to trust the download.  That step is a must or you’ll run into trust issues when running the extensions.  To be fair the download guide on the CodePlex site pointed this out, but I was moving fast and skipped for no good reason.

Import the Community Extensions every time I run PowerShell

I simply added “Import-Module PSCX” as the first line of my Microsoft.PowerShell_profile.ps1 file (I had to create this file and place in <MyDocuments>\WindowsPowerShell\).  My full Microsoft.PowerShell_profile.ps1 is available later in this post.

Write functions allowing for easy database access

This was by far the most difficult step.  I’m not very accustomed to the PowerShell syntax and I found my self trying to write C# most of the time.  That didn’t work and caused me a lot of pain.  I followed some examples on the web, fumbled around for a while, and finally got it all working.  Microsoft is very upfront that PowerShell is a new language their getting started guide (read more here), but I still struggled with this.

Execute a command and return a dataset:

Function ExecuteDataSet([string]$connString, [string]$commandText)
{
    $conn = Get-AdoConnection -Provider $Provider -ConnectionString $connString

    $ds = Invoke-AdoCommand -ProviderName $Provider -Connection $conn -CommandText $commandText -AsDataSet

    $conn.Close()
    $conn.Dispose()

    return $ds
}

Create a simple sp_help wrapper

Function sp_help ([string]$connString, [string]$name)
{
    $ds = ExecuteDataSet $connString "sp_help $name"
    $ds.Tables[1] | Format-Table Column_Name, Type, Length, Prec, Scale, Nullable -AutoSize
}

Create a wrapper that sends in my dev connection string

$ConnectionDev="Data Source=(local);Database=my_db;UID=sa;PASSWORD=XXXX;"

Function sp_help_dev ([string]$name)
{
    sp_help $ConnectionDev $name
}

See them in action

The result were some simple functions that I could call for simple db queries.   In the below example I am able to find out about the columns of a table, and run a SQL statement to aggregate some statistics (look in full ps1 file for exec_sql functions).

RunningSQL_thumb[5]

Summary

All and all I’m a lot happier with this attempt at PowerShell then I have been in the past.  I know that I’m only scratching at the surface of its capabilities, but by spending 2 hours with PowerShell and learning some of these simple capabilities I can defiantly speed up this common daily activity.

The full text of my Microsoft.PowerShell_profile.ps1 file could be found here.