Deleting matches in a huge Excel file

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!

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

Hi @prasath_S

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

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

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.

@aaydin

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

Hope this may help you

Thanks

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

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 :

Regards
Balamurugan.S

Regards
Balamurugan.S

Hi @aaydin have you tried the queries I gave?

@prasath_S

I never worked with queries :thinking:

I receive following error.

@aaydin sorry After Y it should be two brackets.

Ex (“Y”))

Thanks

image

Now, there is a new error :slight_smile:

(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

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

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

Thanks

Another error in the write range activity :slightly_smiling_face:

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

@prasath_S It does not work correctly :frowning:

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).

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

of course, thanks!

INPUT:

OUTPUT: