OleDBConnection with IMEX 1 ignores AM / PM in time value

Question

I read the xls file using this code:

private static DataSet GetDataSetFromExcelFilePath(string filePath)
{
    try
    {

        //Microsoft.Jet.OLEDB.4.0
        using (OleDbConnection oleDbConnection = new
        OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=\"Excel 8.0;Persist Security Info=False;HDR=No;IMEX=1;\"", filePath)))
        //OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=\"Excel 8.0;Persist Security Info=False;HDR=No;IMEX=1\"", filePath)))
        //  using (OleDbConnection oleDbConnection = new
        //     OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=\"Excel 8.0;Persist Security Info=False;HDR=No;IMEX=1\"", filePath)))
        {
            oleDbConnection.Open();

            DataTable schema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string sheetName = schema.Rows[0].Field<string>("TABLE_NAME");

            var adapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), oleDbConnection);
            var dataSet = new DataSet();
            adapter.Fill(dataSet, Path.GetFileName(filePath));

            return dataSet;
        }
    }
    catch (Exception ex)
    {       

        return null;
    }
}

Notice I have IMEX 1 set in the connection string so it reads all data as string yet I have a strange problem on one my client's machine where the AM / PM or full HH is completely ignored for time. Here is how the dataset is filled with the above code: https://gyazo.com/f45c29c42b5d1339ae1fe159be4caf76 where there is no distinction between AM and PM.

and here is how the actual data is in the excel: https://gyazo.com/caf91c165eb08e3110fd2c3b7d4b8c51

Please note it is not possible to change the excel format or structure. Preferably any changes possible has to be done in the code.

For references, if anybody wants to download the excel file to test here it is: https://www.dropbox.com/s/7824xh3ihlym9v9/test.xls?dl=0

One more thing the issue can be replicated when you set the Window's language to Norway as shown here: https://gyazo.com/d03b3056ed81e177076471a74058fdb7

Answer 1

The problem with ODBC is that it is notoriously buggy. It is hard to give correct advice when you do not have exact settings/office version/registry etc.

The IMEX=1 setting you have used sets only the "mode" which is an import mode. Then you have a registry setting ImportMixedTypes where you can specify two values: Text or Majority Type. The default setting is Text and that is the reason you are getting everything as text. For more details you can read my answer here.

Your issue of time is more complex than only converting it to text as you are trying. The excel itself has an internal representation of the time which you are then converting to text. That is the reason why you are missing the PM/AM part. The simpliest way out would be to use the 24-hour format, which you probably know and don't want.

Is there way out? Yes. You need to specify the column directly when you are connecting to the excel file:

...
var timeColumn = 
  new OleDbDataAdapter("SELECT FORMAT([Time], 'hh:mm tt') as [Time] FROM [sheetName]", oleDbConnection);
...

To see more options for the FORMAT see the MSDN.