Delete rows in excel table with Powershell

Question

I have an excel file with dynamic rows volume, what I want to achieve that to keep only the first 50'000 rows of the file and delete the rest, so if the file has 325,000 then keep the first 50,000 and delete the other 275,000 rows.

Any idea how to do this? I know that this is a novice question, but I don't know how to start with that.

Import-Excel file1.xlsx -StartRow 1 -EndRow 50000 | Export-Excel file2.xlsx — Santiago Squarzon
– Santiago Squarzon, Commented Apr 22, 2021 at 13:31

Andrew · Accepted Answer · 2021-04-23 02:21:39Z

1

Just tested this. It will delete the rows on each sheet, but will fail if the Excel file is already open.

$FilePath = 'C:\Users\Andrew\Documents\DeleteRows.xlsx' #Change path to suit

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
$File = $Excel.Workbooks.Open($FilePath)

ForEach($ws in $File.Worksheets)
{

    $ws.Range($ws.Rows(50001),$ws.Rows($ws.Rows.Count)).Delete()

}

$File.Save()
$File.Close()
$Excel.Quit()


[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($File)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

answered Apr 23, 2021 at 2:21

Andrew

2191 silver badge10 bronze badges

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

Collectives™ on Stack Overflow

Delete rows in excel table with Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related