Tag Archives: Export-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'

Quick Learn – Remove and Add Users to an Active Directory Group

The more people that recognize that I’ve made an investment in Windows PowerShell, the more these posts write themselves. I was recently asked by a previous colleague to help them make some modifications in Active Directory (AD). They needed to strip out all the users from a single AD group, and then add a bunch of them back that were, conveniently, stored in a text file. While you can compare the current users in the group with the ones you want to add, this post will assume it’s suitable to momentarily remove all the users from a specific AD group.

When I completed this, out of the kindness of my heart, and because I want to promote and teach PowerShell whenever possible, I sent the previous colleague an email with the five steps I had completed. They might be useful for others as well, so here we are. I think I’m getting a lunch out of this, too. While it might not be Chipotle, I like food, so I’m sure it’ll be suitable.

1. Export Users in the AD Group: This command will return the current members of the VPN-Users group and store them in a Csv file. While this will allow us to compare our final results, its original purpose was as my safety net in case I made a mistake and wanted to make the previous members, members again.

PS> $GroupName = 'VPN-Users'
PS> Get-ADGroupMember -Identity $GroupName | Export-Csv -Path C:\VPN-Users01Pre.csv -NoTypeInformation

2. Remove Users from Group: This next command removes all the members of the group (which I had checked were only users). It did this by piping each member of the group to a ForEach-Object loop that ran Remove-ADGroupMember against the current user. Had there been other object types, you’d have to use a Where-Object command in between these two commands (command 1 | Where-Object… | command 2), filtering on the ObjectClass. The Remove-ADGroupMember cmdlet is using the -Confirm parameter with the $false value, so there’s no need for manual confirmation of each removal.

PS> Get-ADGroupMember -Identity $GroupName | Foreach-Object {Remove-ADGroupMember -Identity $GroupName -Members $_ -Confirm:$false}

3. Add Users to Group: After I allowed a few moments for replication, I went ahead and added all the users in the text file I had been supplied by using the Get-Content command and piping each entry to the ForEach-Object and Add-ADGroupMember cmdlets. It threw two errors when it wasn’t able to find a couple users in AD. While I didn’t, we could’ve written in precautions in this command to avoid this error, or programmatically fixed the file prior to this command.

PS> Get-Content -Path C:\UserNames.txt | Foreach-Object {Add-ADGroupMember -Identity $GroupName  -Members $_}

4. Export Users in the AD Group: Now that we have all the old group members removed, and the new ones added, we can create a new export of the group members for comparison. This isn’t necessary, but helps to see who was removed and added, if we desire that kind of information and comparison.

PS> Get-ADGroupMember -Identity $GroupName | Export-Csv -Path C:\VPN-Users02Post.csv -NoTypeInformation

5Compare Before and After Group Memberships: The next command will reach into the SamAccountName column inside both Csv files and compare them. Results that indicate <= mean they are in the file on the left (the file as the value to -ReferenceObject parameter), and results that indicate => mean they are only in the file on the right (-DifferenceObject parameter). Don’t forget, you can always use the -IncludeEqual parameter if you want to see which users where there before and after.

Compare-Object -ReferenceObject ((Import-Csv -Path C:\VPN-Users01Pre.csv).SamAccountName) -DifferenceObject ((Import-Csv -Path C:\VPN-Users02Post.csv).SamAccountName)

There ya go. The previous colleague said they had a problem finding some good examples of doing this online, so here’s to hoping that this will help someone when it they need it. Just maybe, I’ll be back with the right way to do this, where the users that are going to be added back, never get removed in the fist place.

Quick Learn – Proving PowerShell’s Usefulness to Newbies, Part III

Here we go! It’s the third time doing what we can, to try and prove the usefulness and power in Windows PowerShell. With a little work, those new in PowerShell, get a glimpse into not only what they can do with PowerShell, but also why they’d want to do it with PowerShell. In the end, most of this is about time savings, and tonight’s post is no exception.

Part I can be found here. In that post, we learned that we could use PowerShell to create 10,000 folders in about 10 seconds — that’s 1,000 folders per second, or 1 folder each millisecond.

Part II is here. This post was about reading in a file’s contents, sorting the contents alphabetically, and then writing the sorted results back to the same file. PowerShell took milliseconds, while my manual attempt took minutes.

Part III
As part of my imaginary Windows PowerShell presentation I thought I might give one day, I always pictured a common theme — saving time. That’s a big part of why many of us have taken so fondly to PowerShell. I pictured telling a story about a few different approaches to the same problem, all to be completed by a different admin, each with their own best way of handling the request. Here’s my hypothetical, yet quite possible situation, beginning with the characters.

