Truncated Data when Importing from Excel to an Access Memo Field

Question

Access is truncating the data in a couple Memo fields when I am appending an Excel file. The field in the Access table is already set as a Memo type. I believe the problem is that I do not have any entries in the first few rows of some of the memo fields. Access is assuming the data is a text field, even though I have already set it as a Memo type.

I have tried appending as a CSV. Did not work.

I have put dummy data in the first row that exceeds the 255 character limit and the data is not truncated if I do that.

I do not want to have to put dummy data in every time I have to import an Excel file. This is a process that will be completed at least biweekly, maybe more frequent. I would like to set up an easy way to import the data for future employees that work with the same database. Any ideas?

Update: Even with dummy data in the first couple of rows, Access is truncating the data for 3 out of the 10 Memo feilds when I import the Excel file (Character length of dummy data is 785). Now I am really at a loss for ideas.

Answer 1

Excel and Access are quirky. Apparently, appending Excel or CSVs to the end of an existing Access table which has the same properties of Long Text is an issue. Appending data will default all Long Text to Short Text. The work around was to output the data to Excel, append the data into one table, then import it as a new table in Access. Access has a problem with treating appending data as Short Text instead of Long Text regardless what you do.

Do make sure that when using the import wizard to change the properties of the column to Long Text.

I hope this helps.

Answer 2

I faced the same issue in MS Access 2013. When I import an excel sheet with one of the column text greater than 255 characters, it was truncating. I did lot of research and finally I am able to find a workaround. Actually, Some how MS Access database determining the size of the text based on the first record column text length and fixing that length for the subsequent records. If it's length < 255, access automatically limiting further records length to 255 size or what ever is the first record column length. I ensured the first record to have max length of all the records text column (sorted) and then imported and it worked well for me.