Update the data table based on column value

Help Studio
, ,
1

i have an excel file, Mrkt Comments Column have some string like 06/11/2024_Claim denied for invalid DX code so claim escalated to GEI team for further assistance Claim #66116598…SS
i already extracted date from the string and apply a condition on the date because i need only that date from the string which lie in between last 30 to 45 days old only from current date and i find that dates in my op variable like if date is 06/07/2024 then op= 06/07/2024 and if date 05/04/2024 then op= blank, now i want a linq code if op value have match with Mrkt Comments column value then keep the whole row and if op= blank then delete the whole row from data table
Thanks in advance

2

@Arvind1

dt = dt.AsEnumerable()
       .Where(Function(row) Not String.IsNullOrEmpty(op) AndAlso row("Mrkt Comments").ToString.Contains(op))
       .CopyToDataTable()
3

i apply this linq code there but this is not work,

If(String.IsNullOrEmpty(op), dt3.Clone(), (From r In dt3.AsEnumerable() Where r.Field(Of String)(“Mrkt Comments”).Contains(op) Select r).CopyToDataTable())

4

Hi @Arvind1

Could you share the input and expected output file for our better understanding.

If you don’t have the Confidential data then make the data dummy and share it with us.

1 Like
5

@Arvind1

Is my above linq query works for you,if not please share the input sample file and expected output also

6

get the error –
Assign: The source contains no DataRows.

7

@Arvind1

Please Share the Input sample and expected Output.

8

Might be Your datatable doesn’t contain any data that’s why it was throwing error… @Arvind1

Check the datatable contains any data or not

9

Book1.xlsx (9.4 KB)
this is my excel
check

10

Book1.xlsx (9.4 KB)
This is input file

11

Book1.xlsx (10.3 KB)
expected output in sheet2
based on mrkt comments if date 30-45 older than current date then keep the whole row otherwise delete the whole row

Thanks

12

Hi @rlgandu
Book1.xlsx (10.3 KB)
expected output in sheet2
based on mrkt comments if date 30-45 older than current date then keep the whole row otherwise delete the whole row

Thanks

13

@Arvind1
Sequence1.zip (2.0 KB)

image
image1163×789 48.2 KB

Output Sheet2:
Book1 (1).xlsx (9.9 KB)

14

Okay @Arvind1

You can use the below LINQ Expression,
→ Use the Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Then create a datatable datatype variable called Output_dt and take an assign activity to write the below LINQ Expression,

- Assign -> Output_dt = (From row In Input_dt 
                         Let DateVar = DateTime.ParseExact(System.Text.RegularExpressions.Regex.Match(row("Mrkt Comments").ToString(), "\d+\/\d+\/\d{4}").Value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) 
                         Where DateVar >= DateTime.Now.AddDays(-45) AndAlso DateVar <= DateTime.Now 
                         Select row 
                            	).CopyToDataTable()

→ Then use the write range workbook activity to write the Output_dt to the excel.

Check the below workflow for better understanding,
Sequence5.xaml (13.6 KB)

Sequence5
Sequence5804×576 38 KB

Check the below excel file, the output is in Sheet3,
Book1 (1).xlsx (11.0 KB)

Hope it helps!!

15

getting the error- Write Range Workbook: Object reference not set to an instance of an object.

17

error in write range workbook

18

@Arvind1

Can you try my approach

19

Might be the Output_dt not contain any data with init… @Arvind1

Can you debug the flow and see the Output_dt output in immediate panel.

20

Hi lakshmi,
yes, try , but receive the errr like–Invoke Code: Exception has been thrown by the target of an invocation. what is this

21

yes, you are right, output dt not contain any data, but there are so many data based on the date condition