Admin One: This admin is fairly new to the industry. While they know their way around a desktop version of Windows, they’re still often learning new things about it. While they’re motivated, they haven’t learned enough to often know better.

Admin Two: This admin has a year or two on admin one, and is quickly picking up better ways to speed things up by utilizing certain tools.

Admin Three: This admin is getting there. It’s almost daily that they’re figuring out faster, and more remote ways to complete tasks. While they aren’t scripting and running commands (yet), they do know some tricks to get their results quicker.

Admin Four: This admin has been studying all things PowerShell whenever there’s a moment for it. Every task assigned is another challenge to learn more PowerShell, and to continue to add to their skills. They’re freeing up time, all the time.

One day the boss comes in with a request of the team. It’s not a difficult task. In fact, any one of the team members can get the task done. They need to check 200 of their computers to determine, and then document, which of the computers are running the print spooler service. Maybe it’s just busy work handed down by the boss, or maybe this is a valid concern somehow.

Admin One gets his clipboard and heads down to one of the computer labs. Once the three labs are checked, he’ll head to each of the offices to check those computers manually, too. Here’s the steps this admin takes: (1) Log on to the computer, (2) Open Computer Management, (3) Click on Services, (4) Find the Print Spooler service, noting the status, and (5) Write down the computer name and service’s status.

Let’s assume Admin One takes 5 minutes per computer which includes the time spent walking from computer to computer and office to office. At 200 computers, that’s 1,000 minutes, or approximately 16.6 hours. That’s longer than two full 8 hour days at the office. That kind of time loss equates to zero time spent learning anything new for two days in the week. You can’t stay in the field using this procedure.

Admin Two’s first though is Remote Desktop. While this admin still has to log on, it can be done without physically visiting each computer. In addition, this admin can work with more than one remote computer at a time. This allows Admin Two the ability to complete one computer every 2 1/2 minutes, cutting the time it takes Admin One in half. That’s only a little over one work day — 8.3 hours — lost to this project. While it’s shorter, it’s still too much time.

Admin Three knows enough to know that they can connect to a remote computer from inside Computer Management, which means they can also avoid leaving their desk. This removes the step of interactively logging on to a computer, and loading a desktop environment, too. We’ll assume this admin can do one computer every minute. That’s 200 total minutes, or 3.3 hours. That admin can actually finish before lunch if they start soon enough in the day. This might be reasonable by some boss’ standards, but PowerShell has been designed to do this quicker.

The final admin, Admin Four, knows PowerShell, and they know it well enough to pull back all those results without the need for any GUIs. There’s no wandering around the building, writing things down to a clipboard, or even entering data into a file, as Admin Two and Three do from their desks, as they use RDP and Computer Management, respectively.

Admin Four has a couple approaches and plenty of time to try them both out. While there’s many ways to read in computer names, we’ll assume they have a list of the 200 computers in a text file with each computer name on its own line. They may have had to build this file, but if they’re pulling from Active Directory, then it was probably automated, as well.

The first option is to use the Get-Service cmdlet, as seen below, in the first part of the example. The second part, which requires PowerShell Remoting, uses Invoke-Command. I’ve only include 20 computers in the computers.txt file, and to save space, only included 5 results per command in the example.

PS> # Uses the Get-Service cmdlet with -ComputerName parameter.
PS> # Have to add the MachineName property to see which results goes with which computer.
PS> Get-Service -ComputerName (Get-Content -Path .\computers.txt) -Name spooler | Select-Object MachineName,Status,Name,DisplayName

MachineName                                          Status Name                          DisplayName
-----------                                          ------ ----                          -----------
DC01                                                 Running spooler                       Print Spooler
DC02                                                 Running spooler                       Print Spooler
DC03                                                 Running spooler                       Print Spooler
WEB01                                                Running spooler                       Print Spooler
WEB02                                                Running spooler                       Print Spooler
...

PS> # Uses Invoke-Command which automatically adds the name of the remote computer by default.
PS> Invoke-Command -ComputerName (Get-Content -Path .\computers.txt) -ScriptBlock {Get-Service -Name spooler}

Status   Name               DisplayName                            PSComputerName
------   ----               -----------                            --------------
Running  spooler            Print Spooler                          SQL01
Running  spooler            Print Spooler                          DC02
Running  spooler            Print Spooler                          DC02
Running  spooler            Print Spooler                          WEB02
Running  spooler            Print Spooler                          DC03
...

