powershell - Import-Csv and creating hashtable failing

Question

My CSV file has employee data and each person gets 2 codes, one for login and one for their positionID. To find their manager, I need to lookup the Reports to PositionID table as a cross reference. The problem I'm having is when I use Import-Csv, the first user in the file is terminated and the main login ID is removed from the file. I think that's why the following hash table fails with "Index operation failed; the array index evaluated to null"?

I tried

$csvData = Get-Content -Path $Maincsv | Select-Object -Skip 2 |
           Out-String | ConvertFrom-Csv 

But this fails as well. How can I make adjustments to correctly build my table so it doesn't crash?

$csvData = Import-Csv "C:My.csv"
$PIDTable = @{}
$csvData | ForEach-Object {
    $PIDTable[$_."Position ID"] = $_
}

Data:

PersonID   Position ID Legal First Name    Reports To Position ID
        YQM000051   DIANE           YQM000076
S9999991    YQM000052   CHARISSE        YQM000076
S9999992    YQM000052   CHARISSE        YQM000076
s9999993    YQM000052   CHARISSE        YQM000076
s9999994    YQM000076   Bob         YQM000071

So, trying to cross reference the PersonID from "Reports To Position ID" so that a PersonID could be found for someone's manager Position ID. Sorry for messy format. But can the blank PersonID at the start, mess the array up?

Answer 1

Your data appears to be tab-separated, not comma-separated. Import-Csv expects data to be comma-separated unless you specify a different delimiter via the parameter -Delimiter. Since you didn't do that the cmdlet reads the file into data structures like this:

{ "PersonID   Position ID Legal First Name    Reports To Position ID": "        YQM000051   DIANE           YQM000076" }
{ "PersonID   Position ID Legal First Name    Reports To Position ID": "S9999991    YQM000052   CHARISSE        YQM000076" }
...

rather than the intended data structures:

{
  "PersonID": ""
  "Position ID": "YQM000051"
  "Legal First Name": "DIANE"
  "Reports To Position ID": "YQM000076"
}
{
  "PersonID": "S9999991"
  "Position ID": "YQM000052"
  "Legal First Name": "CHARISSE"
  "Reports To Position ID": "YQM000076"
}
...

Because of that your objects don't have a property Postition ID. Trying to create hashtable entries from these missing properties causes the errors you observed.

Change

$csvData = Import-Csv "C:My.csv"

to

$csvData = Import-Csv "C:My.csv" -Delimiter "`t"

and your code should behave as you expect.