Truncated Data when Importing from Excel to an Access Memo Field

For what it's worth....I ran into a similar problem with Access 2013 - it was truncating fields to 255 characters on import from XLS, even when the Import Wizard selected LONG TEXT as the field, and even when I had fields with > 255 characters in the first few rows.

A colleague suggested that I link the spreadsheet instead importing to a new table, the issue went away. I also created a new table based on the linked one, and all is good.

EDITED TO ADD: In Access 2013, if you've already imported the XLS file into Access and cannot go back to it to try to link first, try this instead:

Go to Design View of the table, go to Field Properties at the bottom of that screen and set the Long Text "Text Format" to "Rich Text". Just today, I found that this saved me from having to recreate a table that I'd imported from excel months ago and found that even though I had the "Notes" column set to Long Text, it was still truncating text that I was manually entering in to 255 characters regardless. Switching to Rich Text made this text visible.

It has been a while, but I was having the same issues as you.

After much digging, I found that the wonderful world of microsoft explains:

To avoid errors during importing, ensure that each source column contains the same type of data in every row. Access scans the first eight source rows to determine the data type of the fields in the table. We highly recommend that you ensure that the first eight source rows do not mix values of different data types in any of the columns. Otherwise, Access might not assign the correct data type to the column.

Apparently, this means when appending an excel file to an existing table, even when columns are formatted and saved as memo fields, that if all 8 of the first rows in the excel file are less than 256 chars, Access assumes you actually meant to specify text, thus truncating the remaining rows after 255 chars. I have performed several tests placing "dummy" rows within the top 8 rows, and each triggered the import of more than 255 chars.

Now, if you import to a new table, the wizard allows you to pick all of the formatting options.

Importing to a new table is convenient if you are okay with overwriting all of the data already in the table. However, if you truly need to append, I would suggest importing to a temporary table, then appending from there. An easy way to do this is to save a import then execute it from VBA, like Elliot_et_al wanted to do. You could then also run the append query in VBA as well. If you set up your tables correctly you may be able to get away with

INSERT INTO [MyTable]
SELECT [MyTable_temp].*
FROM [MyTable_temp];

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.

I use excel to communicate with external partners and capture reports from them into an access database. I've found the best way to do this is to insert a "dummy" first row into the worksheet that contains greater than 255 characters in any given column where the user-populated data is likely to exceed 255 characters.

In this way when I import the data it always imports all the text, and then I can simply delete the "dummy" row from the database table.

I frequently use an "import template" workbook that I link to from my access database. I set the template page to be formatted as a table before linking (so that the import contains all data without having the specify the range each time), and make the first "dummy" row hidden in there.

In this way I can simply copy and paste the data into the import template and then run a database query to import (and if necessary, transform) the data into the database, with a second query to delete the "dummy" record afterwards.

Hope this helps..?