While I didn’t include the results, each of these two options — Get-Service, and Get-Service inside Invoke-Command — took about the same amount of time when they were measured. Even so, we’ll round up and say it takes 10 second for each set of 20 computers. At 200 computers, that 100 seconds for all of them. At 100 seconds, we’re talking about an approximation of 1 1/2 minutes to check all two hundred computers.

If I was in front of a crowd right now, and had just talked thought this post, I’d take a moment to ask the crowd which admin they thought would be around after budgets cuts. The admin that showed up with the results written down, after two days, or the one who sent the boss the results via email after two minutes? Keep in mind, that either of these cmdlets above can be used in conjunction with Export-Csv — a cmdlet that produces a text-based file that can be opened and edited with Microsoft Excel — something in which the boss probably has on their computer. In addition to automatically writing the results to file, by using the Send-MailMessage cmdlet, Admin Four can also automate sending the results by email.

Script Sharing – Find DNS Servers Being Used by DHCP Scopes

Download the Get-TMDhcpDNS function here: https://gallery.technet.microsoft.com/Find-DNS-Servers-Being-640978d1

There was a Microsoft TechNet forum post last week regarding trying to obtain the DNS servers being used by different DHCP Scopes (Here’s the link: https://social.technet.microsoft.com/Forums/en-US/58723d31-7586-40c3-acd2-183f20b49daf/how-to-dump-the-dns-options-for-each-dhcp-scope?forum=ITCG).

I originally thought the person wanted to get the DNS Servers listed in the Server Options (what the scopes use by default), until he (or she) better clued me in to wanting the DNS Servers listed in the Scope Options, when there was one. At times there won’t be any DNS Servers listed in the Scope Options, and it’ll use the Server Options instead.

Since that forum post, I wrapped up a few commands to create a full-service advanced function. Get-TMDhcpDNS function will collect the Scope Name, Scope ID, DNS Servers, and whether the DNS Servers assigned, are done so in the Scope Options or Server Options.

If you think this might be helpful for you or someone else, then please download it, test it, and rate it. Thanks, and here’s an example of the function in action:

PS C:\> Get-TMDhcpDNS -ComputerName 'dhcpsrv1.mydomain.com' | Format-Table -AutoSize

Name                  ScopeName  ScopeID     DNS                                    ScopeOrServerDNS
----                  ---------  -------     ---                                    ----------------
dhcpsrv1.mydomain.com Building01 10.10.10.0  10.10.10.10.20,10.10.10.21,10.10.10.22 Scope
dhcpsrv1.mydomain.com Building02 172.16.16.0 172.16.16.5,172.16.16.15               Server

Use the link above to download the function.

Update: There was an issue with the DNS Server (System.Object[]) when piping the function to Export-Csv. That’s been corrected in 1.0.2. Here’s a post I had to reference (again): http://learn-powershell.net/2014/01/24/avoiding-system-object-or-similar-output-when-using-export-csv/.

Quick Learn – Get Some Help for Later Reading

I’m just starting to get my hands wet with Microsoft Lync. As I often do, I use the Windows PowerShell cmdlets to help learn more about a product; I did this same thing with Hyper-V. The unfortunate thing about the GUI (think, an MMC snap-in for instance), is that the menu options don’t always tell you exactly what that option is going to do, or its exact purpose. While the GUI can be unclear, PowerShell tells you exactly what a cmdlet does. With that knowledge, I’ve often been able to relate a cmdlet, and it’s purpose, to its respective menu option in the GUI.

I wanted to read though the Lync cmdlet’s help files, but only the cmdlet name and its synopsis. Here’s the command I ran to extract this information for later reading.

PS C:> Get-Command -Module Lync | Get-Help | Select-Object Name,Synopsis | Export-Csv -Path C:\LyncCmdlets.csv -NoTypeInfomation

The command above works this way: it returns all the cmdlets included in the Lync Module and sends (pipes) those to the Get-Help cmdlet. The Get-Help cmdlet pipes it’s results to Select-Object which filters the returned properties to just the Name and Synopsis from each cmdlets’ help file. At the end, those filtered results are sent to Export-Csv which creates a file I can read at my leisure.

Although there’s over 500 cmdlets, its safe to say that many of the nouns (the part after the dash (-)) will be the same across some of the cmdlets. That means that many of the Get-* cmdlets will have a partnering Set-* cmdlet. Get reads information and Set changes it. These nouns may also have a matching New-*, Remove-*, and possibly even a Test-* cmdlet. Now off to do some reading…