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

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?

--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
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.

Still.
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
TransferSpreadsheet did the trick.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.