PowerShell how to match text in Datatable and Replace

Question

I have a dataTable created and filled in PowerShell from a SQL query.

I need to loop through each datatable row, find a match on (space/space) and replace that with just (/) (per a customer's request)

I need to replace that match for every row.

I've done countless searches and have tried the following:

$dt | where {$_.l4 -cmatch " / "} | foreach {$_.l4 = "/"}

But that replaces the entire value to just (/)

I've tried using replace, creplace and many other ForEach loops, IF statements, etc,..

This is essentially what I am trying to do:

        #loop through the datatable and replace " / " with "/"
        foreach ($Row in $dt.Rows) {     
           if($Row -cmatch " - "){
                $Row -creplace " - ","/"
           }
        }

Please tell me I am blatantly missing something.

I appreciate your help and guidance as I further my knowledge of PowerShell!

Are you doing the replacing in a particular column, or in any column in the row? — zdan, Sep 19 '19 at 17:10
If I can loop through each column in the row to perform the replace, that would be ideal. — grant117, Sep 20 '19 at 01:42

Nick Graham · Accepted Answer · 2019-09-20T16:01:26.350

1

You've pretty much got it but when you do the -creplace you need to assign that new value to existing value of that row in $dt. There's also no need to test for the match prior to doing the replace as -replace will only perform a replacement if it finds a match

I'm assuming that you want to do the replacement in a specific column as in your first code sample. If that's the case this will work:

foreach ($Row in $dt) {

    $Columns = $Row | Get-Member -MemberType Property
    foreach ($Column in $Columns) {

        $Row.($Column.Name) = $Row.($Column.Name) -creplace " / ", "/"

    }

}

edited Sep 20 '19 at 16:01

answered Sep 19 '19 at 17:56

Nick Graham

1,311
3
14
21

I could have sworn I tried. Alas, thank you for pointing that out. So simple, but much appreciated. Is there a way to do this for each column in the row, without specifying a specific column? – grant117 Sep 20 '19 at 01:43
You could use Get-Member -MemberType Property to dynamically get the names of the columns but I'm not sure how you'd use the object that returns to get and set the values in each column. If you're table structure doesn't change much it might be easier to hardcode the column names in – Nick Graham Sep 20 '19 at 10:47
Thank you, @NickGraham. I will continue to see if I can come up with a way to dynamically update the values based on the current row iteration. I appreciate your quick responses and will post back here if I come up with something more dynamic. – grant117 Sep 20 '19 at 14:55
1

I gave up to soon, there is a way to access the properties with a variable. I found the syntax in the comments to [this answer](https://stackoverflow.com/a/14406385/7360635). I've updated my answer – Nick Graham Sep 20 '19 at 16:00
Works perfectly! You are awesome! Don't let anyone tell you otherwise. Seriously, I appreciate the help and glad I learned something new! Thank you! – grant117 Sep 20 '19 at 18:17

PowerShell how to match text in Datatable and Replace

1 Answers1