How to create basic PowerShell scripts with Export-CSV

In my other tutorial - How to create basic PowerShell scriptsLink - I showed how to create a basic PowerShell script.

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

In this tutorial, I will show you how to create a basic PowerShell script and export the results to a csv file.
The other script used the Get-Mailbox cmdlet and then I used pipe to export selected results on screen.

Get-Mailbox  VIP01 | fl  Name,DisplayName,PrimarySMTPAddress,UserPrincipalName




I can now take that further by removing the text after the pipe symbol -
Get-Mailbox  VIP01 |

And replacing fl (which stands for Format-List) with Select
Get-Mailbox VIP01 | Select

And then adding the attributes that I want to be selected.
For continuity, I will use the same as before. The new cmdlet will be -
Get-Mailbox VIP01 | Select Name,DisplayName,PrimarySMTPAddress,UserPrincipalName

I could actually use that script, and then pipe out to the screen with - | fl (Format List)
Get-Mailbox VIP01 | Select Name,DisplayName,PrimarySMTPAddress,UserPrincipalName | fl
This will give the same results as earlier, but the script is just written differently.




Now that I have selected the results I want exported, I can now pipe using export-csv. I usually add the switch -NoTypeInformation to provide a cleaner csv file. You will also need to add the folder and file location where you want the csv file to be exported to.
*** You must ensure that folder location already exists ***

The full script would be -
Get-Mailbox VIP01 | Select Name,DisplayName,PrimarySMTPAddress,UserPrincipalName | export-csv -NoTypeInformation c:\scripts\VIP01-Mailbox.csv


Once this has run successfully, the csv file will be created and located in the file location you specified.






When you open the file in Excel, you will see that the spreadsheet has the information you requested.





Now that we have created a script to export to csv, we can start to create more powerful scripts from this base knowledge.

This script can be easily edited to generate the information for all mailboxes by simply removing the mailbox name. The script will then run the cmdlet get-mailbox (all mailboxes as we have not identified a specific mailbox), and then select only the Name, DisplayName, PrimarySMTPAddress and UserPrincipalName. The script will then export that data to a csv file in the location specified.

The full script would be
Get-Mailbox | Select Name,DisplayName,PrimarySMTPAddress,UserPrincipalName | export-csv -NoTypeInformation c:\scripts\All-Mailboxes.csv

Once successfully run, you can open the csv file which will look similar to this.


This is the perfect script to create a list of mailboxes that can be used for Exchange Administration or Office 365 Administration. This csv of mailboxes can be imported into scripts for targeting specific mailboxes for reports or general administration in both on-premise Exchange or Office 365.

You can easily discover all the mailbox settings that can be exported by running the get-mailbox cmdlet on a single mailbox and pipe the results to screen with | fl
get-mailbox VIP01 | fl

















This will output ALL the basic attributes from the get-mailbox cmdlet.
Scroll down and select the ones you want to capture in your scripts (copy - paste).

The five main attributes to export that will cover the vast majority of future scripts are -
Name, DisplayName, Alias, UserPrincipalName, PrimarySMTPAddress

This is because most scripts that you will create and use on a daily basis will need at least one of those attributes that can be imported into future scripts. For example some cmdlets need the alias, and some cmdlets will need the PrimarySMTPAddress or the UserPrincipalName.

If you create an export of all your mailboxes with those five attributes, you can then use this csv file to be imported into future scripts.

The final script that I will run in this tutorial will create a csv file of all the mailboxes with those five main attributes. This new script will include the alias attribute.

The full script would be
Get-Mailbox | Select Name,DisplayName,Alias,PrimarySMTPAddress,UserPrincipalName | export-csv -NoTypeInformation c:\scripts\All-Mailboxes.csv

This has provided the new csv file below
(Note that I have hidden the Discovery Search Mailbox in the spreadsheet)















This csv file will be imported in scripts used in future tutorials.

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

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

Basic PowerShell Tutorials
01. How to configure your desktop PC for Office 365 Administration - Link
02. How to connect to Office 365 via PowerShell - Link
03. How to create basic PowerShell scripts - Link
04. How to create basic PowerShell scripts with Export-CSV - Link
05. How to create basic PowerShell scripts with Import-CSV - Link

Series Tutorials -
How to manage Enterprise environments - Part 1 - Filtering queries - Link
How to manage Enterprise environments - Part 2 - Creating scripts with a filtered query - Link
How to manage Enterprise environments - Part 3 - Bulk management using multiple filters - Link

Tips and Tricks
General Tips and Tricks for better Office 365 Administration - Link
How to extend your Office 365 Trial - Link
How to get a 180 day trial tenant in Office 365 for testing - Link

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

No comments:

Post a Comment