Exporting Collection of Hashtable data to CSV

4
votes

I am trying to export to CSV the name/value pairs of a collection hashtable items. The I have not found the correct syntax for the select-object portion of the code. I would the CSV file to have columes for Url and Owner. Thanks for the help

[System.Collections.ArrayList]$collection = New-Object System.Collections.ArrayList($null)
$SiteInfo = @{};

$SiteInfo.Url = "http://some.url.com";
$SiteInfo.Owner = "Joe Smith";
$collection.Add($SiteInfo);

$SiteInfo.Url = "http://another.url.com";
$SiteInfo.Owner = "Sally Jones";
$collection.Add($SiteInfo);


$collection | foreach{
    $hashTableDate = $_;
    $hashTableDate | Select-Object -Property Url, Owner;
}| Export-Csv  "C:\UsageReport.csv" -NoTypeInformation -Encoding UTF8 -Delimiter '|'

# results: file is empty :(
1
powershell-2.0

1 Answers

10
votes

Convert to PSObject and then export to CSV

All that "foreach" stuff is not necessary. Just convert your hash to PSObject then export!

This example uses an ArrayList to store the HashTables and then exports that ArrayList to CSV.

[System.Collections.ArrayList]$collection = New-Object System.Collections.ArrayList($null)

$SiteInfo = @{}
$SiteInfo.Url = "http://some.url.com"
$SiteInfo.Owner = "Joe Smith"
$collection.Add((New-Object PSObject -Property $SiteInfo)) | Out-Null

$SiteInfo = @{}
$SiteInfo.Url = "http://another.url.com"
$SiteInfo.Owner = "Sally Jones"
$collection.Add((New-Object PSObject -Property $SiteInfo)) | Out-Null


$collection | Export-Csv  "UsageReport.csv" -NoTypeInformation -Encoding UTF8 -Delimiter '|'

Things that won't work.

Note that while this here works:

$collection.Add((New-Object PSObject -Property $SiteInfo))

... here are some things that will NOT work:

  • Leaving out one set of parenthesis will NOT work:

    $collection.Add(New-Object PSObject -Property $SiteInfo)

  • Leaving out the -property argument label will NOT work:

    $collection.Add((New-Object PSObject $SiteInfo))

  • Just using += will NOT work:

    $collection += $SiteInfo

For these you will get error messages and/or weird entries in the CSV file.

Why "Out-Null"?

Additional note: $collection.Add() outputs the index of the highest valid index when you run it. The | Out-Null just throws away that number if you don't want it.