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.

Hemimorphite

We are using the Import Spreadsheet Wizard in the External Data tab in Access 2010. I am not sure how to get the SQL code from the wizard. Is there and easy way to do that. – Hemimorphite

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

Firebox

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

Florida

Answer 3

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

Hampton

Answer 4

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.

Jerkwater

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

Abound

Answer 6

I had same exact problem with Access 2010. I found two different workarounds after finding out Access look at first 25 records to determine type of data on each columns when importing.

1. Sorted importing records by length of column in descending order. This means records with larger than 255 characters in some column will be among first 25 records. Then, Access was able to import those records without truncating.
2. Created link table specifying column data type as memo and then appended to table.

Academic

Answer 7

I've had luck in the past with Rich Text solution offered above as well as using "dummy rows" as the first record imported. Thank you for those! However, today I think I've come across a more efficient/consistent solution for imports you'll repeat many times. I tried this in Access 2007.

Use the import wizard as if you're importing the data to a new table. Go through all the screens setting your specifications. Most important, check or specify the data type for each field in the tedious Field Options / Data Type area (for my recent text file, this was the 3rd screen of the Import Text Wizard)--be sure to specify your Memo fields here. (Don't worry, you'll only have to do this once!)

When you arrive at the final "That's all the info the wizard needs..." screen, look for the "Advanced..." button on the lower left. This brings up a screen summarizing everything you just did. Look for "Save as..." on the right. Save these specs with a helpful name. (You can confirm you saved your specs by clicking "Specs..." directly below.) Click "Okay" to leave the advanced screen.

You can now cancel out of the wizard if you don't actually need to create a new table. Next--and this is what you can do every time from now on to avoid truncations--go to the normal import wizard with "Append a copy of the records to the table..." In the wizard, you should see that same "Advanced..." button. Open it, click "Specs...", and double-click your saved specification. Say "OK" to exit "Advanced," and complete the wizard. This should tell Access to keep your memo fields as memo fields!

When importing CSVs to existing tables, I find I need to go through a couple of the normal wizard screens (e.g. specify the Text Qualifier) before going to the "Advanced" screen. Not sure why this makes it happy, just FYI.

I hope this helps someone else who has struggled with Field Truncation import errors like me!

Backsight

Answer 8

In many case you just change text format of memo field from normal text to RTF, now if you open table data you can see all imported text

Antione

Answer 9

SOLVED: 1.- Setup the table in Access with the type of field you want. 2.- Add a made up record with long text in the fields you wanna be sure the long text is imported 3.- Select import excel to add up to your table It shluod work

Furcula

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Yelmene

Answer 10

What has worked for me with MS Access 2019, is to import spreadsheet into local table. But during import for the column with long text, choose Data Type: Binary. After import that column shows as Data Type: Hyperlink. Then change it to Data type: Long Text and save.

For big tables it may show "Not enough memory" error. In this case use VBA function PlainText() to convert this column to plain text.

Toxophilite