![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a> <\/p>\nHey, DW. Before we answer your question we have an observation that we just had<\/i> to pass along. This past Saturday the Scripting Guy who writes this column was driving along making a doughnut run. (And yes, it is<\/i> amazing how much of his life revolves around doughnuts, isn\u2019t it?) As he drove past a Domino\u2019s Pizza he noticed a big banner hanging outside the place:<\/p>\n
Now Serving Domino\u2019s Pizza<\/b><\/p>\n
You read that right: as hard as it might be to believe, there\u2019s at least one Domino\u2019s Pizza place in the Seattle area that now serves Domino\u2019s pizza. What will they think of next?!?<\/p>\n
Although he was tempted to stop and ask about the banner the Scripting Guy who writes this column kept driving. (As the old saying goes, \u201cEat doughnuts first, ask questions later.\u201d) Still, he couldn\u2019t help but wonder what caused the owner of the place to hang up that banner in the first place:<\/p>\n
\u201cYou know, Bob, I was thinking maybe we should start serving Domino\u2019s pizza here.\u201d<\/p>\n
\u201cWhat, are you nuts? You can\u2019t serve Domino\u2019s pizza at a Domino\u2019s Pizza!\u201d<\/p>\n
\u201cI don\u2019t know, Bob. I just really think it would help business if we started serving Domino\u2019s pizza.\u201d<\/p>\n
\u201cWell, maybe. But how are people even going to know we started serving Domino\u2019s pizza? I mean, who\u2019s going to drive by and think, \u2018Oh, there\u2019s a Domino\u2019s Pizza. I bet I could get some Domino\u2019s pizza there.\u2019\u201d<\/p>\n
\u201cHmmm, I hadn\u2019t thought of that \u2026. Wait, I know: we\u2019ll hang up a big banner that says Now Serving Domino\u2019s Pizza<\/b>.\u201d<\/p>\n
\u201cYou know, that\u2019s just crazy enough to work!\u201d<\/p>\n
Before you ask, no, we don\u2019t know what they used to serve before they started serving Domino\u2019s pizza at the Domino\u2019s Pizza place. We\u2019ll have to look into that for you.<\/p>\n
One thing we do<\/i> know, however, is that they didn\u2019t serve up Windows PowerShell commands that can sort the contents of a CSV. (Well, OK, maybe we don\u2019t know that for sure<\/i>. But we\u2019re willing to bet on it.) If you\u2019re looking for information on how to use Windows PowerShell to sort a comma-separated values file, well, there\u2019s only one place to get it.<\/p>\n
No, not Pizza Hut; right here, in the Hey, Scripting Guy!<\/i> column.<\/p>\n
In fact, today we\u2019re going to show you how to sort a CSV file. But not right now; after lunch. All this talk of pizza and doughnuts is making us hungry.<\/p>\n
OK, that\u2019s better. Let\u2019s start out by taking a look at DW\u2019s text file:<\/p>\n
FirstName,LastName,Department,ScoreAlice,Ciccu,Human Resources,222Ken,Myer,Finance,151Pilar,Ackerman,Finance,514Jonathan,Haas,Administration,17Syed,Abbas,Human Resources,67Terri,Chudzik,Finance,188<\/pre>\nAs you can see, this is a relatively simple little CSV (comma-separated values) file, a file containing four fields: FirstName; LastName; Department; and Score. DW would like a script (or command) that can read the file and then sort the data by one of those four fields. Unfortunately, his first crack at the problem didn\u2019t go so well; after issuing the command Get-Content C:\\Scripts\\Test.txt | Sort-Object<\/b> he ended up with output that looked like this:<\/p>\nAlice,Ciccu,Human Resources,222FirstName,LastName,Department,ScoreJonathan,Haas,Administration,17Ken,Myer,Finance,151Pilar,Ackerman,Finance,514Syed,Abbas,Human Resources,67Terri,Chudzik,Finance,188<\/pre>\nInteresting. But not exactly what DW had in mind.<\/p>\n
So what went wrong here? Well, for one thing, the Get-Content cmdlet is typically not<\/i> the cmdlet to use when working with a CSV file. Why not? Because Get-Content doesn\u2019t understand that a CSV file is made up of individual fields; instead, it simply sees each line in the text file as one big, monolithic entity. If you take a look at the output DW got you\u2019ll notice that Get-Content did<\/i> read in the text file and the Sort-Object<\/b> cmdlet did<\/i> sort the contents of that file. The problem is that his command simply sorted the lines (including the header line) by the first character in the line. Definitely<\/i> not what he had in mind.<\/p>\n
And no, asking Sort-Object to sort on a particular field won\u2019t help, either. When we tried the command Get-Content C:\\Scripts\\Test.txt | Sort-Object Department<\/b> we got back this:<\/p>\nJonathan,Haas,Administration,17Syed,Abbas,Human Resources,67Terri,Chudzik,Finance,188Pilar,Ackerman,Finance,514FirstName,LastName,Department,ScoreAlice,Ciccu,Human Resources,222Ken,Myer,Finance,151<\/pre>\nWhat is our data being sorted on here? To tell you the truth, we have no idea. But it\u2019s definitely not sorting our data on the Department field. That\u2019s because Get-Content isn\u2019t passing along any field information to Sort-Object. (Try passing the output to the Get-Member<\/b> cmdlet and you\u2019ll see what we mean.)<\/p>\n
OK, so if Get-Content isn\u2019t the answer then what is<\/i> the answer? As it turns out, any time you\u2019re working with a CSV file you should use the Import-CSV<\/b> cmdlet. For example, take a look at the following line of code, which imports the file C:\\Scripts\\Test.txt:<\/p>\nImport-CSV C:\\Scripts\\Test.txt<\/pre>\nNow look at the output we get when we run that command:<\/p>\n
FirstName LastName Department Score--------- -------- ---------- -----Alice Ciccu Human Resources 222Ken Myer Finance 151Pilar Ackerman Finance 514Jonathan Haas Administration 17Syed Abbas Human Resources 67Terri Chudzik Finance 188<\/pre>\nNotice that the header row is separated from the data rows, and notice that the individual fields have been placed in columns. What does that mean? That means that the Import-CSV cmdlet understands how a comma-separated values file works, and it understands that individual fields should be treated as separate and distinct properties of the text file. To better demonstrate that fact, the following command imports the contents of Test.txt and then pipes that data to the Get-Member cmdlet:<\/p>\n
Import-CSV C:\\Scripts\\Test.txt | Get-Member<\/pre>\nAnd here\u2019s what Get-Member reports back:<\/p>\n
Name MemberType Definition---- ---------- ----------Equals Method System.Boolean Equals(Object obj)GetHashCode Method System.Int32 GetHashCode()GetType Method System.Type GetType()ToString Method System.String ToString()Department NoteProperty System.String Department=Human ResourcesFirstName NoteProperty System.String FirstName=AliceLastName NoteProperty System.String LastName=CiccuScore NoteProperty System.String Score=222<\/pre>\nTake a peek at the last four lines in the output. When you do you\u2019ll see that our four fields \u2013 Department, FirstName, LastName, and Score \u2013 are treated as properties of the text file. (Or, more correctly, as NoteProperties. In PowerShell, a NoteProperty<\/b> is a name-value pair, like FirstName = Alice.) Import-CSV truly understands what a CSV file is all about.<\/p>\n
So does it even matter that Import-CSV treats CVS fields as properties? You bet it does. After all, now we can specify a field name (that is, a property name) when we pipe the contents of the file to Sort-Object:<\/p>\n
Import-CSV C:\\Scripts\\Test.txt | Sort-Object Department<\/pre>\nTake a look at the output that\u2019s<\/i> going to give us:<\/p>\nFirstName LastName Department Score--------- -------- ---------- -----Jonathan Haas Administration 17Terri Chudzik Finance 188Pilar Ackerman Finance 514Ken Myer Finance 151Alice Ciccu Human Resources 222Syed Abbas Human Resources 67<\/pre>\nThat\u2019s<\/i> what DW had in mind all along.<\/p>\n
Does that mean that all our problems are solved? Well, almost. However, there is still one other little matter to take care of. Suppose we issue this command, one designed to sort the CSV file on the Score field:<\/p>\n
Import-CSV C:\\Scripts\\Test.txt | Sort-Object Score<\/pre>\nWhat\u2019s that going to give us? This:<\/p>\n
FirstName LastName Department Score--------- -------- ---------- -----Ken Myer Finance 151Jonathan Haas Administration 17Terri Chudzik Finance 188Alice Ciccu Human Resources 222Pilar Ackerman Finance 514Syed Abbas Human Resources 67<\/pre>\nUh-oh; since when does 151 come before 17? And why does 514 come before 67?<\/p>\n
If you\u2019re thinking, \u201cHmmm, I bet PowerShell is treating those values as strings rather than numbers,\u201d well, you\u2019re absolutely right. In this case PowerShell is<\/i> treating those values as strings. And, in the devil-may-care world of string sorting, anything that starts with 15 (like 151) comes before anything starting with 17. That\u2019s just the way things work.<\/p>\n
So how do we fix this problem? Here\u2019s one way:<\/p>\n
Import-CSV C:\\Scripts\\Test.txt | Sort-Object {[int] $_.Score}<\/pre>\nIn this command we aren\u2019t really sorting by a particular property name; instead, we\u2019re sorting by a value derived from a scriptblock. (Commands enclosed in curly braces are, for all intents and purposes, little scripts embedded in other scripts\/commands.) In PowerShell, the $_<\/b> variable represents the object currently in the pipeline; that means that we can use syntax like $_.Score<\/b> to represent a given property of that object. Of course, we don\u2019t want<\/i> to sort on the Score property; as we just saw, that\u2019s not going to give us the output we expected. Instead, we\u2019re going to use [int]<\/b> to convert the value of the Score property to an integer. In turn, that\u2019s going to cause PowerShell to sort the scores as numbers. And that will give us output like this:<\/p>\nFirstName LastName Department Score--------- -------- ---------- -----Jonathan Haas Administration 17Syed Abbas Human Resources 67Ken Myer Finance 151Terri Chudzik Finance 188Alice Ciccu Human Resources 222Pilar Ackerman Finance 514<\/pre>\nMuch better.<\/p>\n
Incidentally, you can use all the other parameters of Sort-Object when sorting a CSV file. Want to sort the data by score, but in descending order? Then just add the \u2013descending <\/b>parameter, like so:<\/p>\nImport-CSV C:\\Scripts\\Test.txt | Sort-Object {[int] $_.Score} -descending<\/pre>\nAlternatively, maybe you\u2019d like to sort by Department and then, within each department, sort by LastName. That\u2019s fine:<\/p>\n
Import-CSV C:\\Scripts\\Test.txt | Sort-Object Department, LastName<\/pre>\nEtc.<\/p>\n
That should do it, DW; you should now be able to sort your CSV file any way you want. That\u2019s about all the time we have for today, although we should take a moment to remind everyone that the 2008 Winter Scripting Games<\/b><\/a> start this Friday, February 15th<\/sup>. As we\u2019ve said before, we expect this year\u2019s Scripting Games to be bigger and better than ever. Why? Well, for one thing, taking a cue from the good folks at Domino\u2019s Pizza, we\u2019ve decided that this year the Winter Scripting Games will feature Winter Scripting Games events! That\u2019s right: this year\u2019s Winter Scripting Games will be chock-full of Winter Scripting Games events.<\/p>\nSure, it\u2019s a bit of a gamble, but we like to believe that putting Winter Scripting Games events in the Winter Scripting Games is just crazy enough to work. (And we don\u2019t think it\u2019s any crazier than serving pizza at a pizza place.) Did the Scripting Guys finally go too far? Looks like we\u2019ll all find out on Friday, won\u2019t we? See you then.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! have a comma-separated values file that includes several different fields. I\u2019d like to be able to read this file from Windows PowerShell and then sort the information on one (or maybe more) of these fields. However, I can\u2019t seem to get this to work. Any suggestions? — DW Hey, DW. Before we […]<\/p>\n","protected":false},"author":595,"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-56223","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":"
Hey, Scripting Guy! have a comma-separated values file that includes several different fields. I\u2019d like to be able to read this file from Windows PowerShell and then sort the information on one (or maybe more) of these fields. However, I can\u2019t seem to get this to work. Any suggestions? — DW Hey, DW. Before we […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56223","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\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=56223"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56223\/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=56223"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56223"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56223"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")