Excel Datatable manipulation

Below is the format of my excel sheet.


  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


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.

Suresh J

Hi @Faraz_Subhani


Ashwin S

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.

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.