Remove rows with specific words from datatable

Hi all,

I am facing difficulties during the use of Datatables. I have got a huge excel table with more than 80000 rows. I have to remove specific rows from that Excel. Every row which contains the word 2022 1Q and 2022 2Q and 2022 3Q and 2022 4Q should be removed from the Excel. I am first reading the range. Delete all rows in the Excel and afterwards tried to paste the filtered Data, so the datatable without the rows with the mentioned words again in the Excel. I already tried the for each row in datatabke activity in combination with the Remove data row activity, but this takes quite too long. I stopped the run after 10 minutes of loading.

Is there another possibilty maybe with assign acitivity and the Remove data row activity?

I would be happy for any help.

Hi @MaryspaceX

Can you share the sample input and output?

Regards
Sudharsan

Hello @MaryspaceX,

you could work with the “Filter Data Table” activity.

Edit:
-Your Input DataTable and Output DataTable can be the same so you just overwrite it.
-you can chain Operations with and/or so one condition or all conditions have to be met

Kind Regards

1 Like

Hi @Sudharsan_Ka ,

This is a sample input:

This should then be the output:
image

So everything containing 2022 1Q 2022 2Q 2022 3Q and 2022 4Q should be deleted.

Only this or u you need to remove all the integers like 2022 5Q, 2022 6Q… ?
@MaryspaceX

I created 2 variables. One for the current Year.
currentYear= System.DateTime.Now.Year.ToString

One variable for the current quarter in using the switch activity. e.g. from January to March using 1Q from April to June using 2Q.
image

Every other entry from last years should not be removed.

Try with this expression

You need to use a assign activity

DT_Result = the below expression

Dt1.AsEnumerable().Where(Function(s) Not "2022 1Q,2022 2Q,2022 3Q,2022 4Q".Contains(s(0).ToString)).CopyToDataTable

Dt_Result will have the data you wanted

image

Regards
Sudharsan

Hello @lukas.gamper ,

This is working fine, thank you.

I am just wondering is this kind of reliable solution one would choose for such a process scope or is this with VB expression in assigning variables more reliable?

Sorry for this question, but I want to make sure to choose a reliable option.

You can choose anything @MaryspaceX , both are effective

@MaryspaceX, both work, you can use variables in the Filter Table Activity too, either way is fine.

If you have other people working with the project as well, who don´t understand long expressions then I personally would prefer activities since these are easier to understand.

Kind Regards

1 Like

For some reason this is not working. Could it be that I have to include the column name in the VB expression?

I tried this, but this is also not working:
Dt1.AsEnumerable().Where(Function(row) row(“Fiscal Year”) Not “2022 1Q,2022 2Q,2022 3Q,2022 4Q”.Contains(s(0).ToString)).CopyToDataTable

Oh I think I got it. I had to replace the 0 with the respective column. Thank you @Sudharsan_Ka

Dt1.AsEnumerable().Where(Function(s) Not “2022 1Q,2022 2Q,2022 3Q,2022 4Q”.Contains(s(0).ToString)).CopyToDataTable

One last question:

How can I replace “2022 1Q,2022 2Q,2022 3Q,2022 4Q” with variables e.g fiscalYear + " " + “1Q”.

Do I have to surround it with brackets?

Hi @Sudharsan_Ka

One last question:

How can I replace “2022 1Q,2022 2Q,2022 3Q,2022 4Q” with variables e.g fiscal Year is a Varibale assigned before in the process: fiscalYear + " " + “1Q”, fiscalYear + " "+ “2Q”?

Yes @MaryspaceX

You can give like that

Regards
Sudharsan

@Sudharsan_Ka

I tried the following, but poorly it is not working. Do you have an idea?

dT1.AsEnumerable().Where(Function(s) Not fiscalYear +" “+ “1Q”,fiscalYear +” “+ “2Q”,fiscalYear +” “+ “3Q”,fiscalYear +” "+ “4Q” .Contains(s(“Fiscal Year”).ToString)).CopyToDataTable

dT1.AsEnumerable().Where(Function(s) Not (fiscalYear +" “+ “1Q”,fiscalYear +” “+ “2Q”,fiscalYear +” “+ “3Q”,fiscalYear +” "+ “4Q”) .Contains(s(“Fiscal Year”).ToString)).CopyToDataTable

OKay try this way @MaryspaceX

Pass all variables in the single variable and pass that in the expression

Not filtering or do you receive any error?

Regards
Sudharsan

@Sudharsan_Ka

Filtering works but wanted to learn more about the VB expression as this sound better to me. But guess I have to go back to filter option.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.