Select Excel row --> Create Hashtable from Entries

Question

I have an Excel .xlsx-file which looks like this:

---

Now I'd like to create a PowerShell script, which can do the following:

1. Ask the User which row he wants to use (for example 4)
2. Create a hashtable with those entries

The hashtable should look like this:

Name                 Value
----                 -----
Name                 Jane Doe
Age                  67
Street               Grace St. 19
Zipcode              12345
Date                 03.03.2013

Does someone know how I can achieve this?

Plus: Is this actually achievable with a xlsx-File or do I need to use a CSV-file?

Answer 1

A more intuitive way is to

• read the excel file directly with the Import-Excel module
• pipe it to Out-Gridview, select a single line
• and populate the hashtable

---

$HashTable = [ordered]@{}

Import-Excel "x:\path\sample.xls" | Out-GridView -OutputMode Single -Title "Select one line" | 
  ForEach-Object {$_.psobject.properties | ForEach-Object {
    $HashTable[$_.Name]=$_.Value
  }
}
$HashTable

Sample output:

> $HashTable

Name                           Value
----                           -----
Name                           Molly Johnson
Agr                            35
Street                         Murraay St. 86
Zipcode                        54321
Date                           02.02.2009