Resolved: Powershell: search & replace in xlsx except first 3 columns

Question:

I need to run PS script on multiple xlsx files where I need to search and replace certain values. Script has to check entire sheet, but needs to ignore first 4 columns, aka, it has to “start” from column number 5. Is there a way how to do that with the script below please? Those first 4 columns need to be present when exporting/saving a final xlsx file. Thank you.

# variables
$name = "1stname"
$surname = "Surname"


# xlsx to work with
$filename =  Get-ChildItem -Path .\*.xlsx
$xldata = Import-Excel -Path $filename -WorksheetName "Sheet1"
$columns = $xldata[0].psobject.Properties.Name

#script
foreach ($row in $xldata) {
foreach ($cell in $columns) {
$oldvalue = $row.”$cell”
$newvalue = $oldvalue -replace $name, $surname
$row.”$cell” = $newvalue
}
}

# save xlsx file
$xldata | Export-Excel -Path $filename -WorksheetName “Sheet1” -ClearSheet

Answer:

You could replace your second foreach loop with a for loop instead, as you’ll then be able to skip the first x records as desired.
It would look like this to skip the first 4 columns:

# xlsx to work with
$filename = Get-ChildItem -Path .\*.xlsx
$xldata = Import-Excel -Path $filename -WorksheetName "Sheet1"
$columns = $xldata[0].psobject.Properties.Name
foreach ($row in $xldata) {
    for ($i = 4; $i -lt $columns.Count; $i++)
    {
        $cell = $columns[$i]
        $oldvalue = $row."$cell"
        $newvalue = $oldvalue -replace $Space, $ReplaceSpace
        $row."$cell" = $newvalue
    }
}

# save xlsx file
$xldata | Export-Excel -Path $filename -WorksheetName "Sheet1" -ClearSheet

Replace the $i = 4 with another number if you want to start on a different column number instead.

If you have better answer, please add a comment about this, thank you!

Resolved: Powershell: search & replace in xlsx except first 3 columns

Resolved: How to get Union type from an array of objects in Flow?

Resolved: Modify entity using Action in C#

Resolved: How to give rank on datetime column group by another column with userid in it

Leave A Reply