Importing CSV handling dates

Question

I am using the following code to import a CSV file.

       try
        {
            OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties=Text;");
            OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM " + fileName, ExcelConnection);

            OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);

            ExcelConnection.Open();

            DataSet ExcelDataSet = new DataSet();
            ExcelAdapter.Fill(ExcelDataSet);

            ExcelConnection.Close();
            return ExcelDataSet;
        }
        catch
            (Exception ex)
        {
            MessageBox.Show("Cannot read excel file");
            return null;
        }

Here when a column has a value as

19.10.2011 10:08:56 GMT

it turns as

19.102

Can anyone help me ?

Regards,

Answer 1

The JET provider can use a schema.ini file in the directory containing your CSV files.

For example:

[mycsv_file.csv]
Format=CSVDelimited
Col1=myname Text 
Col2=mydate Date
Col3=mynumber Integer  
DateTimeFormat=dd.mm.yyyy hh.nn.ss

This allows you to specify names and types for your columns. So far, I haven't been able to find a specifier for the timezone field, so the above won't work for your specific format. One thing you can do is to parse the date as a string, by setting the format to Text, and then mess around with it in C#.