CODEWITHSUNDEEP
c#c#-4.0timedatagridviewdatatable
Cocoa Dev
Cocoa Dev

Reputation: 9541

DataGridView displays the Time as a date

I have a CSV file which has a column for time and the time is formatted to look like (in the CSV file) 08:22:07

DataGridView will convert it to look like

12/30/1899 8:22 AM

Any suggestions?

My code looks like 

    public static DataTable ParseCSV(string path, String pattern)
    {
        if (!File.Exists(path))
            return null;
        string full = Path.GetFullPath(path);
        string file = Path.GetFileName(full);
        string dir = Path.GetDirectoryName(full);
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Data Source=\"" + dir + "\\\";"
            + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";
        string query = "SELECT [Pc-Tag], [User-Name], [Date], [Time] FROM " + file;// +" WHERE [User-Name] LIKE " + pattern;
        DataTable dTable = new DataTable();
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
        try
        {
            dAdapter.Fill(dTable);
        }
        catch (InvalidOperationException ioe)
        {
            Console.WriteLine(ioe.Message.ToString());
        }
        dAdapter.Dispose();
        return dTable;
    }

Upvotes: 0

Views: 642

Answers (2)

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31723

How many rows do you expect? For few (<1000) rows I would do:

....
dAdapter.Dispose();

dtable.Columns.Add("Custom", typeof(string));

foreach(var row in dtable.Rows)
{
     row["Custom"] = "17:15:30"; //here goes your logic to convert the Time Value
                                 // example: row["Time"].ToString("T");
}

return dTable;

Another solution with better performance is to add an expression column;

http://msdn.microsoft.com/de-de/library/system.data.datacolumn.expression%28v=vs.80%29.aspx

var expression = "SUBSTRING([Time], 11, IF(LEN([Time]) = 19, 8, 7))";
dtable.Columns.Add("Custom", typeof(string), expression);

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You could configure the column to use a certain format string like T, which is the long time format (hh:mm:ss).

Use the DefaultCellStyle.Format property of the column containing your time value to do that. You can either use the Forms designer (click the little arrow that's displayed when you select the DataGridView control and then choose "Edit columns", select the column and then press the "..." button for the DefaultCellStyle property) or you can do it in code.

Just make sure to add the columns manually (either in the designer or in code) and set the AutoGenerateColumns property to false.

Upvotes: 2

Related Questions