Foreach Column?

I want to run a loop through column… Like foreach Column. How can I do that? Once I do that, I want to find the index of the column which meets my condition.

For example: I have an excel with 20 rows. In those rows, we have same column name (multiple, which I don’t need). I want to get three column - One being “Status”. So what I am planning to do is, run a loop through the column and then when it finds the string “:Status” then I want to capture that index. So I can use the same foreach row (with that index).

The reason why I am asking is, we would have lot of different excel with same column name. Wanted to get way to do this!

Do you mean this way?

foreach (DataRow dtRow in dtTable.Rows)
    {
        // On all tables' columns
        foreach(DataColumn dc in dtTable.Columns)
        {
          **var field1 = dtRow[dc].ToString();**
        }
    }
1 Like

Hey, Sorry didn’t get this! Can you please explain a bit?

First foreach is you are looping through all the rows.

foreach (DataRow dtRow in dtTable.Rows)

2nd foreach is inside 1st foreach is you are looping through all the columns of that specific row.

foreach(DataColumn dc in dtTable.Columns)

Instead of index you can use if condtion to check column name if dc =“status” and perform your logic

var field1 = dtRow[dc].ToString();

To get the Index value which you need, use below

int index = dtRow .Table.Columns[“status”].Ordinal;

7 Likes

what were the variable type for rows and col? I think this is a great idea :slight_smile:

Pls check the attached code.

rowcol.zip (7.8 KB)

1 Like

This what I am trying to do : -

All I want to do is check the row with heading - status - and if I found A and then get value of Go/No-Go.

Because we have same column name, read range with heading gives me an error.

Question : Why I don’t use row(0)?? Because I have one more excel which is like this : -

And the chances are the next excel might be

Is the column count standard?

Can you make a copy of the file and alter the column names to rename duplicates (if only few)?

and then do the above process with new file?

renaming duplicate column values in excel

foreach.xaml (19.3 KB)

3 Likes

Hey @ vvaidyaMVP …My requirement is that I have to open a web url from an excel file,but the row or column is not fixed …it can be in any column eg. A,B,C,D or so on .So how solve this any ,idea? Also I have used for-each column but unable to fetch for the data in each column…so If you could help me in this would be a great help …
Thank you.

Regard,
Siddhant.

Hello Sir:

I am searching a relative topic and find your answer over here,
your workflow is very useful to me but I just have a quick question
In the “If” activity the condition is col.Tostring= “status”
But what about I want to use the specific date instead of the “status”?
For example the tile of the column is “8/1/2020” in stead of the status
And because I only want to read the value for current date, so I assign the a variable currentdate=DateTime.now.ToString(“M/01/yyyy”)
But for the “If” activity, after I change the “status” to the variable_currentdate, then it never recognize(never return me the value),
Could you please teach me how to let this robot to give the value when the title of the column changed from “status” to a variable, currentdate?