none
ACE Driver, OleDbDataAdapter, Numeric Fields Exported as Text RRS feed

  • Question

  • We've been exporting data to Excel 2003 with Jet for many years now and are now need to do the same with Excel 2007. We switched to the ACE driver and everything works fine except numeric fields are exported as text. Note this only happens when using the OleDbDataAdapter to export the data. If you use a standalone OleDbCommand, no problems. Sample code below. There are duplicate constants at the top for 2003 or 2007 so you can compare results. You will need either 2007 or the compatibility pack to run this.

    /*
      private const string DataSource = @"Test.xls";
      private const string ExcelOleDbConnectionString =
       "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES'";
    */
      private const string DataSource = @"Test.xlsx";
      private const string ExcelOleDbConnectionString =
       "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 XML;HDR=YES'";
      private const string TargetTableCreateSql = "CREATE TABLE `Sheet1` (`Amount` Decimal (18,2))";
      private const string TargetDataInsertSql = "INSERT INTO Sheet1 ([Amount]) VALUES (@Amount)";
      private const decimal SampleDatum = 5.22M;
    
      static void Main(string[] args)
      {
       if (File.Exists(DataSource)) File.Delete(DataSource);
       var oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder(ExcelOleDbConnectionString)
                          {DataSource = DataSource};
       var oleDbConnection = new OleDbConnection(oleDbConnectionStringBuilder.ToString());
       oleDbConnection.Open();
       var oleDbCommand = new OleDbCommand(TargetTableCreateSql, oleDbConnection);
       oleDbCommand.ExecuteNonQuery();
       oleDbConnection.Close();
       
       var insertCommand = new OleDbCommand(TargetDataInsertSql, oleDbConnection);
       insertCommand.Parameters.Add(new OleDbParameter("@Amount", OleDbType.Decimal, 0,
                               ParameterDirection.Input, 18, 2, "Amount",
                               DataRowVersion.Current, false, null));
       var targetDataAdapter = new OleDbDataAdapter {InsertCommand = insertCommand};
       var targetDataTable = new DataTable();
       targetDataTable.Columns.Add(new DataColumn("Amount", typeof (Decimal)));
       var targetDataRow = targetDataTable.NewRow();
       targetDataRow[0] = SampleDatum;
       targetDataTable.Rows.Add(targetDataRow);
       targetDataAdapter.Update(targetDataTable);
      }
    
    

     

    Tuesday, June 28, 2011 12:32 AM

Answers

  • Burned one of my MSDN tech support incidents. This is a bug with the driver, but for some reason it's only triggered by closing the connection before setting up the OleDbParameter. Comment that out and it works correctly.  
    Friday, July 22, 2011 10:56 PM

All replies

  • I am not sure if Decimal datatype is recognized by ACE OLEDB provider, since file format itself does not support decimal with specific precision or number of decimal places. If I am not mistaken all the values in Excel files stored as doubles. Did you try to use Double datatype?
    Val Mazur (MVP)

     http://www.xporttools.net

    Tuesday, June 28, 2011 3:47 AM
    Moderator
  • I tried Double, same problem. Note that Decimal works fine with Jet/Excel2003. Why would they remove function in ACE? Also note that this is only with OleDbDataAdapter, not if you use just OleDbCommand. It appears that there is some particular interaction there. 

    Tuesday, June 28, 2011 3:47 PM
  • Hi,

    I tried to insert decimal value to Excel using OleDbCommand, but the result shows me that the numeric field is still treated as text; may I know how did you update Excel using OleDbCommand?

    By the way, here is my code:

            static void UseCommand()

            {

                var oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder(ExcelOleDbConnectionString) { DataSource = DataSource };

                var oleDbConnection = new OleDbConnection(oleDbConnectionStringBuilder.ToString());

     

                oleDbConnection.Open();

                OleDbCommand oleDbCommand = new OleDbCommand(TargetTableCreateSql, oleDbConnection);

                oleDbCommand.ExecuteNonQuery();

                oleDbConnection.Close();

     

                oleDbConnection.Open();

                OleDbCommand insertCommand = new OleDbCommand(TargetDataInsertSql, oleDbConnection);

                insertCommand.Parameters.Add(new OleDbParameter("@Amount", SampleDatum));

                insertCommand.ExecuteNonQuery();

                oleDbConnection.Close();

            }


    Eric Yang [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 30, 2011 3:12 AM
  • Don't use OleDbParameter. Alter the TargetDataInsertSql to use a literal value (e.g., INSERT INTO Sheet1 ([Amount]) VALUES (5.22)") instead of a parameter.

    So, based on your result, it looks like the problem is not OleDbDataAdapter, but OleDbParameter. Even though I explicitly set the type to Decimal or Double in the parameter, it does not honor it.

    Thursday, June 30, 2011 7:38 AM
  • It seems different Excel version gives different results: I'm using Excel 2010, even I modify my code to use a literal value,

     

            static void UseCommand()

            {

                var oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder(ExcelOleDbConnectionString) { DataSource = DataSource };

                var oleDbConnection = new OleDbConnection(oleDbConnectionStringBuilder.ToString());

     

                oleDbConnection.Open();

                OleDbCommand oleDbCommand = new OleDbCommand(TargetTableCreateSql, oleDbConnection);

                oleDbCommand.ExecuteNonQuery();

                oleDbConnection.Close();

     

                oleDbConnection.Open();

                OleDbCommand insertCommand = new OleDbCommand("INSERT INTO Sheet1 ([Amount]) VALUES (5.55)", oleDbConnection);

                //insertCommand.Parameters.Add(new OleDbParameter("@Amount", SampleDatum));

                insertCommand.ExecuteNonQuery();

                oleDbConnection.Close();

            }

    The numberic value 5.55 still be treated as text in Excel.


    Eric Yang [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 1, 2011 3:28 AM
  • Well, I can run that to ground, certainly. But the larger point is that the driver is not working correctly for some reason. What do you think the next steps should be? Thx.
    Friday, July 1, 2011 4:00 PM
  • You can submit this issue to Microsoft Connect feedback portal http://connect.microsoft.com, Microsoft engineers will evaluate them seriously, thanks.

     

    If this issue is urgent, please contact support at http://support.microsoft.com.


    Eric Yang [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 4, 2011 4:59 AM
  • Burned one of my MSDN tech support incidents. This is a bug with the driver, but for some reason it's only triggered by closing the connection before setting up the OleDbParameter. Comment that out and it works correctly.  
    Friday, July 22, 2011 10:56 PM