Get values out of psobject

ricardogomes2 · 2015-02-27T10:39:51.000Z

Hello everyone,

I m making a powershell script to get values out of a Excel spreadsheet, by first open the file converting to csv and then retrieve the data out of it (found easier and quicker to do this way). The problem i have is getting values out of the CSV columns, the reason is besides the first 4 columns (witch are always identical or all cases) the numer of columns and their names are diferent so i cannot do this:

write-host $data[$x].randomfield

because the “randomfield” is … always diferent.

Where i m having issues is after “reconstructing” the table from the CSV i m unable to get the values as usable text, in the script i have bellow where i have issues is in this part:

$skill = $data[$x].psobject.properties | select name | where { $_.name -eq $headers[$i] }
$level = $data[$x].psobject.properties | select value | where { $_.name -eq $headers[$i] } | Select-Object -Skip ($csvHeaders.Count-$headers.Count)

I m trying to get something like “guy with ID $variable, has the skill $skill on the level $level”, i always get something like @{value=2,value=1,value=3} instead of 3 for example. I did this script ask me anything you see fit, i really tried to explain the best i can but i found hard to do so … any help i can get a hit or solution is welcome.

Full script:

# ---> Configurações
$xlsx = "C:\1\skills.xlsx"
$tmpCSV = "C:\1\skils.csv"
$csvHeaders=@('id','nome','categoria','especialidade')

# ---> Abre uma instancia de Excel
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false

# ---> Abrir folha de Microsoft Excel na instancia e exporta para csv
$wb = $xl.Workbooks.Open($xlsx)
$wb.SaveAs($tmpCSV, 6)
$wb.Close($false)
$xl.Quit()

# ---> Mata processos de Excel ainda aberto ($xl.Quit() por vezes não funciona ...)
$process = Get-Process -Name 'excel' -ErrorAction SilentlyContinue
if($process.Count -gt 0){
    foreach($toKill in $process){
        $toKill.kill()
    }
}

# ---> Importar CSV criado do ficheiro de Excel
$csv = Import-Csv $tmpCSV -Encoding Default

$headers = Get-Member -InputObject $csv[1] | Where-Object {$_.MemberType -eq "NoteProperty"} | select-object Name
$headers = $headers -split ";" | Where { $_.length -gt 0 }
$headers = $headers[1..($headers.Length)]
$headers = $headers -replace '[{}]'

$csvHeaders=$csvHeaders+$headers
$data = Get-Content $tmpCSV | Select-Object -Skip 3 | ConvertFrom-Csv -Header $csvHeaders -Delimiter ';'

for($x=0;$x -le $data.Count; $x++){
	#$data[$x].id
	#$data[$x].nome
	for($i=0;$i -le $headers.Count;$i++){
		$skill = $data[$x].psobject.properties | select name | where { $_.name -eq $headers[$i] }
		$level = $data[$x].psobject.properties | select value | where { $_.name -eq $headers[$i] } | Select-Object -Skip ($csvHeaders.Count-$headers.Count)
	}
	write-host _______________________________________________
}

craigduff · 2015-02-27T12:44:45.000Z

See if this is the technique you are looking for:

$csv = Import-Csv "T:\110\in.csv"

$headers = $csv[0] | 
Get-Member -MemberType NoteProperty | 
Select-Object -ExpandProperty Name

ForEach($item in $csv) {
    ForEach($header in $headers) {
        "$header :: $($item.$header)"
    }
}

ricardogomes2 · 2015-02-27T14:29:33.000Z

Yes thank you very much that worked as i wanted!