Monday, February 11, 2019

Powershell : delete desired columns from XLSX


$file = "c:\test\book4.xlsx" # here goes the path and name of the excel file.
$ColumnsToKeep = 1,4,6 # Specify the column numbers to delete.

# Create the com object
$excel = New-Object -comobject Excel.Application # Creating object of excel in powershell.
$excel.DisplayAlerts = $False
$excel.visible = $False

# Open the XLSX File
$workbook = $excel.Workbooks.Open($file)
$sheet = $workbook.Sheets.Item(1) # Referring to first sheet.

# Determine the number of rows in use
$maxColumns = $sheet.UsedRange.Columns.Count

$ColumnsToRemove = Compare-Object $ColumnsToKeep (1..$maxColumns) | Where-Object{$_.SideIndicator -eq "=>"} | Select-Object -ExpandProperty InputObject
0..($ColumnsToRemove.Count - 1) | %{$ColumnsToRemove[$_] = $ColumnsToRemove[$_] - $_}
$ColumnsToRemove  | ForEach-Object{
    [void]$sheet.Cells.Item(1,$_).EntireColumn.Delete()
}

# Save the edited file
$workbook.SaveAs("C:\test\newfile.csv")

# Close excel and release the com object.
$workbook.Close($true)
$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel 

2 comments:

Jaspreet Singh said...

It is working perfectly

Anonymous said...

Hi man, can you take a look on this script i am getting errors, while i reinstalled new office 365. I guess they might changed something? Now i get this error:
"You cannot call a method on a null-valued expression.
At line:11 char:1
+ $sheet = $workbook.Sheets.Item(1) # Referring to first sheet.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Unable to index into an object of type System.Int32.
At line:17 char:37
+ ... move.Count - 1) | %{$ColumnsToRemove[$_] = $ColumnsToRemove[$_] - $_}
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : CannotIndex

You cannot call a method on a null-valued expression.
At line:19 char:1
+ [void]$sheet.Cells.Item(1,$_).EntireColumn.Delete()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))
At line:23 char:1
+ $workbook.SaveAs("W:\ServiceNow_Business_Application_Extract.xlsx")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))
At line:26 char:1
+ $workbook.Close($true)
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Exception calling "Quit" with "0" argument(s): "Exception from HRESULT: 0x800AC472"
At line:27 char:1
+ $excel.Quit()
+ ~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : COMException
"
Any suggestion would be helpfull