Adding New Rows to an existing Excel based on Date

[sjavits-cohan]

I’m trying to write rows from a sheet in the Project Notebook into an Existing file based on the date column. I want the bot to only add rows that have a date of today through minus 3 days from today. So, if going by today’s date (2/20), anything from 2/20, 2/19/, 2/18 or 2/17. Is there a way to do this. I tried using an If statement and the “Date” sheet in the Project Notebook but that doesn’t seem to work for me. Attached is the test file and a screenshot of the sheet I’m trying to write from (it’s the same as the test file with additional rows).
Test.xlsx (9.8 KB)

[Parvathy (PS Parvathy)]

Hi @sjavits-cohan

→ Read Range Workbook. (nable Preserve Format option in the properties panel of Read Range Workbook)
Outout-> dt_Input

Try this:

filteredRows = dt_Input.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("DATE").ToString(), "M/d/yyyy", System.GLobalization.CultureInfo.InvariantCulture) >= DateTime.Today.AddDays(-3)).CopyToDataTable()

Hope it helps!!

[sjavits-cohan]

I’m sorry, I’m a little confused, where would this go?

filteredRows = dt_Input.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("DATE").ToString(), "M/d/yyyy", System.GLobalization.CultureInfo.InvariantCulture) >= DateTime.Today.AddDays(-3)).CopyToDataTable()

[Parvathy (PS Parvathy)]

Hi @sjavits-cohan

You can follow the below steps:

Regards

[Jayesh_678 (Jayesh)]

Hi,

In assign activity
To :- filteredRows

Value :- dt_Input.AsEnumerable().Where(Function(row) DateTime.ParseExact(row(“DATE”).ToString(), “M/d/yyyy”, System.GLobalization.CultureInfo.InvariantCulture) >= DateTime.Today.AddDays(-3)).CopyToDataTable()

filtered rows variable type -datatable
dt_Input is the datatable of excel sheet.

Thanks

[sjavits-cohan]

@Jayesh_678

dt_Input.AsEnumerable().Where(Function(row) DateTime.ParseExact(row(“DATE”).ToString(), “M/d/yyyy”, System.GLobalization.CultureInfo.InvariantCulture) >= DateTime.Today.AddDays(-3)).CopyToDataTable()

I’m not sure what I did wrong, but I got the below error.

[Parvathy (PS Parvathy)]

Hi @sjavits-cohan

If possible could you share the workflow xaml file.

Regards

[Shiva_Nikhil]

@sjavits-cohan

can you try this once

dt.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("DATE").ToString,"M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture)>=DateTime.Today.AddDays(-3)).CopyToDataTable()

[sjavits-cohan]

@Shiva_Nikhil

dt.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("DATE").ToString,"M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture)>=DateTime.Today.AddDays(-3)).CopyToDataTable()

This seems

to have gotten rid of the initial error but then I get this error when I try to run the workflow.

[Parvathy (PS Parvathy)]

Hi @sjavits-cohan

Please try below code:

dt_Input.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("DATE").ToString(), "M/d/yyyy", System.GLobalization.CultureInfo.InvariantCulture) >= DateTime.Today.AddDays(-3)).CopyToDataTable()

If you get VisualBasic error please retype Double quotes.

Regards

[sjavits-cohan]

@Parvathy

dt_Input.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("DATE").ToString(), "M/d/yyyy", System.GLobalization.CultureInfo.InvariantCulture) >= DateTime.Today.AddDays(-3)).CopyToDataTable()

This worked thank you so much!!

[Parvathy (PS Parvathy)]

You’re welcome @sjavits-cohan

Happy Automation!!