PowerShell: Convert an array of Hashtables to a CSV file.

Before you continue please consider clicking on one of the horrible ads. I know they are a pain but they help me pay for the hosting of this site. It owes me a lot of money. Sob story over.

Lets start with some background. I have no official training in PowerShell. I have learned it all of my own back. I am also not a programmer. I am an infrastructure engineer and all my code is written in support of me being an infrastructure engineer.

This means there are things that I may approach in an odd way. This could be one of those things and I feel I am going out on a limb here and my get egg face. However this issue constantly robs me of time and I feel it needs to be addressed for my own sanity if not anyone elses.

If you know an easier way to do this, please let me know.

I like to create hashtables when I'm working. Lets say I have 7 commands to run to get the info I need, I create a hashtable to hold all that data in one place. Invariabley I'm working on lots of objects so I put those hashtables in an array. Nice and handy. Now I need that data in something like Excel so my project manager can do whatever it is that project managers do with data...

If you have been here you probably try something like

$hashtarray | ConvertTo-Csv

And you get some nonsense like this


#TYPE System.Collections.Specialized.OrderedDictionary
"Count","IsReadOnly","Keys","Values","IsFixedSize","SyncRoot","IsSynchronized"
"8","False","System.Collections.Specialized.OrderedDictionary+OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary+OrderedDictionaryKeyValue
Collection","False","System.Object","False"
"10","False","System.Collections.Specialized.OrderedDictionary+OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary+OrderedDictionaryKeyValu
eCollection","False","System.Object","False"

So then you google and get lots of answers that involve 'GetEnummatator()' or 'expandProperty' that ultimately don't work.

Well I feel your pain and have something for you...

The following code will take any 'consistent' array of hashtables, convert it to CSV and save it to location of your choosing and its all wrapped up in a function for you.

You can just drop it into your code and call it like this:

ConvertHashTo-CSV -$hashtable $Hasharray -$OutputFileLocation c:\temp\data.CSV

function ConvertHashTo-CSV
{

Param (
    [Parameter(Mandatory=$true)]
    $hashtable, 
    [Parameter(Mandatory=$true)]
    $OutputFileLocation
    )

$hastableAverage = $NULL #This will only work for hashtables where each entry is consistent. This checks for consistency.

foreach ($hashtabl in $hashtable)
{
    $hastableAverage = $hastableAverage + $hashtabl.count #Counts the amount of headings.
}

$Paritycheck = $hastableAverage / $hashtable.count #Gets the average amount of headings

if ( ($parity = $Paritycheck -is [int]) -eq $False) #if the average is not an int the hashtable is not consistent
    { 
    write-host "Error. Hashtable is inconsistent" -ForegroundColor red
    Start-Sleep -Seconds 5
    return
    }

$HashTableHeadings = $hashtable[0].GetEnumerator().name #Get the hashtable headings 
$HashTableCount = ($hashtable[0].GetEnumerator().name).count #Count the headings

$HashTableString = $null # Strange to hold the CSV

foreach ($HashTableHeading in $HashTableHeadings) #Creates the first row containing the column headings
{
    $HashTableString += $HashTableHeading
    $HashTableString += ", "
}

$HashTableString = $HashTableString -replace ".{2}$" #Removed the last , added by the above loop in error

$HashTableString += "`n"


foreach ($hashtabl in $hashtable) #Adds the data
{

    for($i=0;$i -lt $HashTableCount;$i++)
        {
        $HashTableString += $hashtabl[$i]
            if ($i -lt ($HashTableCount - 1))
                {
                $HashTableString += ", " 
                }       
        }
    $HashTableString += "`n"
}

$HashTableString | Out-File -FilePath $OutputFileLocation #writes the CSV to a file

}

Note it says consistent. All the Hashtables in the array need to be the same for this to work. This is actually a problem for me as I have some data that isn't consistent. As soon as I have a solution for that you will be the first to know

Enjoy!

PowerShell: Convert an array of Hashtables to a CSV file.

Recommended for you