{"id":2065,"date":"2014-02-05T00:01:00","date_gmt":"2014-02-05T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2014\/02\/05\/list-music-file-metadata-in-a-csv-and-open-in-excel-with-powershell\/"},"modified":"2022-06-20T13:45:18","modified_gmt":"2022-06-20T20:45:18","slug":"list-music-file-metadata-in-a-csv-and-open-in-excel-with-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/list-music-file-metadata-in-a-csv-and-open-in-excel-with-powershell\/","title":{"rendered":"List Music File Metadata in a CSV and Open in Excel with PowerShell"},"content":{"rendered":"<p><strong>Summary<\/strong>: Microsoft Scripting Guy, Ed Wilson, shares a function that gets file metadata, and then he writes the information to a CSV file.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" alt=\"Hey, Scripting Guy! Question\" \/>\u00a0Hey, 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?<\/p>\n<p>\u2014DD<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" alt=\"Hey, Scripting Guy! Answer\" \/>\u00a0Hello DD,<\/p>\n<p>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 <a href=\"http:\/\/powershellsaturday.com\/007\/conference\/powershell-saturday-007-charlotte-nc-february-8-2014\/\" target=\"_blank\" rel=\"noopener\">PowerShell Saturday #007<\/a>.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-01.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-01.png\" alt=\"Image of logo\" title=\"Image of logo\" \/><\/a><\/p>\n<p>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: <a href=\"http:\/\/gallery.technet.microsoft.com\/scriptcenter\/get-file-meta-data-function-f9e8d804\" target=\"_blank\" rel=\"noopener\">get file meta data function<\/a>. 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.<\/p>\n<p><strong>\u00a0 \u00a0 \u00a0Note\u00a0<\/strong> This is the third in a series of posts that talk about working with files and folders by using Windows PowerShell.<br \/>\n\u00a0 \u00a0 \u00a0You should read the first two posts:<\/p>\n<ul>\n<li><a href=\"https:\/\/devblogs.microsoft.com\/scripting\/list-files-in-folders-and-subfolders-with-powershell\/\" target=\"_blank\" rel=\"noopener\">List Files in Folders and Subfolders with PowerShell<\/a><\/li>\n<li><a href=\"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-create-csv-file-to-open-in-excel\/\" target=\"_blank\" rel=\"noopener\">Use PowerShell to Create CSV File to Open in Excel<\/a><\/li>\n<\/ul>\n<h2>Get the file metadata<\/h2>\n<p>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: <a href=\"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-find-files-metadata\/\" target=\"_blank\" rel=\"noopener\">How can I find Files\u2019 Metadata?<\/a> 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.<\/p>\n<p>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.<\/p>\n<p>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:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-02.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-02.png\" alt=\"Image of menu\" title=\"Image of menu\" \/><\/a><\/p>\n<p>I can get detailed metadata information for a particular file by right-clicking it and selecting <strong>Details<\/strong>. Each of these fields is available via the files metadata. Here is an example:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-03.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-03.png\" alt=\"Image of menu\" title=\"Image of menu\" \/><\/a><\/p>\n<h2>Using the Get-FileMetaData function<\/h2>\n<p>I open the script that contains my <strong>Get-FileMetaData<\/strong> function in the Windows PowerShell ISE. I then run the script (which only contains the <strong>Get-FileMetaData<\/strong> function) so that it loads the function. This is shown in the image here:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-04.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-04.png\" alt=\"Image of command output\" title=\"Image of command output\" \/><\/a><\/p>\n<p>The cool thing is that now when I call the <strong>Get-FileMetaData<\/strong> function and I supply a value for the <strong>\u2013Folder<\/strong> parameter, IntelliSense pops up and displays a list of the subfolders. I only have to select a folder as shown here:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-05.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-05.png\" alt=\"Image of command output\" title=\"Image of command output\" \/><\/a><\/p>\n<p>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:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-06.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-06.png\" alt=\"Image of command output\" title=\"Image of command output\" \/><\/a><\/p>\n<p>Now, I want to select only the particular metadata values that I am interested in obtaining, so I use the following command:<\/p>\n<p style=\"margin-left:30px\">\n  Get-FileMetaData E:\\music\\DizzyGellispie | select Title, Length, authors, album, genre\n<\/p>\n<p>The command and the output from the command are shown here:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-07.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-07.png\" alt=\"Image of command output\" title=\"Image of command output\" \/><\/a><\/p>\n<p>Cool, so I know what I am interested in doing. Now I am going to use <strong>Get-ChildItem<\/strong> 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:<\/p>\n<p style=\"margin-left:30px\">\n  $fileMeta = Get-FileMetaData -folder (gci e:\\music -Recurse -Directory).FullName\n<\/p>\n<p>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:<\/p>\n<p style=\"margin-left:30px\">\n  $fileMeta | select Title, Length, authors, album, genre |\n<\/p>\n<p style=\"margin-left:30px\">\n  Export-Csv -Path c:\\fso\\musicMeta.csv -Encoding ascii -NoTypeInformation\n<\/p>\n<p>I can now use <strong>Invoke-Item<\/strong> (<strong>ii<\/strong>) to open the file and see the Excel spreadsheet. The <strong>Invoke-Item<\/strong> command is shown here:<\/p>\n<p style=\"margin-left:30px\">\n  ii C:\\fso\\musicMeta.csv\u00a0\u00a0\n<\/p>\n<p>The Microsoft Excel spreadsheet with the songs and the selected metadata looks like this:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-08.png\"><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-2-5-14-08.png\" alt=\"Image of menu\" title=\"Image of menu\" \/><\/a><\/p>\n<p>Remember, I uploaded the function to the Script Center Repository: <a href=\"http:\/\/gallery.technet.microsoft.com\/scriptcenter\/get-file-meta-data-function-f9e8d804\" target=\"_blank\" rel=\"noopener\">get file meta data function<\/a>. You can download it or copy it from that location.<\/p>\n<p>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.<\/p>\n<p>I invite you to follow me on <a href=\"http:\/\/bit.ly\/scriptingguystwitter\" target=\"_blank\" rel=\"noopener\">Twitter<\/a> and <a href=\"http:\/\/bit.ly\/scriptingguysfacebook\" target=\"_blank\" rel=\"noopener\">Facebook<\/a>. If you have any questions, send email to me at <a href=\"mailto:scripter@microsoft.com\" target=\"_blank\" rel=\"noopener\">scripter@microsoft.com<\/a>, or post your questions on the <a href=\"http:\/\/bit.ly\/scriptingforum\" target=\"_blank\" rel=\"noopener\">Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n<p><strong>Ed Wilson, Microsoft Scripting Guy<\/strong>\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Summary: Microsoft Scripting Guy, Ed Wilson, shares a function that gets file metadata, and then he writes the information to a CSV file. \u00a0Hey, 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 [&hellip;]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[38,122,481,123,3,4,12,45],"class_list":["post-2065","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-files","tag-graphics","tag-metadata","tag-multimedia","tag-scripting-guy","tag-scripting-techniques","tag-storage","tag-windows-powershell"],"acf":[],"blog_post_summary":"<p>Summary: Microsoft Scripting Guy, Ed Wilson, shares a function that gets file metadata, and then he writes the information to a CSV file. \u00a0Hey, 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 [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/2065","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=2065"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/2065\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=2065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=2065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=2065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}