locked
Why OLEDB don't extract Date correctly? RRS feed

  • Question

  • User-1725652363 posted
    I'm using OLEDB to extract data from an Excel file: 
    string connexion_string = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + src + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";
    OleDbConnection connexion = new OleDbConnection(connexion_string);
    connexion.Open();
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Feuil1$]", connexion);

    This is the error in console:


    This is the stylesheet:


    and this is the line 71:
    nv_trame.Date_Emon = DateTime.FromOADate(Convert.ToDouble(reader[1].ToString())); 
    but some date values are missing, knowing that's not the case in Excel.
    Thank you.

    Wednesday, April 11, 2018 8:13 AM

All replies

  • User-1716253493 posted

    Why convert to double first?

    Try this

    nv_trame.Date_Emon = DateTime.FromOADate(reader[1]);

    Wednesday, April 11, 2018 8:58 AM
  • User-1725652363 posted

    Because FromOADate should have a double in his parameter.

    After this bug, I distinguished 3 cases:

    • Normal Date Format
    • Microsoft Timestump format => That's why I used FromOADate
    • And the last one I don"t know why it give me an empty value

    But In all those cases, Excel show all of them as a readeable human format

    Wednesday, April 11, 2018 9:09 AM
  • User753101303 posted

    Hi,

    IMO Check which value you have in reader[1] and which type is exposed. For now you convert this value to a string and it fails when you then convert this string to a double.

    Wednesday, April 11, 2018 9:20 AM
  • User-1725652363 posted

    I haven't any value in reader[1] and his type is dbnull 

    Wednesday, April 11, 2018 9:26 AM
  • User753101303 posted

    Ok so you can't convert this value and would have to check for that.

    If it is unexpected, an issue is that by default the first few lines are scanned to infer a datatype for the column and if the type is not suitable for later values the driver has no other choice than to expose a DbNull value.  Try http://microsoft-ssis.blogspot.fr/2011/06/mixed-data-types-in-excel-column.html

    I'm not sure there is really a satisfactory solution about that. It's been a long time since then but the next time I'm doing an Excel file import I'll likely try an approach such as https://www.codeproject.com/Articles/770240/Importing-Excel-Data-to-a-Generic-List-Using-Open (ie using an Excel Library to read the file content) rather than going through a database driver.

    Wednesday, April 11, 2018 11:39 AM
  • User-1716253493 posted

    Maybe the issue is related to datetime format, It's MM/dd/yyyy format instead of dd/MM/yyyy format

    04/10/2015 => 04/10/2015 00:00:00 (correct value), I assume apr 10 2015
    30/06/2015 => "30/06/2015" (string)
    14/04/2015 => can't convert to date value

    Thursday, April 12, 2018 12:32 AM
  • User-1725652363 posted

    All dates have DD/MM/YYYY format

    Thursday, April 12, 2018 2:51 PM
  • User753101303 posted

    What if you open the Excel file and make sure date columns are using the default date format (or even a number format to see the actual underlying value for each date).

    Make 100% sure the source data is ok. If this particular cell comes as null it is necessarily some formating issue on this particular cell (this is the first one in this column that couldn't be a MM/DD/YYYY date ? could it be part of the problem ?)

    Thursday, April 12, 2018 3:21 PM
  • User-1716253493 posted

    To ensure, you can simply format the first correct value 04/10/2015 00:00:00 to MMMM dd yyyy

    Its april or october?

    Friday, April 13, 2018 2:25 AM
  • User-1725652363 posted

    Its April

    This is another test when I display the type and the content:

    Console.WriteLine(i + " Type:" + reader[1].GetType() + "=" + reader[2].ToString());

    Monday, April 16, 2018 10:25 AM
  • User-1725652363 posted

    Patric this is what I get: standard

    Monday, April 16, 2018 10:29 AM