Renaming Files by Padding Numbers with PowerShell

Some time ago I had downloaded all the SQL Saturday XML files. I’ve been meaning to flatten those into a database, but that project keeps getting away from me. And now it’s become an archive task.

In any case, I’ve been looking to work with Jekyll and get a list of events. There is one at SQLSatHistory.com, but I’m going to try and do a bit more than a simple list. In any case, I have found a small issue: my files were named SQLSat1,xml, SQLSat2.xml, etc.

That’s not a big problem, but it results in my archive looking like:

  • SQL Saturday #1 – Orlando
  • SQL Saturday #100 – Brazil
  • SQL Saturday #101 – Kansas City
  • etc.

The file names are strings, and in alpha sorting, 100 comes before 2. That’s not critical, but it’s not what I want, and I would like something that’s a bit better and easier to follow.

The easiest way to do this is to rename the files. I have some data in the files, and I have those being parsed, so if I can rename the files, I can get the events generated in an orderly fashion.

PowerShell has a Rename-Item cmdlet, which will work, but what new name? I need to parse out the filename and then come up with a new one. Likely there are better methods, but this worked quickly for me to rename some files.

The Process

My thought in doing this was first to extract out the number and then check the length. From there, I can assemble a new string. Instead, as I was starting, I thought of a better way. I decided to remove everything but the number.

There is a replace method for strings. As In, I can do this:

$EventNumber = $XmlFile.Name -replace 'SQLSat', ''

This will take away the string before the number, and results in file names going from:

  • SQLSat1.xml
  • SQLSat2.xml
  • SQLSat3.xml

to

  • 1.xml
  • 2.xml
  • 3.xml

I repeated that and removed the .xml as well, which gave me each event’s number. From here, I found a cool trick on Stack Overflow to do this. The PadLeft method is used

 % PadLeft 4 '0'

I use 4 as a padding factor. This results in giving me what I want, and when I concatenate this, I get the file names I want.

$NewFile = "SQLSat" + $EventNumber + ".xml"

From there, a simple call to Rename-Item with each file resulted in an orderly list of events.

2021-01-07 13_21_43-xml

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

2 Responses to Renaming Files by Padding Numbers with PowerShell

  1. brianary says:

    Another way to do this would use the undocumented (or at least underdocumented) feature of the -replace operator: that the final param can be a code block.

    The code block is executed with $PSItem ($_ for short) defined as the regex match, which requires indexing into the Groups property to get to each match group, and the Value property of that to get to the actual string value. There’s a trick to do this succinctly: $_ = $_.Groups.Value . When accessing a property of a collection that isn’t part of the collection object, PowerShell will try to access a named property of each collection element and return each in a new collection.

    Finally, using the -f formatting operator makes zero-padding a bit easier.

    Putting it all together:

    $NewFile = $XmlFile.Name -replace '\A(\D+)(\d+)(\..*)\z',{ $_=$_.Groups.Value; $_[2]=[int]$_[2]; '{1}{2:0000}{3}' -f $_ }

    Like

    • way0utwest says:

      Thanks, some of this makes sense, but I hate the regex stuff. I find it means I end up spending a bunch of time decoding how this works later if I maintain it. I guess I don’t use regex enough.

      Like

Leave a Reply to brianary Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.