Home > Enterprise >  Modify text of specific CSV column with regex
Modify text of specific CSV column with regex

Time:09-08

Note: The full script is at the bottom of the question.

Let's assume I have the following CSV file (data.csv). It's a simple file with 2 columns: a name and a path of the corresponding person's folder in drive D.

name,path
A,D:\folder/A
B,D:\folder/B
C,D:\folder/C
D,D:\folder/D
E,D:\folder/E

I would like to change "/" to "" using the replace operator or method in PowerShell.

I begin by importing the CSV file:

$data = Import-Csv -Path "misc/data.csv"
Write-Output $data

name path
---- ----
A    D:\folder/A
B    D:\folder/B
C    D:\folder/C
D    D:\folder/D
E    D:\folder/E

Then I use ForEach-Object to perform the desired operation of the path property like so:

$data | ForEach-Object {
    $_.path -replace "/", "\"
} | Write-Output

What I obtain is unfortunately an array of the modified values:

D:\folder\A
D:\folder\B
D:\folder\C
D:\folder\D
D:\folder\E

After looking at a few examples on the internet, I also tried this:

$data | ForEach-Object {
    $_.path -replace "/", "\"
    $_
} | Write-Output

The result I got was definitely unexpected for me:

name path
---- ----
A    D:\folder/A
D:\folder\B
B    D:\folder/B
D:\folder\C
C    D:\folder/C
D:\folder\D
D    D:\folder/D
D:\folder\E
E    D:\folder/E

Would you please advise? Thank you in advance.

Full script:

# Import CSV file

$data = Import-Csv -Path "misc/data.csv"
Write-Output $data

# Method 1

$data | ForEach-Object {
    $_.path -replace "/", "\"
} | Write-Output

# Method 2

$data | ForEach-Object {
    $_.path -replace "/", "\"
    $_
} | Write-Output

CodePudding user response:

My appreciation goes to @Doug Maurer who pointed out that the modified values need to be assigned back to the path property. The solution is therefore:

$data = Import-Csv -Path "misc/data.csv"

$data | ForEach-Object {
    $_.path = $_.path -replace "/", "\"
    $_
}

name path
---- ----
A    D:\folder\A
B    D:\folder\B
C    D:\folder\C
D    D:\folder\D
E    D:\folder\E
  • Related