{"id":3257,"date":"2013-07-11T00:01:00","date_gmt":"2013-07-11T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2013\/07\/11\/use-powershell-to-sort-csv-files-in-order\/"},"modified":"2013-07-11T00:01:00","modified_gmt":"2013-07-11T00:01:00","slug":"use-powershell-to-sort-csv-files-in-order","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-sort-csv-files-in-order\/","title":{"rendered":"Use PowerShell to Sort CSV Files in Order"},"content":{"rendered":"

Summary<\/strong>: Microsoft Scripting Guy, Ed Wilson, talks about sorting comma-separated value (CSV) files.<\/span><\/p>\n

Microsoft Scripting Guy, Ed Wilson, is here. This morning I am sifting through some of the email that arrives at scripter@microsoft.com<\/a>. It is always fun to read the email because it gives me a great sense of what is going on with people trying to use Windows PowerShell. For some reason, today there are several emails about dealing with CSV files—in particular, sorting them. It seems that people have tried all kinds of ways to sort CSVs. In reality, Windows PowerShell makes this really easy.<\/p>\n

One of the revolutionary features of Windows PowerShell is the object-oriented nature of the language. Windows PowerShell passes objects, not strings. This makes it easy to access stuff because the data is a property of an object. Comma-separated value (CSV) files are not the exception.<\/p>\n

Suppose I have a CSV file that contains two columns. The first is a user name column and the second is a password column. The CSV file (named testusers.csv) is shown here:<\/p>\n

\"Image<\/a><\/p>\n

To read this CSV file in Windows PowerShell, all I need to do is to use the Import-CSV<\/strong> cmdlet as shown here:<\/p>\n

Import-Csv C:\\fso\\testusers.csv<\/p>\n

The command and output from the command are shown in the image that follows:<\/p>\n

\"Image<\/a><\/p>\n

Sorting the data<\/h2>\n

Suppose for some reason the data in the TestUsers.csv file needs to be sorted by user name in descending order. To do this, I use the Sort-Object<\/strong> cmdlet, and I specify that I want to sort on the UserName<\/strong> property, and I want the sort to be descending. The Sort-Object<\/strong> cmdlet accepts pipelined output; and therefore, I can pipe the Import-CSV<\/strong> cmdlet results directly to Sort-Object<\/strong>. This command is shown here:<\/p>\n

Import-Csv C:\\fso\\testusers.csv | sort username -Descending<\/p>\n

The command and the associated output are shown in the following image:<\/p>\n

\"Image<\/a><\/p>\n

Updating the original file<\/h2>\n

Often the requirement is to update the original file. There are two ways to do this. One is to first make a backup copy of the original file, and then overwrite the original file. The other way is to simply overwrite the original file. I never like to destroy stuff, so I will first rename the original file by using a .old file extension as shown here:<\/p>\n

Rename-Item C:\\fso\\testusers.csv c:\\fso\\testusers.csv.old<\/p>\n

Now, I will read the renamed file, sort the data, and export it back to a CSV file. The command is shown here:<\/p>\n

Import-Csv C:\\fso\\testusers.csv.old | sort username -Descending | Export-Csv -Path c:\\fso\\testusers.csv -NoTypeInformation<\/p>\n

The newly sorted CSV file is shown in the following image:<\/p>\n

\"Image<\/a><\/p>\n

That is all there is to using Windows PowerShell to sort CSV files. Join me tomorrow when I have a guest blog written by Chris Campbell. He talks about using the Windows API to access password files.<\/p>\n

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

Ed Wilson, Microsoft Scripting Guy<\/strong> <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Microsoft Scripting Guy, Ed Wilson, talks about sorting comma-separated value (CSV) files. Microsoft Scripting Guy, Ed Wilson, is here. This morning I am sifting through some of the email that arrives at scripter@microsoft.com. It is always fun to read the email because it gives me a great sense of what is going on with […]<\/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":[169,3,4,45],"class_list":["post-3257","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-windows-powershell"],"acf":[],"blog_post_summary":"

Summary: Microsoft Scripting Guy, Ed Wilson, talks about sorting comma-separated value (CSV) files. Microsoft Scripting Guy, Ed Wilson, is here. This morning I am sifting through some of the email that arrives at scripter@microsoft.com. It is always fun to read the email because it gives me a great sense of what is going on with […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/3257","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=3257"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/3257\/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=3257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=3257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=3257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}