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?
More Error:
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#?
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,
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,
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,
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,
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()
Sorry, AndAlso is for VB.net. “&” or “&&” is correct as you already modified.
Regards,