Data Truncation Upon Import - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Data Truncation Upon Import

Christopher Godfrey

15 months ago

I am importing an excel file into Access (*.xlsx file type).  The file is 57kb in size and has16 columns.  The last column in the spreadsheet is a text field that averages 500-600 characters in length.  The data in that last column looks like this;

     1SP, A9U, AAB, ABE, AF6, AII, AKE, AKK, AQP, ARU, ASD, AVK, AVU, AWS, AWT, AXP,
     B1V, B56, B5B, B5C, B71, B7Y, B83, BGP, BSF,…………

Presently, the data import into Access works fine except for that last column.  Access truncates the field down to 255 characters.
In Access, my temporary table is set up with a “Long Text” field type.  The VBA code I am using for import is attached.

How do I import this data file without the data in this field being truncated?

	Christopher Godfrey OP @Reply 15 months ago

Bill Carver @Reply

15 months ago

The hard limit according to the interwebs on a long text field is 255 characters.

"What is the limit of long text in Access?
255 characters is a hard limit imposed by Access. If you need something that supports larger field sizes, I recommend using SQL Server Express (Download Microsoft® SQL Server® 2022 Express from Official Microsoft Download Center) and then link to the tables in Access for your frontend."

That's one solution but I'm curious. What kind of data are those three letter codes? I am seeing that they are all comma separated. Can you just bring them in as individual fields?

	Kevin Robertson @Reply 15 months ago
Short Text: 255 characters Long Text: 4,294,967,295 characters

	Bill Carver @Reply 15 months ago
Thanks Kevin. This is proof that the internets can be wrong.

	Christopher Godfrey OP @Reply 15 months ago
I Would like to stay with Access to accomplish this. There has to be a way to import a long text field into Access without Access truncating the data.

	John Davy @Reply 15 months ago
Hi Christopher, Have you set the Column in Excel to Format Cell, Wrap? Try it. John

	Tom Juric @Reply 15 months ago
Are you looking to do this multiple time (with code) or a one-time deal? Import the Excel into Access and make sure the import filed (500 characters) is set to Long Text. If you save the steps, you can use those steps again in code any time you desire

	Christopher Godfrey OP @Reply 15 months ago
Yes, that worked. Is this possible using VBA and not the import Wizard?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View