topic Need To delete bulk excel data in Product Forum

Need To delete bulk excel data

sivaranjani_96 — Sun, 26 Sep 2021 06:34:45 GMT

Hi,
I am handling excel which is having around 70k records daily,
1) I have 3 sheets namely Brand,Sheet1,Position, values are copied from Brand sheet and pasted into sheet1
2)once value pasted into sheet1 of respective columns the E and F of sheet1 will be auto populated based on the formulas
3)I have to filter E column where value =N/A which is around 30k
4)after filtering I have to delete these values so that 3rd sheet which is position gets updated automatically.

The problem is when I do it manually it takes 25 mins to delete entire filtered data rows, Bot takes an hour
I tried using OLEDB but it says deletion cannot be performed by linked table ISAM.
Sheet1:Screenshot below
Note: Column A:D having data it's blank

Please suggest me a good way to do this.
Thanks.

Re: Need To delete bulk excel data

EVIPUTI — Sun, 26 Sep 2021 10:08:52 GMT

Using Delete command you can’t delete data from Excel as a database because OLEDB provider only allows to select, insert or update records to excel sheet. It does NOT allow delete operations. You can use the update command to delete your data , simply set the columns to NULL where value = #N/A

Re: Need To delete bulk excel data

sivaranjani_96 — Sun, 26 Sep 2021 11:54:20 GMT

@vipul Tiwari,
It's not working below is the query
"UPDATE [Sheet1$]
SET [ISO COUNTRY CODE]=null,
SET[BRAND CODE]=null,
SET[BRAND POSITION CODE]=null,
SET[MARKET CODE]=null
WHERE [category]='N/A'"

It's showing below error,

Please advise.

Re: Need To delete bulk excel data

sivaranjani_96 — Mon, 27 Sep 2021 05:34:46 GMT

Hi,
Is there any code to filter data and delete those filtered data faster, Please help me on this.

Thanks,
Sivaranjani.

Re: Need To delete bulk excel data

krishna.kodimurali9 — Mon, 04 Oct 2021 14:58:40 GMT

Hi Sivaranjani,

There is no inbuilt action to delete filtered rows in excel. We need to create a custom action for that which gonna take time and we only should invest that time when its that necessary.

for the time being, you can use the excel macro to filter and delete the resultant visible rows .
You can update the code according to your requirments.No need to mention the sheet range, just update the last column, as it gets the range till the last row .
Field- the Numeric equivalent of the column
Creteria1- Filter criteria to be selected

Sub DeleteFilteredRows()
'
' DeleteFilteredRows Macro
'
Range("A1").Select
Selection.AutoFilter
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A:$X").AutoFilter Field:=3, Criteria1:="#N/A"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
Selection.AutoFilter
Range("A2").Select
End Sub