iicngz Contributor.
Contributor.
505 views

Scriptlet in HP-OO How to Remove Duplicates in Excel & How to Delete Column(F) in Excel

Hi Coworks,

 

My problem is there; there has a empty column(F) because of my txt folder.  The excel has 44284 rows but there has 3100 duplicates... i need to delete F column and  remove 3100 duplicates rows with operations or JS,.

Please share your thoughts,

Thanks in advance,

Regars,

İbrahim.

0 Likes
4 Replies
Micro Focus Expert
Micro Focus Expert

Re: Scriptlet in HP-OO How to Remove Duplicates in Excel & How to Delete Column(F) in Excel

Hi,

Usually when i want  (or need to) modify excel files  in such a way  you mention I tend to favor using powershell scripts to modify the files. The main limitation of which is that Excel needs to be installed on the  server where the script is executed. 

Here is a sample script for deleting rows or collumns:

http://www.sqltact.com/2013/11/powershell-delete-unneeded-rowscolumns.html

 

Hope this helps,

Vlad

0 Likes
AndreiTruta Outstanding Contributor.
Outstanding Contributor.

Re: Scriptlet in HP-OO How to Remove Duplicates in Excel & How to Delete Column(F) in Excel

Besides Vlad's very good hint which should provide you all the details to acomplish that with powersheel you could make use of ootb operations that we have for excel if you make usage of some tips:

a. add an extra check to the flow logic and do not add the empty rows from your text to the excel

b. consider relaying on an excel template - original excel file on which you make the additions that you could format or add logic so that the empty rows are filtered out by default when someone reads out the file

c. consider reading the file twice to

1. get table like data (row 0 to n colum 1 to e)

2. get table like (row 0 to n column g to h) - thus skip column F here

3. put all the data to a new worksheet or excel file

Note: notice that get excel data has the option to trim empty cells and rows so you could play with these options too.

Hope it helps,

Andrei Vasile Truta
0 Likes
iicngz Contributor.
Contributor.

Re: Scriptlet in HP-OO How to Remove Duplicates in Excel & How to Delete Column(F) in Excel

Hi All,

Primarily thank you for feedbacks, I did delete column in .xlsx but it should be  .csv extension ,otherwise; it returns error.

I'm sharing my code:

 

PS:  unneeded column 6 like "F"


$file = "C:\Projects\dumpOpssData_20171019.xlsx"
$ColumnsToKeep = 1,2,3,4,5,7    


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

# Open the CSV File
$workbook = $excel.Workbooks.Open($file)
$sheet = $workbook.Sheets.Item(1)

# 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:\Projects\dumpOpssData_20171019.csv", 6)

# Close excel and release the com object.
$workbook.Close($true)
$excel.Quit()

0 Likes
iicngz Contributor.
Contributor.

Re: Scriptlet in HP-OO How to Remove Duplicates in Excel & How to Delete Column(F) in Excel

Hi all,

I successfully deleted specific column xlsx intension in excel. benefit from blow code.

 

$file = "C:\Projects\dumpOpssData_20171020.xlsx"
$excel = new-object -com Excel.Application -Property @{Visible = $false}
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $workbook.Sheets.Item(1) # Activate the first worksheet
[void]$sheet.Cells.Item(1,6).EntireColumn.Delete() # Delete the first column
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.