Powershell using ImportExcel to delete rows

Question

I am trying to delete rows of data from an Excel file using the ImportExcel module.

I can open the file, find the the data I wish to delete and the DeleteRow command works on a hardcoded value however does not appear to work on a variable...any ideas?

# Gets ImportExcel PowerShell Module
if (-not(Get-Module -ListAvailable -Name ImportExcel)) {

    Find-module -Name ImportExcel | Install-Module -Force
}

# Open Excel File
$excel = open-excelpackage 'C:\temp\input.xlsx' 

#Set Worksheet
$ws = $excel.Workbook.Worksheets["Sheet1"]

#Get Row Count
$rowcount = $ws.Dimension.Rows

#Delete row if Cell in Column 15 = Yes
for ($i = 2; $i -lt $rowcount; $i++) {
    $cell = $ws.Cells[$i, 15]
    if ($cell.value -eq "Yes") {      
        $ws.DeleteRow($i)
        
    }
}

#Save File
Close-ExcelPackage $excel -SaveAs 'C:\Temp\Output.xlsx'

Interestingly on a small data set this works however in a larger data set, it appears a number of passes is required to remove all the data rows. I am still interested in anyways of improving this process. Thanks — Azrael211
– Azrael211, Commented Aug 3, 2021 at 11:42

Theo · Accepted Answer · 2021-08-03 13:03:23Z

1

You should reverse the loop and go from bottom to top row. As you have it, by deleting a row, the index of the ones below that is changed and your for ($i = 2; $i -lt $rowcount; $i++) {..} will skip over.

You can also do this without the ImportExcel module if you have Excel installed:

$file  = 'C:\Temp\input.xlsx'
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
# open the Excel file
$workbook = $excel.Workbooks.Open($file)
$sheet    = $workbook.Worksheets.Item(1)
# get the number of rows in the sheet
$rowMax   = $sheet.UsedRange.Rows.Count

# loop through the rows to test if the value in column 15 is "Yes"
# do the loop BACKWARDS, otherwise the indices will change on every deletion.
for ($row = $rowMax; $row -ge 2; $row--) {
    $cell = $sheet.Cells[$row, 15].Value2
    if ($cell -eq 'Yes') {
        $null = $sheet.Rows($row).EntireRow.Delete()
    }
}

# save and exit
$workbook.SaveAs("C:\Temp\Output.xlsx")
$excel.Quit()
# clean up the COM objects used
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

answered Aug 3, 2021 at 13:03

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

1 Comment

Azrael211 Over a year ago

Awesome thank you very much, I hadn't considered the index changing dynamically. I do know about the -comObject way but was hoping to run this serverside without Excel installed.

Collectives™ on Stack Overflow

Powershell using ImportExcel to delete rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related