CODEWITHSUNDEEP
csvpowershellhashtable
allenr74
allenr74

Reputation: 11

Use Powershell to Create Hash Table from .CSV File with Header

I have a .csv file (delimiter is |) that has AD fields as the header and the strings to go in there as the data. I'd like to create a hash table from that .csv file that has the column header as the key and the string information as the data.

Here is the file format:

LastName|FirstName|Office|Employeeid|BusinessTitle|CoreSector|Sector|CmbCorePractice|CmbPractice|Region|Committees|SectorLeaderRole|PracticeLeaderRole|AreaCountryRole|FirmLeaderShipRoleOne|FirmLeaderShipRole|generationQualifier|givenName|middlename|sn|extensionAttribute12|homePostalAddress|telephoneNumber|ipPhone|facsimileTelephoneNumber|mobile|homePhone|department|manager|assistant|extensionAttribute13
SMITH|JAMES|AMSTERDAM|0000000000|Leader|Healthcare|#|#|#|Europe|#|#|#|#|#|#|MR|JAMES|#|SMITH|#|#|+1 212 000 7692|0000|#|#|#|Knowledge Management|0001000000|#|#|#|#

The data begins with SMITH and that lines up with LastName.

Upvotes: 0

Views: 8284

Answers (3)

Peter Hansen
Peter Hansen

Reputation: 464

I wanted the same result as Doug's Answer but went about it in a bit more compact way. The goal was to read in from a csv file and to be able to add attributes to each row for use later in the script.

$csv = Import-CSV file.csv
$data = $csv| % {
     $row = @{};
     $_.psobject.Properties | % {$row[$_.name]= $_.value};
     $row;
}

Upvotes: 1

Deacon
Deacon

Reputation: 3803

I came across this post while I was trying to determine how to do something similar. My goal was also to take the column names generically from the first line of the CSV (credit to Ansgar Wiechers' excellent answer for solving this issue for me). Ansgar's answer creates a hashtable, where the value of each key is an array of all the values for that key.

For my purposes, I needed an array, where the value of each element is a hashtable. I.e., each element of the array corresponds to a line in the original CSV. This gives me the benefit of being able to iterate through each record in the CSV, and then iterate through each column in the record.

Since the OP's requirements for which format he ultimately needed his data in were nicely ambiguous, I thought I'd post my solution as a supplement to Ansgar's.

Note that to fit the OP's specific use case, it is only necessary to change the value of $DELIM from , to |.

# Constants.
$DELIM = ','
$CSV_F = 'file.csv'

# Parse keys
$keys = (gc "${CSV_F}" -TotalCount 1).Split($DELIM)
$csv = Import-CSV "${CSV_F}"
$data = @()

# Iterate through CSV to build array of hashtables.
ForEach ($r in $csv) {
    $tmp_h = @{}

    # Create hash of key-value pairs.
    ForEach($k in $keys) {
        $tmp_h[$k] = $r.($k)
    }

    # Add hash to array of hashes.
    $data += $tmp_h
}

# Display data
$data.ForEach({[PSCustomObject]$_}) | ft -AutoSize

Given the following sample data in file.csv:

RecordId,Name,Value
1,Alpha,Kilo
2,Bravo,Lima
3,Charlie,Mike
4,Delta,November
5,Echo,Oscar
6,Foxtrot,Papa
7,Golf,Quebec
8,Hotel,Romeo
9,India,Sierra
10,Juliet,Tango

The following output will be generated:

Name    Value    RecordId
----    -----    --------
Alpha   Kilo     1
Bravo   Lima     2
Charlie Mike     3
Delta   November 4
Echo    Oscar    5
Foxtrot Papa     6
Golf    Quebec   7
Hotel   Romeo    8
India   Sierra   9
Juliet  Tango    10

Upvotes: 1

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200273

That's simple enough. Read the first line of the CSV to get the keys for the hashtable, like this:

$keys = (Get-Content 'C:\path\to\your.csv' -TotalCount 1) -split '\|'

or like this:

$keys = (Get-Content 'C:\path\to\your.csv' -TotalCount 1).Split('|')

Next import the CSV:

$csv = Import-Csv 'C:\path\to\your.csv' -Delimiter '|'

Then collect the columns of the CSV into a hashtable:

$ht = @{}
foreach ($key in $keys) {
  $ht[$key] = $csv | Select -Expand $key
}

Upvotes: 4

Related Questions