Truncated data when importing from Excel to Memo Access field

Question

Access truncates the data into a couple of Memo fields when I add the Excel file. The field in the Access table is already set as the Memo type. I believe the problem is that I do not have entries in the first few lines of some memo fields. Access assumes the data is a text field, although I already set it as a Memo type.

I tried adding to CSV. Does not work.

I put dummy data in the first line, which exceeds the 255 character limit, and if I do, the data will not be truncated.

I do not want to put dummy data every time I have to import an Excel file. This is a process that will be completed at least once every two weeks, perhaps more frequently. I would like to create an easy way to import data for future employees working with the same database. Any ideas?

Update: Even with dummy data in the first two lines, Access truncates data for 3 out of 10 Memo backgrounds when importing an Excel file (dummy data characters are 785). Now I'm really at a loss for ideas.

Answer 1

Some time passed, but I had the same problems as you.

After long digging, I found that the wonderful world of Microsoft explains :

To avoid import errors, make sure each source column contains the same data type on each row. Access checks the first eight source rows to determine the data type of the fields in the table. We strongly recommend that you ensure that the first eight row sources do not mix the values ​​of different data types in any of the columns. Otherwise, Access may not assign the correct data type to the column.

Apparently, this means adding an excel file to an existing table, even if the columns are formatted and saved as memo fields, that if all 8 of the first lines in the excel file are less than 256 characters, Access assumes that you are actually Designed to indicate text, thus trimming the remaining lines after 255 characters. I performed several tests by placing the dummy lines in the top 8 lines, and each of them caused the import of more than 255 characters.

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

Importing into a new table is convenient if you can overwrite all the data that is already in the table. However, if you really need to add, I would suggest importing a temporary table, and then add from there. An easy way to do this is to save the import and then execute it from VBA, for example Elliot_et_al wanted to do . Then you can also run the append request in VBA. If you set up your tables correctly, you can leave with

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

Answer 2

For what it's worth .... I ran into a similar problem with Access 2013 - it cut fields to 255 characters when importing from XLS, even when the import wizard selected LONG TEXT as the field, and even when I had fields s> 255 characters in the first a few lines.

A colleague suggested that I link the spreadsheet instead of importing into a new spreadsheet, the problem disappeared. I also created a new table based on the related one, and all is well.

EDITED TO ADD . In Access 2013, if you have already imported the XLS file into Access and cannot return to it to try linking first, try this instead:

Go to “Design Presentation of the Table”, go to “Field Properties” at the bottom of this screen and set the “Text Format” of the long text to “Rich Text”. Just today, I discovered that it freed me from having to recreate the table that I imported from excel a few months ago, and found that although I had the Notes column set to Long Text, it still truncated text, which I manually entered up to 255 characters. Switching to Rich Text made this text visible.

Answer 3

I use excel to communicate with external partners and collect reports from them into an access database. I found that the best way to do this is to insert the first line of "dummy" into the worksheet containing more than 255 characters in any column where the data the user has filled can exceed 255 characters.

That way, when I import the data, it always imports all the text, and then I can just remove the “dummy” row from the database table.

I often use the book "import template" with which I refer from my access database. I set the template page to format as a table before linking (so the import contains all the data without specifying a range each time) and make the first hidden line "dummy".

That way, I can simply copy and paste the data into the import template, and then run the database query to import (and, if necessary, convert) the data into the database, with a second request to delete the "dummy" record after that.

Hope this helps ..?

Answer 4

I had the exact same problem with Access 2010. I discovered two different workarounds after I found out Access by looking at the first 25 records to determine the data type for each column when importing.

• Sort imported records by column length in descending order. This means that records with more than 255 characters in a certain column will be among the first 25 records. Access was then able to import these records without truncating.
• A link table has been created, which indicates the data type of the column as a note, and then is added to the table.

Answer 5

I was lucky in the past with the Rich Text solution proposed above, and also using "dummy strings" as the first imported record. Thank you for that! However, today I think I came across a more efficient / consistent import solution that you will repeat many times. I tried this in Access 2007.

Use the import wizard as if you were importing data into a new table. Go through all the screens that set your characteristics. Most importantly, check or specify the data type for each field in the boring field of fields / Data Type (for my last text file, this was the third screen of the text import wizard). Be sure to include your Memo fields here. (Don’t worry, you will only need to do this once!)

When you get to the final screen, "So that everything you need is a wizard ...", find the "Advanced ..." button in the lower left corner. This raises the screen, summing up everything you just did. Search for "Save As ..." on the right. Keep these specifications with a useful name. (You can confirm that you have saved your specifications by clicking "Specs ..." directly below.) Click "Okay" to exit the extended screen.

Now you can refuse the wizard if you really do not need to create a new table. Next - and this is what you can do every time from now on to avoid truncation - go to the usual import wizards with the help of "Add a copy of the records to the table ...". In the wizard you should see the same "Advanced ..." button. Open it, click "Specs ..." and double-click the saved specification. Say OK to exit Advanced and complete the wizard. This should tell Access to have your memo fields marked as memo fields!

When importing CSV into existing tables, I found that before going to the "Advanced" screen, I need to go through a couple of normal wizard screens (for example, specify "Text Qualifier"). Not sure why this makes him happy, just FYI.

I hope this helps someone who has been struggling with Field Truncation import errors like me!

Answer 6

In many cases, you simply change the text format of the memo field from plain text to RTF, now if you open the table data, you can see all the imported texts

Answer 7

Excel and Access are dodgy. Apparently, the problem of adding Excel or CSV to the end of an existing Access table that has the same Long Text properties is a problem. When adding data, all long text for short text will be used by default. The work was related to data output in Excel, adding data to one table and then importing it as a new table in Access. Access has a problem handling the added data as Short Text instead of Long Text, no matter what you do.

Be sure to use the import wizard to change column properties in Long Text.

Hope this helps.