Powershell Hashtable Export to CSV

Question

I am trying to create a CSV export that contains all rows in the data spreadsheet that the IDs from the search spreadsheet show up in.

I have managed to create the searching element through PowerShell now but am having trouble exporting the data into the CSV format.

Below are some example tables, the actual data has up to 8 values (including ID column), but only the first three are guaranteed to be filled.

Data Spreadsheet

+------+---------+---------+---------+---------------------+ | ID | Value 1 | Value 2 | Value 3 | Value 4 | +------+---------+---------+---------+---------------------+ | 1234 | London | Serial1 | HP | Laptop User | | 2345 | Moscow | Serial7 | | HR Application | | 1234 | London | Serial9 | | Finance Application | | 3456 | Madrid | Serial4 | HP | Laptop User | +------+---------+---------+---------+---------------------+

Search Spreadsheet

+------+ | ID | +------+ | 1234 | | 2345 | +------+

Desired Result

+------+---------+---------+---------+---------------------+ | ID | Value 1 | Value 2 | Value 3 | Value 4 | +------+---------+---------+---------+---------------------+ | 1234 | London | Serial1 | HP | Laptop User | | 2345 | Moscow | Serial7 | | HR Application | | 1234 | London | Serial9 | | Finance Application | +------+---------+---------+---------+---------------------+

Below is the current code that I have with the attempts to export to CSV removed.

$finalValues = @{}

$users = Import-Csv "SEARCH.csv"

$data = Import-Csv "DATA.csv" | Group-Object -property ID -AsHashTable

foreach ($user in $users) 
{
    If ($data.Contains($user.ID))
    {
        #write-output $data[$user.ID].ID
        $finalValues.Add($data[$user.ID].ID, $data[$user.ID])
    }
}

The following two commands (ran after the rest of the script has executed) have the below output.

$finalValues.Values

ID      : 1234
Value 1 : London
Value 2 : Serial 1
Value 3 : HP
Value 4 : 
Value 5 : 
Value 6 : 
Value 7 : Laptop User

ID      : 2345
Value 1 : Moscow
Value 2 : Serial7
Value 3 : 
Value 4 : 
Value 5 : 
Value 6 : 
Value 7 : HR Application

ID      : 1234
Value 1 : London
Value 2 : Serial9
Value 3 : 
Value 4 : 
Value 5 : 
Value 6 : 
Value 7 : Finance Application

$finalValues

{1234, 1234}             {@{ID=1234; Value 1=London; Value 2=Serial1; Value 3=HP; Value 4=; Value 5=; Value 6=; Value 7=Laptop User}, @{ID=1234; Value 1=London; Value 2=Serial 9... ; Value 7 =Finance Application}}

2345                        {@{ID=2345; Value 1=Moscow; Value 2=Serial7; Value 3=; Value 4=; Value 5=; Value 6=; Value 7=HR Application}}           

When exporting to CSV with the following command I get the following result: $finalValues | export-csv -Path test.csv -NoTypeInformation

+------------+-------------+----------------+--------------------------------------------+----------------------------------------------+---------------+-------+
| IsReadOnly | IsFixedSize | IsSynchronized |                    Keys                    |                    Values                    |   SyncRoot    | Count |
+------------+-------------+----------------+--------------------------------------------+----------------------------------------------+---------------+-------+
| FALSE      | FALSE       | FALSE          | System.Collections.Hashtable+KeyCollection | System.Collections.Hashtable+ValueCollection | System.Object |    14 |
+------------+-------------+----------------+--------------------------------------------+----------------------------------------------+---------------+-------+

When exporting to CSV with the following command I get the following result:

$finalValues.Values | export-csv -Path test.csv -NoTypeInformation

+-------+------------+-------------+---------------+----------------+
| Count | IsReadOnly | IsFixedSize |   SyncRoot    | IsSynchronized |
+-------+------------+-------------+---------------+----------------+
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     3 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     2 | FALSE      | FALSE       | System.Object | FALSE          |
|     2 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     2 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
|     1 | FALSE      | FALSE       | System.Object | FALSE          |
+-------+------------+-------------+---------------+----------------+

Solution 1

This could be simplified by using a Where-Object filter and then exporting with Export-CSV.

$Search = Import-CSV "SEARCH.csv"
Import-CSV "DATA.csv" | Where-Object {$Search.ID -Contains $_.ID} | Export-CSV C:\exampleexportpath.csv -NoTypeInformation

Solution 2

@BenH's answer is clearly a better way to implement this, but I just wanted to explain what the issue was with your original code. The problem is that $data is a HashTable mapping a string (the user ID) to an array (actually it's a Collection<PSObject> but for our purposes it behaves the same). Even in the case of ID '2345' where there is only one matching record, $data still stores it as an array with one element:

PS> $data['2345'].GetType().Name
Collection`1
PS> $data['2345'].Count
1
PS> $data['2345']    # Returns the array of values


ID      : 2345
Value 1 : Moscow
Value 2 : Serial7
Value 3 :
Value 4 : HR Application


PS> $data['2345'][0] # Returns the first element of the array of values


ID      : 2345
Value 1 : Moscow
Value 2 : Serial7
Value 3 :
Value 4 : HR Application

Thus, when this line executes...

$finalValues.Add($data[$user.ID].ID, $data[$user.ID])

...you are adding a new item to $data where both the key and the value are arrays. This is why the output of piping $finalValues or $finalValues.Values to Export-Csv behaves as if the values are arrays; it's because they are.

To fix this, when accessing items in $data we'll need an inner loop to "unwrap" each value. Also, we can't use a HashTable for $finalValues because you're using ID as the key but there are duplicate IDs ('1234') in the results. Since all we need is a flat list of records to eventually pass to Export-Csv, we can just use an array instead. Here's some modified code...

$finalValues = @() # Cannot be a HashTable because there may be multiple results with the same ID

$users = Import-Csv "SEARCH.csv"

$data = Import-Csv "DATA.csv" | Group-Object -property ID -AsHashTable

foreach ($user in $users)
{
    If ($data.Contains($user.ID))
    {
        # "Unwrap" the array stored at $data[$user.ID]
        foreach ($dataRecord in $data[$user.ID])
        {
            $finalValues += $dataRecord
        }
    }
}

$finalValues | Export-Csv -Path test.csv -NoTypeInformation

...that produces this CSV...

"ID","Value 1","Value 2","Value 3","Value 4"
"1234","London","Serial1","HP","Laptop User"
"1234","London","Serial9","","Finance Application"
"2345","Moscow","Serial7","","HR Application"

Comments

[Mathias R. Jessen]

$finalValues.Values |Export-Csv ...

[Kickball]

Hi Mathias, Thank you for the response. I had previously tried that but failed to mention it before. I have updated the post with the output with that command.

[Kickball]

Wow, thank you for this comment. It has resolved the problem I had and cut down on code massively. I will update the main post with this.

[Kickball]

thank you for taking the time to explain how the solution would work with the original attempt. I am still getting familiar with data structures in PowerShell and this has been very useful.