How to create basic PowerShell scripts with Import-CSV

How to create basic PowerShell scripts with Import-CSV

In the previous tutorial -
4. How to create basic PowerShell scripts with Export-CSV - Link
I showed how to create a basic PowerShell script with the export to csv functionality.

In this tutorial, I will show you how to create a basic PowerShell script that uses the import-csv functionality. For continuity, I will start by using the csv file that was created in the previous tutorial.
The name and location of the file is - c:\scripts\All-Mailboxes.csv

Preparation for Exchange 2013 and PowerShell ISE -
Note - As I am creating and running these tutorial scripts in Exchange 2013 on-premise, I will be using PowerShell ISE on my Exchange 2013 server. I have the Exchange tools installed and I will run the PowerShell snapin using this as a reference - Link
To enable Exchange PowerShell cmdlets within PowerShell ISE or standard powershell, you will need to add the Exchange 2013 snapins.
Run the cmdlet -
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn;

I will now be able to access the Exchange 2013 cmdlets used for the scripts, while using PowerShell ISE.

As a quick recap, in the previous tutorial, I created a script to export all mailboxes with the most common attributes. This created a csv file - c:\scripts\All-Mailboxes.csv
Get-Mailbox | Select Name,DisplayName,Alias,PrimarySMTPAddress,UserPrincipalName | export-csv -NoTypeInformation c:\scripts\All-Mailboxes.csv

I will run this script again to ensure it works in PowerShell ISE. As I have previously added the PSSnapin for Exchange, I can access the Exchange cmdlets.
I have run the script successfully and created the csv - All-Mailboxes.csv

So lets look at a few scenarios and see how I can edit the existing csv file with all the mailboxes to get some more reports.

--------------------------------------------------------------------------------------------

Scenario 1 - This script will have three objectives.
- Target only four specific mailboxes - user01, user02, user03, and user04.
- Select the Name, display name , mailbox database and Organizational Unit of those four mailboxes
- Export that data to a new spreadsheet named - FourUsersWithDatabase.csv

Using the knowledge from the previous tutorials, it is very easy to create a script to achieve this.

Scenario 1 - Step 1 - Edit the csv for importing the four users.
Open the original csv - All-Mailboxes.csv and save a new copy as - c:\scripts\FourUsers.csv
*** DO NOT CHANGE THE EXISTING COLUMN NAMES ***
Tip -
To speed up your future scripting, never change the column names. When creating scripts and entering the attributes to be added when using - Select - , Ensure you use the column names from the script.
For example. If your script uses the shortened version of UserPrincipalName - UPN. Your script will fail if you do not change your column name to UPN. For consistency, always keep the column names the same as the original attribute ( UserPrincipalName - PrimarySMTPAddress, DisplayName). And make sure you use the original name in your scripts.

Edit the new CSV -
Open the new csv - c:\scripts\FourUsers.csv
Highlight and delete all the mailboxes except user01, user02, user03 and user04.
Save the csv file.

Scenario 1 - Step 2 - Creating a script with the import-csv
We already have a working script tested previously, so let's just edit this.
Previous script
Get-Mailbox | Select Name,DisplayName,Alias,PrimarySMTPAddress,UserPrincipalName | export-csv -NoTypeInformation c:\scripts\All-Mailboxes.csv

Edit the script by removing the Get-mailbox and ensure you keep the pipe symbol - |
 |
Select Name,DisplayName,Alias,PrimarySMTPAddress,UserPrincipalName |
export-csv -NoTypeInformation c:\scripts\All-Mailboxes.csv

To make the script easier to read, I usually put each | (pipe symbol) at the end of each step of the script.

Next I add the text on line 1 -
Import-CSV "c:\scripts\FourUsers.csv"
just before the pipe symbol

