You are currently offline, waiting for your internet to reconnect

The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.


This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.
Novice: Requires knowledge of the user interface on single-user computers.
SYMPTOMS
When you link to a Microsoft Office Excel spreadsheet from a Microsoft Office Access database, and the columns of the Excel spreadsheet contain more than 255 characters, you may notice that the data in the linked table appears truncated after the 255th character.
CAUSE
In Access, when you link to an Excel spreadsheet that contains more than 255 characters, the column of the Excel spreadsheet is mapped to the formatted Memo data type. Because Access treats the formatted Memo field as a text field that has a 255-character limit, you can view only 255 characters. However, the data is not physically truncated in the linked table.
WORKAROUND
To work around this problem, you must import the Excel spreadsheet to an Access table when the columns of the Excel spreadsheet contain more than 255 characters.
STATUS
This behavior is by design.
MORE INFORMATION
In Access, you can set the Format property of the Text field and the Memo field to create custom formats. You can use the following special characters to set the Format property of the Text field and the Memo field:
Special characterDescription
@Text character. Either a character or a space is required.
&Text character is not required.
<Force all characters to lowercase.
>Force all characters to uppercase.
When you link an Excel spreadsheet to an Access database, and the columns of the Excel spreadsheet contain more than 255 characters, the columns are mapped to Memo data types, and the Format property of the Memo field is set to @. Therefore, Access treats the Memo field as a text field, and you can view only 255 characters of data.

However, when you import an Excel spreadsheet that has columns that contain more than 255 characters, the columns are mapped to a Memo field with no specific format. Therefore, you can view the complete data in the field.
formatted formats cut off disappears truncate truncates truncated truncating cutting ACC2000 ACC2002 ACCXP ACC2003 ACC2007
REFERENCES
For additional information about how to import or link data from a spreadsheet, visit the following Microsoft Web site: For additional information about a formatted Memo field, click the following article number to view the article in the Microsoft Knowledge Base:
259893 Formatted Memo field appears to truncate data after 255 characters
Properties

Article ID: 839785 - Last Review: 09/17/2011 09:14:00 - Revision: 3.0

Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition, Microsoft Access 2000 Standard Edition

  • kbfield kbdatabase kbprb KB839785
Feedback
= 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" ass="col-sm-6 col-xs-24 ng-scope"> España - Español
Paraguay - Español
Venezuela - Español
microsoft.com/c.gif?DI=4050&did=1&t="> var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" rue';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">