none
Microsoft.Jet.OLEDB.4.0 rounding excel data? RRS feed

  • Question

  • Hi,

     

    I am using the jet 4 OLE DB reader to read an excel file (using c#) that a client has sent to me.  The data is loaded into a dataset but I have noticed that the data is rounded down to zero decimal places.  The actual data in Excel has 1 decimal place, but the sheet is formatted to show no decimal places.  When the sheet is formatted to show 1 decimal place, it comes through with 1 decimal place into the dataset.

     

    I cannot seem to figure this out - the full value must be stored but jet 4 doesn't seem to extract it.  One thing that solved the problem was having the Excel file open whilst my code read the data...but this seems a bit of a work around and not something that I would like to suggest to my clients that they should do.

     

    My connection details are:

     

    dbConn=new OleDbConnection();

    string dbFilepath=filepath + filename;

    string connection ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFilepath + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";

    dbConn.ConnectionString=connection;

    dbConn.Open();

     

    Thanks in advance for any help,


    Ashley

    Wednesday, August 15, 2007 1:51 PM

All replies

  • Dear Ashley!

     

    You need iterate thorugh the Excel file. because i haven't seen any other solution for this.

     

    Regards,

    Wasif Ahmad

     

    Wednesday, August 15, 2007 8:28 PM
  • Results with Jet could be inconsistent in some cases. Sometimes it caused by using IMEX=1, which uses formatting information from the Excel file to return string for all non-string types. If you need to get exact value, you would need to use Visual Studio Tools for Office or third party component. I did my own component just because I was not able to handle all those situations.

     

    Thursday, August 16, 2007 10:32 AM
    Moderator
  • thanks for the replies, I have found an excel reader on www.codeproject.com that does not use jet 4 and this works fine.

    Friday, August 17, 2007 7:29 AM