Tag Archives: Import-Csv

Use PowerShell to Edit a CSV

I recently completed a scripting assignment for work. Yeah… it’s a part of what I do. During the process I learned something new, that I hadn’t known before. And like it normally does, this leaves me in a position to share it with the Internet, as well as help solidify it in my own mind. And that’s, why I’m writing today.

I thought my recent assignment had a need to edit an existing CSV file on the fly. It turns out it wasn’t necessary for the project, but just maybe it will be for another one, or maybe even one that’s sitting in front of you right now. So, how do you do that? How do you edit an existing CSV file?

Let’s begin with a simple, yet worthy CSV file as an example. In the below CSV data, we have four columns, fields, or properties — whatever you want to call them at this point. They are Name, Status, BatchName, and SentNotification. The idea here — or what I thought it was in my recent assignment, at first — was to notify by email the users that were in a Synced state and then modify the SentNotification field so that it said True, instead of FALSE. Take a look at our example data.

Name,Status,BatchName,SentNotification
landrews,Other,MigrationService:FirstBatch-to-O365,FALSE
lpope,Synced,MigrationService:FirstBatch-to-O365,FALSE
ljohnson,Other,MigrationService:FirstBatch-to-O365,FALSE
mlindsay,Other,MigrationService:FirstBatch-to-O365,FALSE
rperkins,Synced,MigrationService:FirstBatch-to-O365,FALSE
dstevenson,Other,MigrationService:FirstBatch-to-O365,FALSE
jbradford,Other,MigrationService:FirstBatch-to-O365,FALSE
jsmith,Other,MigrationService:FirstBatch-to-O365,FALSE
mdavidson,Synced,MigrationService:FirstBatch-to-O365,FALSE
bclark,Synced,MigrationService:FirstBatch-to-O365,FALSE

Let’s first begin by using Import-Csv and Format-Table to view our data.

Import-Csv -Path '.\Desktop\UCSVTestFile.csv' | Format-Table -AutoSize

Name       Status BatchName                           SentNotification
----       ------ ---------                           ----------------
landrews   Other  MigrationService:FirstBatch-to-O365 FALSE           
lpope      Synced MigrationService:FirstBatch-to-O365 FALSE           
ljohnson   Other  MigrationService:FirstBatch-to-O365 FALSE           
mlindsay   Other  MigrationService:FirstBatch-to-O365 FALSE           
rperkins   Synced MigrationService:FirstBatch-to-O365 FALSE           
dstevenson Other  MigrationService:FirstBatch-to-O365 FALSE           
jbradford  Other  MigrationService:FirstBatch-to-O365 FALSE           
jsmith     Other  MigrationService:FirstBatch-to-O365 FALSE           
mdavidson  Synced MigrationService:FirstBatch-to-O365 FALSE           
bclark     Synced MigrationService:FirstBatch-to-O365 FALSE

Now what we need to do, is modify the content, as it’s being imported. Let’s start first, however, by piping our Import-Csv cmdlet to ForEach-Object and returning each object (line).

Import-Csv -Path '.\Desktop\UCSVTestFile.csv' | ForEach-Object {
    $_
}

Name       Status BatchName                           SentNotification
----       ------ ---------                           ----------------
landrews   Other  MigrationService:FirstBatch-to-O365 FALSE           
lpope      Synced MigrationService:FirstBatch-to-O365 FALSE           
ljohnson   Other  MigrationService:FirstBatch-to-O365 FALSE           
mlindsay   Other  MigrationService:FirstBatch-to-O365 FALSE           
rperkins   Synced MigrationService:FirstBatch-to-O365 FALSE           
dstevenson Other  MigrationService:FirstBatch-to-O365 FALSE           
jbradford  Other  MigrationService:FirstBatch-to-O365 FALSE           
jsmith     Other  MigrationService:FirstBatch-to-O365 FALSE           
mdavidson  Synced MigrationService:FirstBatch-to-O365 FALSE           
bclark     Synced MigrationService:FirstBatch-to-O365 FALSE

Hey, look at that. It’s the same thing. The $_ variable represents the current object, or the current row, if it helps to think about it that way — in the pipeline. Let’s add an If statement inside our ForEach-Object loop, and get the results we’re after. Remember, if a user has a Synced status, we want to change their SentNotification property to $true, and perhaps notify them, had this been more than just an example.

Import-Csv -Path '.\Desktop\UCSVTestFile.csv' | ForEach-Object {
    If ($_.Status -eq 'Synced' -and $_.SentNotification -eq $false) {
        $_.SentNotification = $true
    }
    $_
} | Format-Table -AutoSize

Name       Status BatchName                           SentNotification
----       ------ ---------                           ----------------
landrews   Other  MigrationService:FirstBatch-to-O365 FALSE           
lpope      Synced MigrationService:FirstBatch-to-O365 True            
ljohnson   Other  MigrationService:FirstBatch-to-O365 FALSE           
mlindsay   Other  MigrationService:FirstBatch-to-O365 FALSE           
rperkins   Synced MigrationService:FirstBatch-to-O365 True            
dstevenson Other  MigrationService:FirstBatch-to-O365 FALSE           
jbradford  Other  MigrationService:FirstBatch-to-O365 FALSE           
jsmith     Other  MigrationService:FirstBatch-to-O365 FALSE           
mdavidson  Synced MigrationService:FirstBatch-to-O365 True            
bclark     Synced MigrationService:FirstBatch-to-O365 True

In the above example, we use an If statement to check the values of two properties. If Status is Synced and SentNotification is $false, we’ll change SentNotification to $true. You can see that this worked. But what now? You see, the file from which we did our import is still the same. In order to update that file, we have a bit more work to do.

I wish I could say pipe directly back to the file; however, that doesn’t work. The file ends up being blank. It makes sense is doesn’t work though, as we’re literally reading each object — each row — and then trying to write back to the file in the same pipeline. Something is bound to go wrong, and it does. So, don’t do what’s in the below example, unless your goal is to fail at this assignment and wipe out your data. If that’s what you’re after, then by all means, have at it.

Import-Csv -Path '.\Desktop\UCSVTestFile.csv' | ForEach-Object {
    If ($_.Status -eq 'Synced' -and $_.SentNotification -eq $false) {
        $_.SentNotification = $true
    }
    $_
} | Export-Csv -Path '.\Desktop\UCSVTestFile.csv' -NoTypeInformation

What we need to do instead, is Export to a file with a different name, so that when we’re done, both files exist at the same time. Then, we remove the original file and rename the new one with the old one’s name. Here’s the entire example; take a look. And then after that, enjoy the weekend. Oh wait, tomorrow is only Friday. I keep thinking it’s the weekend, because I’m home tomorrow to deal with 1,000 square feet of sod. If only PowerShell could lay the sod for me.

Import-Csv -Path '.\Desktop\UCSVTestFile.csv' | ForEach-Object {
    If ($_.Status -eq 'Synced' -and $_.SentNotification -eq $false) {
        $_.SentNotification = $true
    }
    $_
} | Export-Csv -Path '.\Desktop\UCSVTestFile-temp.csv' -NoTypeInformation
Remove-Item -Path '.\Desktop\UCSVTestFile.csv'
Rename-Item -Path '.\Desktop\UCSVTestFile-temp.csv' -NewName 'UCSVTestFile.csv'

Twitter Reply – Randomly Selecting a Name from a CSV File

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.

Randomly Selecting Name from a CSV File01

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:

Randomly Selecting Name from a CSV File02

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.