Excel Datatable manipulation

Hi,
Below is the format of my excel sheet.

Conditions:

  1. If the BOT is running in the month of April(for eg), it has to consider values in (-2,1), i.e,Feb-19, Mar-19, May-19.
  2. Wherever the BOT finds Y, it has to iterate for all values present only in that line item and perform certain steps…

Any suggestions will be helpful

@Faraz_Subhani

Could you please explain condition 1 to help you.

For step 2 you can iterate the datatable and using If Condition (row(“Feb19”).tostring.contain(“Y”)) If exist u can give the step inside.
Else It will remains same.

Thanks,
Suresh J

Hi @Faraz_Subhani

Datatable.Select("["+Now.ToString("%MMM-yy")+"=‘Y’").CopyToDatatable()

Thanks
Ashwin S

Hi,
1st condition states that, if my BOT is running in the month of April, it has to check for ‘Y’ only for 2 months prior to April(Feb, March) and 1 month after the the month in which bot is running(May).
How Can I dynamically get preceding and succeeding month names.

I think you are correct, I will need to filter the data first, so if BOT is running for April month, then it needs to check ‘Y’ only for 2 months prior feb and march and 1 month after, i.e May.
I am getting error for this.
image

My main concern here is to get the preceding and succeeding month names dynamically, if BOT runs in month of April, I will have to get the index of column name matching with the current month then may be prior 2 months will be(index-1) and row(index-2) and succeeding month column name will be row(index+1)

@AshwinS2 provided a viable solution with a SELECT statement earlier. Note that there was only one column provided, just expand the concept for all 3 columns.

I would recommend using a DataView to achieve your goal. This can be done as follows:

Assign CurrentDate (variable type = datetime) = now
Assign dt1View (variable type = DataView) = new DataView(dt1)
Assign dt2 (variable type = DataTable) = dt1View.ToTable(“dt2”,False,{CurrentDate.AddMonths(-2).ToString(“MMM-yy”),CurrentDate.AddMonths(-1).ToString(“MMM-yy”),CurrentDate.AddMonths(1).ToString"MMM-yy")})

Now you will have a new datatable (called dt2 above) that contains only the columns you are looking for.

@Dave Dataview did the magic, don’t have words to thank you all.
Only Thing is that,how can I include the first 2 columns as well, i.e ‘Item’,‘NIC’ while writing d2 data table in sheet?

Add those columns within the {curly braces} portion of the last assign. The order you write them in the curly braces is the way they show up in your datatable as well. So change it to:

Assign dt2 (variable type = DataTable) = dt1View.ToTable(“dt2”,False,{"Item","NIC",CurrentDate.AddMonths(-2).ToString(“MMM-yy”),CurrentDate.AddMonths(-1).ToString(“MMM-yy”),CurrentDate.AddMonths(1).ToString"MMM-yy")})

EDIT: So you can see the documentation of how this works, you are using the DataView.ToTable() method as documented here: https://docs.microsoft.com/en-us/dotnet/api/system.data.dataview.totable Specifically, you are using the ToTable(String, Boolean, String[]) syntax

1 Like

Thank you very much

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