I don’t know about this blog post title, it doesn’t quite explain what I needed to do. Anyway.
Months and months ago, I downloaded all the SharePoint 2011 videos (using some PowerShell) and it worked great. However, the videos were all named with their session code, for example SPC201.wmv. See this post on how it was done: http://www.sharepointeer.com/blog/Lists/Posts/Post.aspx?ID=4
The file names are not very descriptive, but luckily a gentleman named Patrick Drews provided a session list in Excel that matched the code with the title. It looked like this:
| Code | Session |
| SPC201 | Applying a Brand to your SharePoint Web Site |
Armed with this, we should be able to rename the files so they’re descriptive. Here’s what I did.
The first problem is how to interact with this Excel spreadsheet. There are a lot of opinions on this, because natively, there’s no real easy way to do it. Many times, converting to CSV is the easiest. So, I found this function that does just that. It’s available from this site: http://jamesone111.wordpress.com/2011/02/07/how-to-read-excel-files-in-powershell/
Great! The next problem was to figure out the logic. First, lets define some variables:
$dir = "C:\SPC11"
$xlsessions = gci $dir\SessionList.xlsx | ConvertFrom-XLx -PassThru
$filenames = gci $dir\Videos | select -ExpandProperty Name
I end up with two arrays, $xlsessions which has the codes and session titles and $filenames which is just a list of the filenames. I need to match the filename with the session code for each file.
foreach ($filename in $filenames)
{
foreach ($xlsession in $xlsessions)
{
If (($xlsession.CODE + ".wmv") -match $filename)
{ #Do something }
}
}
This was actually the hardest part (for me anyway, because I’m a PowerShell n00b). Basically, we loops through each filename and each time we also loop through each of the session codes. If they match, then we’ll do something. Here’s what we’ll do:
{
$newname = $xlsession.CODE + " - " + $xlsession.SESSION + ".wmv"
write-host "Renaming $filename to $newname" -ForegroundColor Yellow
Rename-Item -Path $dir\Videos\$filename -NewName $dir\Videos\$newname
}
First, I’ve setup a variable called $newname that sets up the new filename for me. Then, I’m using Rename-Item to rename it. It seems simple enough, but took me a couple hours to get the logic right. Once I ran it, my files were renamed just as I wanted them in a matter of seconds. It would have taken hours upon hours to do this manually, cutting and pasting names from the Excel sheet into Windows Explorer. PowerShell saves the day again! Now take some time and save Live Writer….
This post was created with Windows Live Writer. Save this great tool by signing the petition here. Learn more by following #dontkillwlw on twitter.