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.

Solution 1

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];

Solution 2

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.

Solution 3

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.

Solution 4

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..?

Solution 5

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.

View more solutions