List Music File Metadata in a CSV and Open in Excel with PowerShell

Doctor Scripto

Summary: Microsoft Scripting Guy, Ed Wilson, shares a function that gets file metadata, and then he writes the information to a CSV file.

 Hey, Scripting Guy! Getting basic file properties such as the file name, the size of a file, and the directory that a file resides in is pretty cool. But when I go into a folder that contains music files, I see much more information, such as the name of the music group, the type of music, and how long the song will play. Can I get that type of information via Windows PowerShell?

—DD

 Hello DD,

Microsoft Scripting Guy, Ed Wilson, is here. In just a few days it will be time for Windows PowerShell Saturday in Charlotte, North Carolina. If you have not gotten your ticket yet, you need to do so ASAP before they are all gone. The lineup of speakers is really top shelf, and the subjects vary from beginner to advanced, with a special emphasis on Windows PowerShell scripting and security. It is truly a special event, and you do not want to miss it. To register, go to PowerShell Saturday #007.

DD, to answer your question, I decided to write a custom Windows PowerShell function to retrieve the file metadata. I uploaded the function to the Script Center Repository: get file meta data function. You can download it or copy it from that location. I uploaded the function as a .ps1 file, and I copied it to the tool, so you can simply copy it into your Windows PowerShell ISE, if you wish.

     Note  This is the third in a series of posts that talk about working with files and folders by using Windows PowerShell.
     You should read the first two posts:

• List Files in Folders and Subfolders with PowerShell
• Use PowerShell to Create CSV File to Open in Excel

Get the file metadata

The first thing that needs to happen is to get the file metadata. To do this, I modified a script that I wrote in 2008: How can I find Files’ Metadata? You should look at that post if you want more information about what I am doing today. This is because the Shell.Application interface has not changed in this regard, and it still works the same way.

The neat thing about that script is that I already output the data as a hash table, so it was very easy to modify to create a custom PSObject for my output instead. I still need to iterate through 266 possible metadata values for each file to find the ones I need. So, the script is a bit slow when dealing with thousands of potential files. But I only need to eat the elephant one time, and so it is not too bad.

In Windows Explorer, I see columns that do not appear as file properties. This is the file metadata. By default, Windows Explorer only displays a subset of the metadata. The default view is shown here:

I can get detailed metadata information for a particular file by right-clicking it and selecting Details. Each of these fields is available via the files metadata. Here is an example:

Using the Get-FileMetaData function

I open the script that contains my Get-FileMetaData function in the Windows PowerShell ISE. I then run the script (which only contains the Get-FileMetaData function) so that it loads the function. This is shown in the image here:

The cool thing is that now when I call the Get-FileMetaData function and I supply a value for the –Folder parameter, IntelliSense pops up and displays a list of the subfolders. I only have to select a folder as shown here:

After selecting the folder, I press ENTER, and it gets the metadata for each file in the folder. Keep in mind that when using it like this, it is not recursive. Here is an image of some of the output from the command:

Now, I want to select only the particular metadata values that I am interested in obtaining, so I use the following command:

Get-FileMetaData E:\music\DizzyGellispie | select Title, Length, authors, album, genre

The command and the output from the command are shown here:

Cool, so I know what I am interested in doing. Now I am going to use Get-ChildItem to retrieve all the folders in my E:\music directory, and I am going to store the results in a variable. This command is shown here:

$fileMeta = Get-FileMetaData -folder (gci e:\music -Recurse -Directory).FullName

When I run that command, I select the properties that I am interested in seeing, and I write the output to a CSV file. The following is a one-line command that is broken at the pipe for readability:

$fileMeta | select Title, Length, authors, album, genre |

Export-Csv -Path c:\fso\musicMeta.csv -Encoding ascii -NoTypeInformation

I can now use Invoke-Item (ii) to open the file and see the Excel spreadsheet. The Invoke-Item command is shown here:

ii C:\fso\musicMeta.csv  

The Microsoft Excel spreadsheet with the songs and the selected metadata looks like this:

Remember, I uploaded the function to the Script Center Repository: get file meta data function. You can download it or copy it from that location.

DD, that is all there is to using a custom function to get file metadata. File Week will continue tomorrow when I will talk about working with photo metadata.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

Doctor Scripto Scripter, PowerShell, vbScript, BAT, CMD

Follow