I have to admit, I’m kind of excited to be doing another Twitter Reply post. This one is the result of a post by Michael Bender. I’ve never met him, but I’ve follow his career a bit in the last few years, when I was introduced to his name via TechEd. He made a recent Tweet where he asked for a PowerShell genius to help build him a script. The goal was to randomly select a name from a CSV file, with an option to choose how many names it should randomly select.
Here’s the Tweet: https://twitter.com/MichaelBender/status/593168496571322370
I wouldn’t consider myself a genius, but if you consider what he’s after — a name from a CSV file (not a line from a text file) — then there’s a better option then the one he accepted: “get-content file.csv | get-random.” This will work, but there’s some reasons why this isn’t such a great idea. We can use the Get-Content cmdlet with CSV files, but we generally don’t. CSV files are created and formatted in such a way, that using Get-Content, instead of Import-Csv, isn’t the best way to do things.
Let’s consider that we’re using the CSV file in the image below. It has three headers, or column names: Name, Age, and HairColor.
First of all, if we use Get-Content, it’s going to consider the first line — the header line, or the column headings — a selectable line within the file. Here’s that example:
PS C:\> Get-Content .\file.csv | Get-Random
Name,Age,HairColor
Uh, not helpful. While this isn’t the end of the world, as we can simply rerun the command (and probably get something different), we’d be better off to write this so that this error is not a possibility — something we’ll see in a moment, when we use Import-Csv.
Second of all, unless the only column in the CSV file is Name, then we’re not returning just a name, but instead, everything in a row of the file. This means that if I randomly choose a line in the file, that it will include information that Michael didn’t request — the age and hair color.
PS C:\> Get-Content .\file.csv | Get-Random
Jeff,19,Brown
I suppose we could start parsing our returned value to just return the name ((Get-Content .\file.csv | Get-Random).Split(‘,’)[0]), but seriously, let’s skip that and use one of the cmdlets that was built to work with CSVs directly. We’ll assume we’re still using the same CSV file in the image above.
PS C:\> Import-Csv .\file.csv | Get-Random | Select-Object Name
Name
----
Lance
Um, that was easy. If we wanted to increase the number of names returned from our CSV file, then we would use Get-Random’s -Count parameter and an integer value, such as in this example:
PS C:\> Import-Csv .\file.csv | Get-Random -Count 3 | Select-Object Name
Name
----
Bob
Stephen
Sue
I think we’re best off when we use the *-Csv cmdlets with CSV files, and the Get-Content cmdlet with most other file types we can read in PowerShell. There is at least one exception of doing this in reverse: Using Import-Csv with a text file, that has a delimiter, might help you better parse the information in your file. Consider this text file:
The next few examples will progressively show you how to use Import-Csv with a delimited text file until all of the “columns” are broken apart. When we’re finished, we can even export our data into a properly formatted CSV file, and then import it, piping those results to other cmdlets — take a look.
PS C:\> Import-Csv .\file.txt
001|Sunday|Car7
---------------
002|Monday|Car2
003|Tuesday|Car3
004|Wednesday|Car3
005|Thursday|Car7
006|Friday|Car2
007|Saturday|Car3
PS C:\> Import-Csv .\file.txt -Header Id,Day,Car
Id Day Car
-- --- ---
001|Sunday|Car7
002|Monday|Car2
003|Tuesday|Car3
004|Wednesday|Car3
005|Thursday|Car7
006|Friday|Car2
007|Saturday|Car3
PS C:\> Import-Csv .\file.txt -Header Id,Day,Car -Delimiter '|'
Id Day Car
-- --- ---
001 Sunday Car7
002 Monday Car2
003 Tuesday Car3
004 Wednesday Car3
005 Thursday Car7
006 Friday Car2
007 Saturday Car3
PS C:\> Import-Csv .\file.txt -Header Id,Day,Car -Delimiter '|' | Export-Csv -Path C:\file-cars.csv -NoTypeInformation
PS C:\> Import-Csv .\file-cars.csv
Id Day Car
-- --- ---
001 Sunday Car7
002 Monday Car2
003 Tuesday Car3
004 Wednesday Car3
005 Thursday Car7
006 Friday Car2
007 Saturday Car3
PS C:\> Import-Csv .\file-cars.csv | Where-Object Car -eq 'Car3'
Id Day Car
-- --- ---
003 Tuesday Car3
004 Wednesday Car3
007 Saturday Car3
PS C:\> Import-Csv .\file-cars.csv | Where-Object Car -eq 'Car3' | Select-Object Day
Day
---
Tuesday
Wednesday
Saturday
So, if I had seen the Tweet first, I would have to recommend Import-Csv, piped to Get-Random, and then piped to Select-Object Name. Thanks for reading the post.
