Return line and column Index of a value that exists in dataTable

Question

This solution provided in this link was really helpful:

DataColumn[] columns = tbl.Columns.Cast<DataColumn>().ToArray();
bool anyFieldContainsPepsi = tbl.AsEnumerable()
    .Any(row => columns.Any(col => row[col].ToString() == "PEPSI"));

But I need to return, instead of bool, the index of the "found" value. Should I try this idea? Note that I do need the Line Number/Index and the Column Number/Index.

My code at this point:

    bool find = csvDataOnlyHeader.Rows.Cast<DataRow>().Any(r => r.ItemArray.Any(c => c.ToString().Contains("SN:")));

    DataColumn[] columns = csvDataOnlyHeader.Columns.Cast<DataColumn>().ToArray();
    bool anyFieldContains = csvDataOnlyHeader.AsEnumerable()
        .Any(row => columns.Any(col => row[col].ToString() == "SN:"));

What do I need:

Search the whole DataTable for a specific string (not caring about columns name or position).
Return the Index (or Indexes) of the places that contains this string. (I.e. line 2 column 4)

StriplingWarrior · Answer 1 · 2017-07-31T15:40:59.393

1

There's a variant of Select that allows you to capture an index.

var matchingIndexes = csvDataOnlyHeader.Rows.Cast<DataRow>()
    .Select((r, index) => new 
        {
            isMatch = r.ItemArray.Any(c => c.ToString().Contains("SN:")),
            index
        })
    .Where(e => e.isMatch)
    .Select(e => e.index);
var firstMatch = matchingIndexes.FirstOrDefault();

To avoid ambiguity between firstMatch having a 0 (meaning the first match is the zeroth element of the array) and 0 (meaning there were no matching indexes), you could cast the results as nullable ints:

...
.Select(e => (int?)e.index)
...

Or you can use my CallMeMaybe library to get a Maybe<int>:

var firstMatch = matchingIndexes.FirstMaybe();

edited Jul 31 '17 at 15:40

answered Jul 31 '17 at 15:16

StriplingWarrior

151,543
27
246
315

Hey, if I try like this, var firstMatch is returning "0". However, both "bool checks" are returning "true". In my case, "bool find" and "bool anyFieldContains". Both return true as if they found the "SN:" value. – FFLS Jul 31 '17 at 15:25
If the first bool check returns true, then matchingIndexes should contain `[0, 1]`, right? `matchingIndexes.FirstOrDefault()` would return `0`, which is correct: zero is the first index of an item that matches in your datatable. – StriplingWarrior Jul 31 '17 at 15:29
@FabioSoares: I added to my answer, to show how you can avoid ambiguity using nullables. – StriplingWarrior Jul 31 '17 at 15:35
The matchingIndexes is returning "[0] = 0" -> (new System.Linq.SystemCore_EnumerableDebugView(matchingIndexes)).Items[0]. On the other hand, the bool find it returning "true". For both I am looking for "SN:" – FFLS Jul 31 '17 at 15:46
If you want to check the steps, I put some screenshots: https://drive.google.com/drive/folders/0B98UpTa2n4XbeHZtOHU2cVotUms?usp=sharing – FFLS Jul 31 '17 at 16:02
@FabioSoares: It looks like your data table's first row contains row headers, and one of the row headers has "SN:" in it, so the first row (at index 0) matches your criteria. You originally just said you wanted the index (which I took to mean the row index). Since you clarified that you need the column index as well, this answer is probably not sufficient for what you're looking for. – StriplingWarrior Jul 31 '17 at 19:50

Eugene Komisarenko · Accepted Answer · 2017-07-31T17:11:30.907

1

You should be able to get the row index using the IndexOf method of the DataRowCollection type or like @StriplingWarrior pointed out the index parameter of the lambda expression if the index of the row in the original enumerable is reliable enough for you.

var columns = tbl.Columns.Cast<DataColumn>().ToList();
var enumerableRowCollection = tbl.AsEnumerable();

var results = enumerableRowCollection
    .Select((row, index) =>
    {
        var column = columns.FirstOrDefault(col => row[col].ToString() == "PEPSI");

        return new
        {
            Column = column,
            ColumnIndex = column != null ? columns.IndexOf(column) : -1,
            Row = row,
            RowIndex = index
        };
    })
    .Where(x => x.Column != null)
    .ToList();

for (var i = 0; i < results.Count(); i++)
{
    var result = results[i];
    Console.WriteLine($"Result {i}");
    Console.WriteLine($"RowIndex: {result.RowIndex}");
    Console.WriteLine($"ColumnIndex: {result.ColumnIndex}");
}

edited Jul 31 '17 at 17:11

answered Jul 31 '17 at 15:22

Eugene Komisarenko

1,533
11
25

This is working well. Is there any way of getting also the column Index? In my case, I need the exact position. For example Row 3 Position 4 (Position = Column) – FFLS Jul 31 '17 at 16:13
Added expanded version of the example to retrieve column index. – Eugene Komisarenko Jul 31 '17 at 16:33
Thank you for the update. One last thing, in the "ColumnIndex = columns.IndexOf(x.Column)," the "Indexof" part is presenting the error "No overload for method 'IndexOf' takes 1 arguments". – FFLS Jul 31 '17 at 16:51
Did two updates [1] RowIndex is now coming from the first projection and [2] changed the very first line to convert to list. And [3] refactored into single projection call to combine all the data in the result. – Eugene Komisarenko Jul 31 '17 at 16:52
Hey, still... the "ColumnIndex = columns.IndexOf(column)," presents the error "No overload for method 'IndexOf' takes 1 arguments". Now, I also have another error in the part "DataColumn[] columns = csvDataOnlyHeader.Columns.Cast().ToList();" - Cannot implicity convert type 'System.Collections.Generic.List' to 'System.Data.DataColumn[]' – FFLS Jul 31 '17 at 17:01
It should be good now, sorry about the bug. `IndexOf` is only available in the `List` generic type. One more fix to check for the null of the column inside the expression body. – Eugene Komisarenko Jul 31 '17 at 17:02
Awesome! It works really well. Thank you for helping and the updates. – FFLS Jul 31 '17 at 17:26

Return line and column Index of a value that exists in dataTable

2 Answers2