Next I need to add a foreach loop -
After the | (pipe symbol) on line 1, press enter to get a new line
Type the following text - (note the | (pipe symbol) at the end of the line
foreach {Get-Mailbox -id $_.UserPrincipalName} |

The for-each command tells powershell to run the get-mailbox cmdlet for each of the mailboxes in the spreadsheet. Note that the cmdlet get-mailbox uses the identity switch (-id). The script also points the -id (identity switch) to use the Column - UserPrincipalName. This is why it is important to make sure that the script (which uses UserPrincipalName) points to the column of the same name (UserPrincipalName).

The script is now nearly complete. If you run it now, it will successfully import the new csv file - FourUsers.csv and then run a get-mailbox for each of the mailboxes in the spreadsheet. It will then select the attributes requested and export to All-Mailboxes.csv.

Next we need to change the export-csv file name -
Edit Line 4 - change the file name from All-Mailboxes.csv to FourUsersWithDatabase.csv

Finally, we need to change the attributes selected-
The attributes we want are -  Name, display name , mailbox database and Organizational Unit
As we don't know exactly what the attribute names are, we can run a quick get-mailbox in another PowerShell tab to see what the correct name is. This name will then be added to the selected attributes.

PowerShell ISE - File - New - A new PowerShell tab will appear -
Run get-mailbox | fl against one mailbox to see all the attributes.
PowerShell cmdlet -
get-mailbox VIP01 | fl

Scroll down and look for attributes like - Database and Organizational Unit
Note that I am only looking on how to spell Database and Organizational Unit. I do not need the results like the database name or Organizational Unit name.

Database - Note the spelling and no spaces

OrganizationalUnit - Note the spelling (US English) and no spaces

Now that I know the spelling (and no spaces), I can edit the script accordingly.
The data we need selected is -
Name, display name , mailbox database and Organizational Unit

The script already has the line (Line 3) -
Select Name,DisplayName,Alias,PrimarySMTPAddress,UserPrincipalName |

Now, Edit Line 3 - Remember to use the correct spelling and no spaces
Change the line from
Select Name,DisplayName,Alias,PrimarySMTPAddress,UserPrincipalName |
to
Select Name,DisplayName,Database,OrganizationalUnit |

This final script runs successfully and creates a csv file called FourUsersWithDatabase.csv
As stipulated, it has also recorded the Database and OrganizationalUnit.

Final Script -
Import-CSV "c:\scripts\FourUsers.csv"|
foreach {Get-Mailbox -id $_.UserPrincipalName} |
Select Name,DisplayName,Database,OrganizationalUnit |
export-csv -NoTypeInformation c:\scripts\FourUsersWithDatabase.csv

Congratulations - You have now successfully created a script using the import-csv functionality.

---------------------------------------------------------------------------------------------

Scenario 2 - Mailbox Statistics - Specific Users.
Using the knowledge gained from this tutorial, you can easily create a new script to generate a report on mailbox sizes with an item count and Last Logon Times.

Preparation - Cmdlets
I have run the cmdlet -
Get-MailboxStatistics User01 | fl
And reviewed the spelling of the attributes I need -
LastLogonTime - TotalItemSize - ItemCount
Of course there are many other attributes I can record, but this is all I want at the moment.

Preparation - CSV file
I have edited the existing All-Mailboxes.csv file to only the two users I wish to report on, and saved it as c:\scripts\VIP-Users.csv

Creating / Editing an existing script.

In this instance, I should be able to edit the existing script by changing some properties, but keeping the majority of the script.
Original script -
Import-CSV "c:\scripts\FourUsers.csv"|
foreach {Get-Mailbox -id $_.UserPrincipalName} |
Select DisplayName,Database,OrganizationalUnit |
export-csv -NoTypeInformation c:\scripts\FourUsersWithDatabase.csv

Edit Line 1 - new script name -
Import-CSV "c:\scripts\VIP-Users.csv" |

Edit Line 2 - new cmdlet - (Get-MailboxStatistics)
foreach {Get-MailboxStatistics -id $_.UserPrincipalName} |

Edit Line 3 - New items to select
Select DisplayName,LastLogonTime,TotalItemSize,ItemCount |

Edit Line 4 - new export csv name and location
export-csv -NoTypeInformation c:\scripts\VIPMailboxStats.csv

New Script -
Import-CSV "c:\scripts\VIP-Users.csv" |
foreach {Get-MailboxStatistics -id $_.UserPrincipalName} |
Select DisplayName,LastLogonTime,TotalItemSize,ItemCount |
export-csv -NoTypeInformation c:\scripts\VIPMailboxStats.csv

This script runs successfully and generates the information requested -

Congratulations - You now have the knowledge of how to create basic PowerShell scripts with the Import-csv functionality.

---------------------------------------------------------------------------------------------

Check out a list of ALL of my tutorials here - Link