Delete Matching Row in DataTable before export to Excel use C#

Hi Team,

I would like to match the row in excel and delete the matching word row[UserName] in DataTable.
i have done as below but have some error. May help to guide me how to delete the DataTable Row by read the excel and delete the row in Data Table?


My Target Data table to remove → Match the “User Name” row.

My Excel File
image
My Error on the finding UserName

image

More Error:


image

HI @balaraj.pubalan

Check your column name in the expression is with space

User Name whereas is datatable UserName

Regards
Sudharsan

Hi @Sudharsan_Ka i have removed the space and still i have the below error:
Does it pick the row in excel by .Select(“”) or is diffrent for C#?
image
image

I tries this but have this error…not sure where i missed.

Error
image

Hi,

I guess the following is what you want to achieve. Can you try this?

arrDelete = excDt.AsEnumerable().Select(r=>r["UserName"].ToString()).ToArray()

filteredDt = dtNTU.AsEnumerable().Where(r=>arrDelete.Contains(r["UserName"].ToString())).CopyToDataTable()

note: arrDelete is String array.

Regards,

Hi @Yoichi , I get below error :slight_smile:

image

I think we cant assign it before the Data Table have data?

Hi,

If there is possibility result has no row, the following will work

arrDelete = excDt.AsEnumerable().Select(r=>r["UserName"].ToString()).ToArray()

arrDr = dtNTU.AsEnumerable().Where(r=>arrDelete.Contains(r["UserName"].ToString())).ToArray()

If you want to delete row which is matched execDt, the following will work.

arrDr = dtNTU.AsEnumerable().Where(r=> !arrDelete.Contains(r["UserName"].ToString())).ToArray()

Then check arrDr.Any() using IF.

I think we cant assign it before the Data Table have data?

Please read datatable for excDt and dtNTU in advance

Regards,

1 Like

Hi @Yoichi , after i use IF condition, the export works but not filter the Username in exported excel file.
is the any() should make it as contain()? or need specific the column name in dtNTU data table? The any() will read all the Data Table data is it?

HI,

If possible, can you share your input and expected output as file? It’s no problem, if dummy data.

Regards,

Hi @Yoichi Here is the file.
Mainly i do get the word from outlook body and export in excel file. now the excel file is working fine. additionally i want to add the matching to delete before export to excel file. I am figuring out how can make the deletion on the Data table level after the data capture in dtNTU Data Table.
Export.zip (5.6 KB)

Hi,

It may be user name in NTUdt contains extra white space because pattern is (?<=User Name: )(.*)
So, can you try to use the following pattern for user name?

(?<=User Name: )(.*\S)

Regards,

1 Like

Hi @Yoichi , its still same where no filter happen. The RegEx detect in email outlook and export to dtNTU Data Table. Should we use “Like” method??

Hi,

Perhaps, we should check actual data in dtNTU and excDt, including white space.
Can you write dtNTU to excel file and compare excDt exactly?

Or if we need partial match, the following may work.

arrDr = dtNTU.AsEnumerable().Where(r=>!arrDelete.Any(s=>r["UserName"].ToString().Contains(s))).ToArray()

Regards,

This will work but i want try work in DataTable so the get the final result as output in excel.

Hi @Yoichi , its works after add the final line
dtNTU.AsEnumerable().Where(r=>!arrDelete.Any(s=>r[“UserName”].ToString().Contains(s))).ToArray()

Hi @Yoichi , also i need one more help. let say i have more sheet and more keyword to check. how i can add the [“UserName”],[Phone"] like that? or should i refer all in same sheet?

Do you want to check both column at once? How about the following? Please make arrDeleteForUserName and arrDeleteForPhone from each sheets for them.

   dtNTU.AsEnumerable().Where(r=>!arrDeleteForUserName.Any(s=>r["UserName"].ToString().Contains(s)) AndAlso !arrDeleteForPhone.Any(s=>r["Phone"].ToString().Contains(s))).ToArray()

Regards,

1 Like

Hi @Yoichi , i have error when use AndAlso
so i use
& instead??
dtNTU.AsEnumerable().Where(r=>!arrDeleteForUserName.Any(s=>r[“UserName”].ToString().Contains(s)) & !arrDeleteForPhone.Any(s=>r[“Phone”].ToString().Contains(s))).ToArray()

1 Like

Sorry, AndAlso is for VB.net. “&” or “&&” is correct as you already modified.

Regards,

1 Like