CODEWITHSUNDEEP
excelpowershell
RaWkStAr
RaWkStAr

Reputation: 3

Search & Replace in Excel without looping?

This seems to be the common answer on the internet for how to do a bulk search and replace on an excel file column.

My problem is that on a file of only 8K rows, it takes 5 minutes to replace a simple two-character string in a single column and the file isn't even 1MB in size.

Is there a faster/better way, or even a way to optimize this?

Current code: (made a little more modular and re-usable by putting the search/replace logic in a separate function)

function excel_search_replace ( $worksheet, $column_name, $search_str, $replace_str ) {
    echo "Replacing all '$search_str' with '$replace_str' in column '$column_name'"
    $range = $worksheet.Range( "$($column_name)1" ).EntireColumn
    $search = $range.find( $search_str )
    $i = 0

    if ( $search -ne $null ) {
        $i += 1
        $first_addr = $search.Address
        do {
            $i += 1
            $search.value() = $replace_str
            $search = $range.FindNext( $search )
        } while ( $search -ne $null -and $search.Address -ne $first_addr )
    }
    echo "...Found and replaced $i instances of '$search_str'"
    return $void
}

$source_file = 'C:\some\excel\file.xlsx'
$excel_obj = New-Object -ComObject 'Excel.Application'
$excel_obj.DisplayAlerts = $false
$excel_obj.Visible = $false
$workbook = $excel_obj.Workbooks.Open( $source_file ) # Open the file
$sheet = $workbook.Sheets.Item( 1 ) # select target worksheet by index

excel_search_replace $sheet 'A' 'find this' 'and replace with this'
[void]$workbook.save() # Save file
[void]$workbook.close() # Close file
[void]$excel_obj.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject( $excel_obj ) >$null # Release COM

Upvotes: 0

Views: 4068

Answers (1)

MBG
MBG

Reputation: 102

$Excel = New-Object -ComObject Excel.Application
$Workbook=$Excel.Workbooks.Open("Files\MyFile.xlsx”)
$WorkSheet = $Workbook.Sheets.Item(1)
$WorkSheet.Columns.Replace("ThisNeedsTobeReplaced","ImReplaced")

Upvotes: 5

Related Questions