Powershell script to Find and replace Particular value in CSV file

Question

I have a csv file in path and i need to fetch the file and do Find & Replace $(dollar) value to ,(comma) using PowerShell script. please help me on the script .

CSV file

id|name|place
1|adam$|USA
2|john|USA
3|Jack$|England

Expected output : Need to do Find & Replace $(dollar) value to ,(comma) under name column

id|name|place
1|adam,|USA
2|john|USA
3|Jack,|England

Command Used

Import-csv D:\CSV\Customer.csv
ForEach-Object {  $_.name -replace '$',',' }

i didn't got any error nor the expected changes. i need to update the values in existing CSV file

Below Answer by Zett42 works but

(Import-Csv D:\CSV\Customer.csv -Delimiter '|') |
    Select-Object 'id', 
                  @{ n = 'name'; e = { $_.name.Replace('$', ',') } },
                  'place' |
    Export-Csv D:\CSV\Customer.csv -Delimiter '|'

but the tricky part is we are replacing the ,(Comma) value and rewriting the CSV files again, so CSV file creation part considers the value after ,(comma) as new column and move the value to column B instead of Column A.

Before :

After :

zett42 · Accepted Answer · 2022-06-21 09:31:18Z

3

This should do the trick:

Import-Csv D:\CSV\Customer.csv -Delimiter '|' |
    ForEach-Object { 
        $_.name = $_.name.Replace('$', ',')
        $_  # Implicit output - gets passed to Export-Csv
    } |
    Export-Csv D:\CSV\Customer_new.csv -Delimiter '|' -NoTypeInformation

In your code, there is a pipe symbol | missing at the end of the Import-Csv line.
As the CSV uses non-standard delimiters, you have to specify them using -Delimiter.
Also, the -replace operator doesn't modify its arguments, so you are actually just outputting the replaced value. Also, '$' has special meaning in regular expression patterns, which -replace uses, so you have to backslash-escape it or use the String.Replace() method as I did in the code above. The String.Replace() method doesn't use RegEx.
In the ForEach-Object script block, the $_ at the end is necessary to output the current object again, so it gets send down the pipeline to Export-Csv. Otherwise Export-Csv wouldn't have anything to output.

Alternative code using Select-Object:

Import-Csv D:\CSV\Customer.csv -Delimiter '|' |
    Select-Object 'id', 
                  @{ n = 'name'; e = { $_.name.Replace('$', ',') } },
                  'place' |
    Export-Csv D:\CSV\Customer_new.csv -Delimiter '|' -NoTypeInformation

To overwrite the existing file instead of creating a new file use the group operator () to collect all data in memory and close the old file before overwriting the file:

(Import-Csv D:\CSV\Customer.csv -Delimiter '|') |
    Select-Object 'id', 
                  @{ n = 'name'; e = { $_.name.Replace('$', ',') } },
                  'place' |
    Export-Csv D:\CSV\Customer.csv -Delimiter '|' -NoTypeInformation

IMO this is somewhat cleaner as you don't need to modify the current pipeline object and don't need to explicitly forward it to the next command.

It uses a calculated property to generate the new value for the name column. In this case we don't need to assign the result back to $_.name because calculated properties automatically assign the output from the script block to the property.

edited Jun 21, 2022 at 9:31

answered Jun 20, 2022 at 17:59

zett42

28.3k3 gold badges44 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hell Boy Over a year ago

@zerr42 thanks for the answer .is there any option to update the value in existing CSV file without Creating New CSV File ?

zett42 Over a year ago

@HellBoy Added solution to overwrite existing file.

Hell Boy Over a year ago

yes it works , but the tricky part is we are replacing the ,(Comma) value and rewriting the CSV files again,so CSV file creation considers the data after ,(comma) as new column and place data to column B instead of Column A.

zett42 Over a year ago

@HellBoy When you import CSV in Excel or OpenOffice, you could specify delimiter |. Add parameter -NoTypeInformation to Export-Csv to remove the first line.

Hell Boy Over a year ago

yes it removes first line . is there any option/logic to replace data in CSV file without rewriting the file again. Rewriting CSV file make the format to consider value after comma as new column

Collectives™ on Stack Overflow

Powershell script to Find and replace Particular value in CSV file

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related