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:
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.