Typecasting for Import-CSV

Here’s the scenario: you run a script, or basic cmdlet, to obtain some data from PowerShell and export it to a CSV file. Everything seems OK, you can examine the report in Excel or even prepare a beautiful spreadsheet. If you decide however to use that CSV as a source for your next PowerShell task, you might run into a problem. Namely, after you import the CSV in PowerShell (using the Import-CSV cmdlet), all the columns will be of the same type (string). This is of course explained in details in the article, but there is no example to help you get around it.

So, let’s say you have run the Get-Mailbox and Get-MailboxStatistics cmdlets to prepare a mailbox usage report for your company. You later decide that based on the results of this report, some mailboxes need their properties changed. You want to use the already generated report to filter those mailboxes and run the necessary PowerShell cmdlets on them. Of course you can do the filtering with the almighty Excel and everything will be just fine. You are however, after all, PowerShell enthusiast, so why not use PowerShell for the filtering task as well? You import the CSV and use the Where-Object cmdlet to filter those results, but because of the type of the imported values, you’re in for a surprise. Here is what you will get:

 PS C:\O365> $list = import-csv 2014-01-04_16-56-56_MailboxSize.csv

PS C:\O365> $list[0] | gm
TypeName: System.Management.Automation.PSCustomObject

Name                     MemberType   Definition
----                     ----------   ----------
Equals                   Method       bool Equals(System.Object obj)
GetHashCode              Method       int GetHashCode()
GetType                  Method       type GetType()
ToString                 Method       string ToString()
Email                    NoteProperty System.String Email=xxx@xxx.com
ItemCount                NoteProperty System.String ItemCount=4063
Owner                    NoteProperty System.String Owner=xxx@xxx.xxx
TotalItemSize            NoteProperty System.String TotalItemSize=26991790

Notice all the “NoteProperties” in the above input? Notice their Type? This will get you into trouble – sorting/filtering strings instead of numbers will give you some funny results. Try this for example:

$list | ? {$_.ItemCount -gt 100000}

Doesn’t seem to work, right? Instead, here is how to properly import the data, by explicitly declaring the type up front:

$list = import-csv 2014-01-04_16-56-56_MailboxSize.csv | Select Owner,Email,@{Name="ItemCount";Expression={[int32]$_.ItemCount}},@{Name="TotalItemSize";Expression={[int64]$_.TotalItemSize}}

Once you’ve explicitly defined the type, you will have no trouble filtering the needed data, as it is of the correct type:

</span>PS C:\Users\Vasil\Desktop\O365> $list[0] | gm

TypeName: Selected.System.Management.Automation.PSCustomObject

Name          MemberType   Definition
----          ----------   ----------
Equals        Method       bool Equals(System.Object obj)
GetHashCode   Method       int GetHashCode()
GetType       Method       type GetType()
ToString      Method       string ToString()
Email         NoteProperty System.String Email=xxx@xxx.com
ItemCount     NoteProperty System.Int32 ItemCount=4063
Owner         NoteProperty System.String Owner=xxx@xxx.com
TotalItemSize NoteProperty System.Int64 TotalItemSize=26991790

So, now you can easily repeat the example and get the desired result:

$list | ? {$_.ItemCount -gt 100000}

And of course you need to be careful with the type you declare. Using int32 for the total size of a 50GB mailbox, or the quota, is not the way to go.

This entry was posted in Exchange Online, Office 365, PowerShell. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *