Check all values of specific column to define the datatype in DGV

Question

I'm asking your help to know if exists a fast method to check if all the values in a specific column of a DataTable/Datagridview are DateTime or numbers.

I'm trying to make a generic method to put specific formats in any column in a DGV.

I have information from TEXT files / Excel or XML files without previously data type definition

Thanks!

For `DataTable`, check out the property `DataColumn.DataType`. For `DataGridView`, check out the property `DataGridViewColumn.ValueType`. — King King, Sep 12 '13 at 03:43
@SSS: I don't want create a loop to go for each row. I'm thinking in more effective method. — MiBol, Sep 12 '13 at 03:44
@KingKing: I have some examples when the ValueType is a String, but the values are Date or numbers. I want create a format for these columns. `DataGridViewColumn.DefaultCellStyle.Format = "E2" ' d, g, N, N2, C2 , etc` — MiBol, Sep 12 '13 at 03:47
@MiBol if your grid is `DataBound`, you should check on the `DataTable` instead, the result should be exact. — King King, Sep 12 '13 at 03:49
If you are talking about date-time or numerical data that has been serialised as text, then you will need to go through row-by-row, just because the first 99 rows are dates in a particular format, doesn't mean you won't hit gibberish in row 100. — SSS, Sep 19 '13 at 06:25

score 1 · Accepted Answer · answered Sep 12 '13 at 03:54

You can bury the loop in an extension method. The end result will need a loop somewhere, though, even if the loop is hidden inside Linq operations. For example, you could write this extension method:

public static void ApplyColumnFormatting(this System.Data.DataTable table, string column, Action formatDateTime, Action formatNumeric)
{
    bool foundNonDateTime = false;
    bool foundNonNumeric = false;

    DateTime dt;
    Double num;

    foreach (System.Data.DataRow row in table.Rows)
    {
        string val = row[column] as string;

        // Optionally skip this iteration if the value is not a string, depending on your needs.
        if (val == null)
            continue;

        // Check for non-DateTime, but only if we haven't already ruled it out
        if (!foundNonDateTime && !DateTime.TryParse(val, out dt))
            foundNonDateTime = true;

        // Check for non-Numeric, but only if we haven't already ruled it out
        if (!foundNonNumeric && !Double.TryParse(val, out num))
            foundNonNumeric = true;

        // Leave loop if we've already ruled out both types
        if (foundNonDateTime && foundNonNumeric)
            break;
    }

    if (!foundNonDateTime)
        formatDateTime();
    else if (!foundNonNumeric)
        formatNumeric();
}

Then you can call it like this:

System.Data.DataTable table = ...;

table.ApplyColumnFormatting("Column_Name",
    () => { /* Apply DateTime formatting here */ },
    () => { /* Apply Numeric formatting here */ }
);

This is fast in the sense that it does not check any more rows than necessary, and it does not continue checking a given type after it has been ruled out.

I figured out this idea... but I think that should exists a more efficient method. Thanks by the answer! — MiBol, Sep 12 '13 at 16:14
@MiBol Any more efficient methods would require some known constraints on the data. For example, if you knew that the data type would always stay consistent, then you could just check the first several rows. This is what the data type prediction does, for example, when you import a CSV file into Excel. — boynoid, Sep 13 '13 at 23:17

Check all values of specific column to define the datatype in DGV

1 Answers1