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?
CodePudding user response:
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