By Joseph


2010-12-20 09:35:13 8 Comments

I am using OLE DB driver to insert more than 255 characters into an Excel sheet, but I get the error:

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

Seems from this thread that it's an Excel limitation. Even the Microsoft site seems to say so here.

So does this mean I can't programmatically, but I can manually? Because I can enter more than 255 characters when I manually type them in Excel. So is it a Microsoft.ACE.OLEDB.12.0(AccessDatabaseEngine.exe) driver limitation?

3 comments

@kingkong0924 2014-06-25 02:38:01

My workaround to this issue is to add a text which its length is longer than 255 characters on the first row of the column. OleDB will treat this column as MEMO type and then it can insert more than 255 characters in a cell.

@nelucon 2016-03-30 21:01:30

Thank you so much! You are awesome! It works like a charm, I've just hided the second line and it looks great!

@Waleed 2012-07-06 14:27:06

I found a more efficient way to handle this problem, my references are the 2 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 do not use data adapter, data set and parameters to insert rows in the Excel (this never worked for me) instead run SQL insert from the code directly using command object

Here what you have to do

  1. Declare the wide field as MEMO instead of string or CHAR "CREATE TABLE WorkSheetName ( field1 INT, field2 MEMO, field3 ... );"
  2. Build the insert statement like "INSERT INTO WorkSheetName (field1, field2, ...) VALUES (1234, 'Any long string here...', ...)"
  3. Declare a DbCommand to run the Create table and Insert statements above, here I am using the command obj from enterprise 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();

@Slogmeister Extraordinaire 2018-11-14 18:34:46

This did not work for me. Fortunately, the CREATE TABLE statement creates a named range of the same name (only without the $). If I INSERT INTO the range, then it works.

@jvdbogae 2010-12-20 10:59:21

The link you are refering to, is about 256 columns and not characters. The 256 characters problem is described here with a workaround: http://support.microsoft.com/kb/213841

@Joseph 2010-12-22 04:53:09

Nope - read carefully. On the MS site, see 3rd row. It says: Column width: 255 characters Btw, thank you for the workaround!

@ndemou 2018-06-10 13:26:58

Link only answers are strongly discouraged in SO -- for good reasons.

Related Questions

Sponsored Content

1 Answered Questions

SSIS Excel Import Columns with More or Less than 255 Characters

0 Answered Questions

SQL Server Export To Excel 2010 bigger 255 character

3 Answered Questions

2 Answered Questions

0 Answered Questions

2 Answered Questions

[SOLVED] Reading and working with strings longer than 255 with ADODB Excel 2010 VBA

1 Answered Questions

[SOLVED] data truncated to 255 characters using excel Microsoft.ACE.OLEDB.12.0

  • 2014-02-14 21:15:42
  • Priya
  • 938 View
  • -1 Score
  • 1 Answer
  • Tags:   .net excel

1 Answered Questions

How to insert a cloumn with more than 255 characters to excel in SSIS

  • 2013-12-23 15:12:48
  • Venkatesh Karamala
  • 3662 View
  • 0 Score
  • 1 Answer
  • Tags:   excel ssis dataflow

Sponsored Content