Deleting matches in a huge Excel file

[aaydin (Ahmet Aydin)]

Hello,

I have a huge Excel file with over 60 k lines and 20 columns (A:T).

I strive to delete all match numbers where column T (“2.Flag”) is “Y”.
But it is very important that the same match numbers in other lines are also deleted.

Finally the output has to be all columns.

How can I solve this issue?

Many thanks in advance!

[prasath_S]

Hi @aaydin

Please try below steps,

1. Read the excel sheet data into a datatable using read range activity. Variable name - dt

2. Use write range activity and pass the query in datatable property.

dt.asenumerable.where(function (row) not row(“T (“2.Flag”)”).tostring.trim.toupper.equals(“Y”)).copytodatatable

Note : give different sheet name in write range, so it will not overwrite the source data.

Thanks

[aaydin (Ahmet Aydin)]

Hi @prasath_S

will this also consider identic match numbers with “N”?

[prasath_S]

You want to delete rows if the last column is “N” and match number is specific number.

What number you want to delete.

If possible please share input and output files.

Thanks

[aaydin (Ahmet Aydin)]

First it should identify all match numbers with a “Y”. In the second step all identified match numbers have to be deleted. There are sometimes duplicate match numbers. So it is very important that these duplicates will also be delted.

[Srini84 (Srinivas Kadamati)]

@aaydin

Use Filter Datatable and give the condition to filter rows with Y

Hope this may help you

Thanks

[prasath_S]

Hi @aaydin

Please try below steps,

1. Read the excel data into a datatable variable name dt.

2. Create a array variable of type string.variable name arrStr

3. On assign activity, assign array variable on left side and the query on right side

(From dtRow in dt.asenumerable
Group dtRow by k= dtRow("2.Flag").tostring.trim into grp= Group
Where cstr(grp(0)("2.Flag").equals("Y")
Select cstr(grp(0)("Match Number"))).toarray

And in write range give datatable as

(From dtRow on dt.asenumerable
Let mchNumber = dtRow("Match Number").tostring
Let flag = dtRow("2.Flag").tostring
Where not (arrStr.any(function (item) item.tostring.trim.equals(mchNumber)) or flag.equals("Y"))
Select dtRow).copytodatatable

Thanks

[balupad14 (Balamurugan (BalaReva))]

Hi @aaydin ,

Using BalaReva Easy Excel package, You can delete all the rows which are matched with T (“2.Flag”) is “Y”.

• Use Excel Application Scope from BalaReva Easy Excel. Select the file.
• Use Find an activity and specify the cell range as “T:T” and set the Find Text as “Y”.
• Now the activity returns the row index as an integer array.
• Conver the integer array to stirng array like this arr_strValue= arr_intValue.Select(Function(i) i.ToString()).ToArray()
• Use the Delete Rows activity and set the Row Range the string array variable “arr_strValue”

Ref :

UiPath Go ! BalaReva EasyExcel Activities

Hi, I have developed some activities to make easy excel automation. Package manage Search keyword: Balareva.easyexcel An activity library that makes Excel automation very easy This package contains activities that will help automate your work with Excel. The below activities are available: ExtractGraphImage - Extracts graphs available in a specific Excel and converts these into images. ExtractHyperLinks - Extracts hyperlinks from a specific cell range within a given Excel sheet. The e…

Uipath | excel delete rows | excel delete columns | excel copy as picture (BalaReva)

Uipath Excel Find | Search | Find and Replace (BalaReva)

Regards
Balamurugan.S

Regards
Balamurugan.S

[prasath_S]

Hi @aaydin have you tried the queries I gave?

[aaydin (Ahmet Aydin)]

@prasath_S

I never worked with queries

I receive following error.

[prasath_S]

@aaydin sorry After Y it should be two brackets.

Ex (“Y”))

Thanks

[aaydin (Ahmet Aydin)]

Now, there is a new error

[prasath_S]

(From dtRow in dt.asenumerable
Group dtRow by k= dtRow("2.Flag").tostring.trim into grp= Group
Where cstr(grp(0)("2.Flag")).equals("Y")
Select cstr(grp(0)("Match Number"))).toarray

Hope this will help, typing in phone that’s why some syntax errors.

Thanks

[aaydin (Ahmet Aydin)]

Where do I have to type the below second code?

(From dtRow on dt.asenumerable
Let mchNumber = dtRow(“Match Number”).tostring
Let flag = dtRow(“2.Flag”).tostring
Where not (arrStr.any(function (item) item.tostring.trim.equals(mchNumber)) or flag.equals(“Y”))
Select dtRow).copytodatatable

[prasath_S]

@aaydin Use the query in write range activity,in the property panel there is an property called datatable (type in that).

Thanks

[aaydin (Ahmet Aydin)]

Another error in the write range activity

[prasath_S]

Please try this,

(From dtRow in dt.asenumerable
Let mchNumber = dtRow("Match Number").tostring
Let flag = dtRow("2.Flag").tostring
Where not (arrStr.any(function (item) item.tostring.trim.equals(mchNumber)) or flag.equals("Y"))
Select dtRow).copytodatatable

Changed on with in in first line

make sure the arrStr is the array we have assigned earlier

Match Number and 2.Flag is the column names.

Thanks

[aaydin (Ahmet Aydin)]

@prasath_S It does not work correctly

There is just 1 problem in duplicate match numbers.
If the lines with the flag “N” are red before the lines “Y” then only lines with “Y” are deleted (see red lines).

[prasath_S]

@aaydin could you show the input you are using and output you got.

[aaydin (Ahmet Aydin)]

of course, thanks!

INPUT:

OUTPUT: