Use PowerShell to Sort CSV Files in Order

Doctor Scripto

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 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.

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.

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:

Image of command output

To read this CSV file in Windows PowerShell, all I need to do is to use the Import-CSV cmdlet as shown here:

Import-Csv C:\fso\testusers.csv

The command and output from the command are shown in the image that follows:

Image of command output

Sorting the data

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 cmdlet, and I specify that I want to sort on the UserName property, and I want the sort to be descending. The Sort-Object cmdlet accepts pipelined output; and therefore, I can pipe the Import-CSV cmdlet results directly to Sort-Object. This command is shown here:

Import-Csv C:\fso\testusers.csv | sort username -Descending

The command and the associated output are shown in the following image:

Image of command output

Updating the original file

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:

Rename-Item C:\fso\testusers.csv c:\fso\testusers.csv.old

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

Import-Csv C:\fso\testusers.csv.old | sort username -Descending | Export-Csv -Path c:\fso\testusers.csv -NoTypeInformation

The newly sorted CSV file is shown in the following image:

Image of command output

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.

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 

0 comments

Discussion is closed.

Feedback usabilla icon