Excel cannot insert more than 255 characters?

Question

I use the OLE DB driver to insert more than 255 characters into an Excel worksheet, but I get an error:

Exception Details: System.Data.OleDb.OleDbException: The field is too small to accept the amount of data you tried to add. Try pasting or pasting less data.

This thread seems to be a limitation of Excel. Even the Microsoft site seems to say so here .

Does this mean that I cannot programmatically, but can I manually? Because I can enter more than 255 characters when I manually enter them in Excel. Is this a limitation of Microsoft.ACE.OLEDB.12.0 (AccessDatabaseEngine.exe) ?

Answer 1

The link you are referring to is about 256 columns, not characters. A 256 character issue is described here with a workaround: http://support.microsoft.com/kb/213841

Answer 2

I found a more efficient way to deal with this problem, my links are the two links below

http://support.microsoft.com/kb/316934

http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled

Basically, don't use a data adapter, dataset and options to insert rows in Excel (this never worked for me) instead, run SQL insert from code directly using the command object

Here is what you need to do

• Declare a wide field as MEMO instead of a string or CHAR "CREATE TABLE WorkSheetName (field1 INT, field2 MEMO, field3 ...);"
• Create an insert statement like "INSERT INTO WorkSheetName (field1, field2, ...) VALUES (1234," Any long line here ... ", ...)"

• Declare DbCommand to run the Create table and Insert views above, here I use the obj command from the corporate library

  DbCommand dbCommand = m_dbConnection.CreateCommand (); dbCommand.CommandText = "Create table ..."; dbCommand.CommandType = CommandType.Text; dbCommand.ExecuteNonQuery ();

  dbCommand.CommandText = "Insert Into ..."; dbCommand.CommandType = CommandType.Text; dbCommand.ExecuteNonQuery ();

Answer 3

My workaround for this problem is to add text that is longer than 255 characters in the first row of the column. OleDB will treat this column as a MEMO type, and then it can insert more than 255 characters into the cell.