We help IT Professionals succeed at work.

Cannot import more than 255 charaters from an Excel cell to a table field

SteveL13 asked
I had this problem once before and thought I had it solved but I guess not.

I'm trying to import an Excel file into a table in Access 2013.  One of the fields contains many more than 255 characters but the import process WILL NOT import more than the first 255 characters!  Is very frustrating.

I have done what some exports suggested before including:

1) Be sure one of the first 25 rows I the Excel file have more than 255 characters.  Actually, they all do.
2) Link to the Excel file and try an append query to the table.  Doesn't work either.

Any other thoughts?

Watch Question

Most Valuable Expert 2014
There are different limits.
While an Excel cell can hold 32K characters, only the first 1024 will ever display, and sometimes the first 255 only will display in the cell, while the full 1024 will display only in the address/formula bar.

I'm trying to import an Excel file into a table in Access 2013
I take it that you are trying to use an import specification.
That is likely to yield unhappy results.
255 is the cutoff number between text and memo field sizes.
Likely you are facing truncation issues in the import spec that are unlikely to be resolvable.

Try TransferSpreadsheet or CopyFromRecordset methods in VBA.
That'll likely get you to the 1024 limit -- but nothing will get you beyond that.
Excel will store 32K characters in a cell -- but you'll never see more than 1024.
The rest are accessible only through code and userforms.

You are hitting a truncation AND THERE ARE LOTS of reasons that Access will truncate a memo field in a query
A lot of those reason involve stuff I know you habitually use, like DLookup for instance.
Any kind of grouping and Select Distinct can cause truncation.

The way to work around the problems of truncation are through clever query designs.
Get an initial query with ALL the fields EXCEPT the truncated fields working to your satisfaction.
Save it.
Start another query.
Use the saved query and the tables with the truncation prone fields
Create a straight SELECT query with no DISTINCT, no functions, no-nothing except fields
Try punching that out to Excel.

Post your SQL for the export, and we'll see what landmine you've stepped on.


TransferSpreadsheet did the trick.  Thanks.