How to update cells in csv one by one without knowing column names

April 26, 2022

I try to replace some texts inside a csv like following:

$csv = Import-Csv $csvFileName -Delimiter ';'
foreach ($line in $csv)
{
    $properties = $line | Get-Member -MemberType Properties
    for ($i = 0; $i -lt $properties.Count;$i++)
    {
        $column = $properties[$i]
        $value = $line | Select -ExpandProperty $column.Name
        
        # Convert numbers with , as separator to . separator
        if ($value -match "^[+-]?(\d*\,)?\d+$")
        {
            $value = $value -replace ",", "."
            # HOW TO update the CSV cell with the new value here???
            # ???
        }
    }
}
$csv | Export-Csv $csvFileName -Delimiter ',' -NoTypeInformation -Encoding UTF8

As you can see, I miss the line where I want to update the csv line’s cell value with the new value => can someone tell me how I can do that?

>Solution :

Assuming the regex you have in place will match the pattern you expect, you can simplify your code using 2 foreach loops, easier than a for. This method invokes the intrinsic PSObject member, available to all PowerShell objects.

$csv = Import-Csv $csvFileName -Delimiter ';'

foreach ($line in $csv) {
    foreach($property in $line.PSObject.Properties) {
        if ($property.Value -match "^[+-]?(\d*\,)?\d+$") {
            # regex operator not needed in this case
            $property.Value = $property.Value.Replace(",", ".")
        }
    }
}

$csv | Export-Csv ....

You can also do all process in pipeline (method above should be clearly faster, however this method is likely to be memory friendlier):

Import-Csv $csvFileName -Delimiter ';' | ForEach-Object {
    foreach($property in $_.PSObject.Properties) {
        if ($property.Value -match "^[+-]?(\d*\,)?\d+$") {
            # regex operator not needed in this case
            $property.Value = $property.Value.Replace(",", ".")
        }
    }
    $_ # => output this object
} | Export-Csv myexport.csv -NoTypeInformation