locked
Error writing excel sheet with fields over 255 characters in C# using Microsoft.ACE.OLEDB.12.0 RRS feed

  • Question

  • I am getting "The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data" error when trying to inert a row with 1 cell value more than 255 characters  into .xlsx file using the ACE.OLEDB.12.0 provider

    my connection string

    string CnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test.xlsx;Extended Properties='Excel 12.0 ;HDR=Yes;'";

    then i  changed  TypeGuessRows to 0 in my registry (HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\office\12.0\Engines\Excel) and added IMEX = 1 in Extended property in connection string.

    changed connection string

    string CnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test.xlsx;Extended Properties='Excel 12.0 ;HDR=Yes;IMEX=1'";

    then i am getting Operation must use an updateable query error.

    Code 

            

    public static void ExportRSPLToExcel()
        {
            string CnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TestFile.xlsx;Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
            System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(CnString);
            try
            {
                objConn.Open();
            }
            catch (Exception ex)
            {
            }
            string Columns = "Item Number,Item Description";

            string values =
            string.Format(" \"{0}\",\"{1}\"  ","1","xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 250 0123456789 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx299");

            string SQL = string.Format("insert into ExportRSPL_LineItem({0}) Values ({1}) ", Columns, values);
            System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = SQL;
            objCmd.ExecuteNonQuery();

            objConn.Close();
        }

    Environment: Windows 8.1 OS 64 bit,  office 2013 64 bit and visual studio 2008

    Can anyone offer any advice?

     

    Thanks 

    Anil Reddy Allam



    Thursday, June 11, 2015 11:54 AM

All replies

  • Hello,

    >>then i am getting Operation must use an updateable query error.

    Somehow, it seems the “Operation must use an updateable query error” is caused by the IMEX=1, try to remove it and run your application again, I made a test with your code and the similar environment, it could work fine and successfully insert the long string to the excel:

    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel20150612.xlsx;Extended Properties='Excel 12.0;HDR=Yes;'");
    
                    try
    
                    {
    
                        conn.Open();
    
    
                        //OleDbCommand cmd = new OleDbCommand("select * from [Order$]", conn);
    
    
                        //OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    
    
                        //DataSet ds = new DataSet();
    
    
                        //da.Fill(ds);
    
    
                        string Columns = "OrderID,OrderName";
    
    
                        string values = string.Format("{0},\"{1}\"", "1", "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 250 0123456789 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx299");
    
    
                        string SQL = string.Format("insert into [Order$] ({0}) Values ({1}) ", Columns, values);
    
    
                        System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand(SQL,conn);
    
    
                        objCmd.ExecuteNonQuery();
    
    
                    }
    
                    catch (Exception)
    
                    {
    
    
                    }
    
                    finally
    
                    {
    
                        conn.Close();
    
                    }
    

    The result:

    Both the columns are “General” type.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 12, 2015 6:11 AM
  • You should be using Command Parameters instead of directly inserting the values into your SQL string. This may allow you to specify a data type for the parameter that can handle more than 255 characters and practically eliminates the possibility of a special character in text causing the SQL string to become invalid.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 12, 2015 1:36 PM

  • Thank you for your reply Fred. I apologize for not posting  exact code. 

    In above code string length is less than 255 characters, please find my original code below  

    public static void ExportRSPLToExcel()
        {
            string CnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TestFile.xlsx;Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
            System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(CnString);
            try
            {
                objConn.Open();
            }
            catch (Exception ex)
            {
            }
            string Columns = "Item Number,Item Description";

            string values =
            string.Format(" \"{0}\",\"{1}\"  ","1","Dfgfdgfdgfdgfdgdfgfdghdhdyjyiyui67i7i76oifghfhfgthfdsatjfgkryhtrur6tu6trut6uyt7i67i76yjygjygtjtykytkytkyukyxxxxxxxxxxxxxxxfxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 250 0123456789 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx299");

            string SQL = string.Format("insert into ExportRSPL_LineItem({0}) Values ({1}) ", Columns, values);
            System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = SQL;
            objCmd.ExecuteNonQuery();

            objConn.Close();
        }

    Friday, June 12, 2015 5:34 PM
  • Hello anil reddy allam,

    With your new provided string, I tested it and got the your showed result. The filed is too small. 255 is the column limit for provider i guess since the long string could be placed in Excel filed. For a long string, please try with the Excel.Interop.dll.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Tuesday, June 16, 2015 9:40 AM
    Tuesday, June 16, 2015 9:06 AM