PowerShell read column value from csv file

Question

This is my below input data which is in CSV format and roles,admin, accountant and security are columns.

roles, admin,accountant,security
Engineer,  ,x , ,

I want to get value of rows using columns with below code, example , foreach for accountant column should return 'x', but I am getting something else.

$path = "$PSScriptRoot\Test.csv"
$csv = Import-Csv -path $path -Delimiter ","
$columns = $csv | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name'

$csv | ForEach-Object {

$row = $_
foreach ($col in $columns) {

   Write-Host " vaalue of scripting is : $col " 
   Write-Host " vaalue of scripting row is : $row.$col " 
   Start-Sleep -s 10

}

}

Output I get is

 vaalue of scripting is : accountant 
 vaalue of scripting row is : @{roles=Engineer; admin=; accountant=x ; security=}.accountant 
 vaalue of scripting is : admin 
 vaalue of scripting row is : @{roles=Engineer; admin=; accountant=x ; security=}.admin
 vaalue of scripting is : roles 
 vaalue of scripting row is : @{roles=Engineer; admin=; accountant=x ; security=}.roles

How can I get 'x' for accountant column or any other column value using

I'm on my cellphone right now :) look for `$( ) subexpression operator` from this link https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_operators?view=powershell-7.2 to understand why it works and you can answer your own question if you feel so. — Santiago Squarzon, Nov 27 '21 at 16:54
@SantiagoSquarzon no hurries. When you back on your comfort, please give as solution, so that I can accept it — Joe_12345, Nov 27 '21 at 16:59
In short: In order to embed _expressions_ in an expandable string (`"..."`), you must enclose them in `$(...)`. Notably, this includes property and indexed access (e.g., `$($var.property)`, `$($var[0])`). Only variables _as a whole_ do not require this (e.g., `$var`, `$env:USERNAME`). See [this answer](https://stackoverflow.com/a/40445998/45375) to the linked duplicate. — mklement0, Nov 27 '21 at 20:12

Santiago Squarzon · Accepted Answer · 2021-11-27T21:44:00.837

Following from my comment, short answer was to use the Subexpression operator $( ), which would allow PowerShell to reference the property $col of the object $row.

Short extract from MS Docs:

Use this when you want to use an expression within another expression. For example, to embed the results of command in a string expression.

To give you a short explanation of why this is needed, using this as an example:

$object = [pscustomobject]@{
    foo = 'var'
}

$property = 'foo'

When we do "$object.$property" or in simple terms, "$object.foo", the double quotes "..." are not allowing PowerShell to reference the foo property from $object because the dot . is interpreted as literal and not as a dot method. In addition, the quotes are converting $object to its stringified ? representation @{foo=var} followed by the literal dot . followed by the variable expansion of $property.

Another extract from about_Properties:

The most common way to get the values of the properties of an object is to use the dot method. Type a reference to the object, such as a variable that contains the object, or a command that gets the object. Then, type a dot (.) followed by the property name.

Lastly, what other alternatives do we have to get around this besides $(...):

String Formatting and String.Format method:

'Value of $object.$property is "{0}".' -f $object.$property

[string]::Format('Value of $object.$property is "{0}".', $object.$property)

Using + to concatenate strings is also a very known one:

'Value of $object.$property is "' + $object.$property + '".'

As a side note, and unrelated to the actual issue, this might be a more direct way of approaching your code:

@'
roles,admin,accountant,security
Engineer,,x,,
Operator,,y,,
'@ |
ConvertFrom-Csv | ForEach-Object -Begin { $i = 1 } -Process {
    foreach($Property in $_.PSObject.Properties.Name)
    {
        
        'Value of Row {0} Column "{1}" is "{2}"' -f 
            $i, $Property, (
                'NULL', ($val = $_.$Property)
            )[[int][bool]$val]
    }
    $i++
}

Note the use of .PSObject to access the object's properties and methods, an alternative to Get-Member.

The above would result in:

Value of Row 1 Column "roles" is "Engineer"
Value of Row 1 Column "admin" is "NULL"
Value of Row 1 Column "accountant" is "x"
Value of Row 1 Column "security" is "NULL"
Value of Row 2 Column "roles" is "Operator"
Value of Row 2 Column "admin" is "NULL"
Value of Row 2 Column "accountant" is "y"
Value of Row 2 Column "security" is "NULL"

score 0 · Answer 2 · answered Nov 27 '21 at 19:49

I wrote this based on your csv file, let me know if it worked (change the folder path and file name).

$folderspath = 'C:\Test'
$csvfilename = 'info.csv'
$csvfilepath = $folderspath + "\" + $csvfilename
$csvfilepath = $csvfilepath.ToString()

$csvfile = Import-CSV -Path $csvfilepath -Delimiter ","
    ForEach ($row in $csvfile) {
        IF($row.security -eq "High") {
            $Roles = $row.roles
            $Admin = $row."admin"
            $Accountant = $row.accountant
            $Security = $row."security"

            Write-Host "Roles: " $Roles "; Admin:" $Admin "; Accountant:" $Accountant "; ` 
            Security:" $Security
    }
}

The csv file I used

roles, admin,accountant,security
Engineer,  ,x , ,
Engineer2,Yes ,x ,High,
Engineer3, No, , Low,

PowerShell read column value from csv file

2 Answers2