Powershell replace reformats CSV

Question

I have a csv that I want to remove a few characters from for further processing. I've done this many times before using the regex -replace method, but this time it seems to be messing up the whole csv. I'm using:

$Removed_Characters=$Good_CSV|%{$_ -replace "X",""}

Instead of the same csv without the "X" character, this outputs a series of arrays, one for each row (ie @{Name=Tom, ID=12}@{Name=Bill, ID=15}...). This is not at all what I want, and not what I've seen in the past. What could I be doing differently?

I'm not clear if "reformats" is an important part of your question, or a typo. Do you want to remove or replace text in the CSV? — YetAnotherRandomUser
– YetAnotherRandomUser, Commented Oct 31, 2017 at 1:48

Neossian · Accepted Answer · 2017-10-31 02:03:11Z

1

It looks like you are trying to replace a string in any property of an object array (from import-csv) but you may just be intending to replace a string in a single value.

The script you have provided is converting each object to a string and then perform a string-wise replace on that string value. Giving you what looks like a hash of the object properties.

Replace a string from a particular field in a CSV

@"
Name,ID
Tom,12
Joe,15
"@ | out-file test.csv
$CSVFile = import-csv test.csv 
$CSVFile | %{$_.name = $_.name -replace 'o','i'}
$CSVFile
#Name ID
#---- --
#Tim  12
#Jie  15

Replace a string from any field in a CSV

$Properties = $CSVFile | get-member -membertype noteproperty | select -expand name
$CSVFile | %{
    $entry=$_; 
    foreach ($prop in $Properties) {
        $entry.$prop = $entry.$prop -replace 'o|1','i'
    }
}
$CSVFile
#Name ID
#---- --
#Tim  i2
#Jie  i5

Note that replacing values on all properties is also converting all those values to strings, so you could be losing fidelity (dates etc).

Good Luck!

Shane

answered Oct 31, 2017 at 2:03

Neossian

7054 silver badges14 bronze badges

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

2 Comments

Errorum Over a year ago

Thanks, this works! I looked through some old scripts and found what I used to do. I answered with that below. Even though my way looks worse, I actually found it to be ~2.5x faster with measure-command

Neossian Over a year ago

Your way works, and will definitely be faster, just be aware that your regex might break the CSV file if you aren't careful change heading rows/removing data if you replace a ',' accidentally. etc.

Errorum · Accepted Answer · 2017-10-31 18:56:24Z

0

The trick is using get-content to import the csv as a string.

$Good_CSV | export-csv $out -notypeinformation
$replaced=(gc $out).replace("X","")
$replaced|convertfrom-csv|export-csv $final_out -notypeinformation

Using regex replace is much simpler on a giant string than on a csv object.

answered Oct 31, 2017 at 18:56

Errorum

2234 silver badges17 bronze badges

Collectives™ on Stack Overflow

Powershell replace reformats CSV

2 Answers 2

Replace a string from a particular field in a CSV

Replace a string from any field in a CSV

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Replace a string from a particular field in a CSV

Replace a string from any field in a CSV

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related