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.
Access在几个Memo字段中截断数据，当我添加Excel文件时。访问表中的字段已经设置为Memo类型。我认为问题在于，在一些memo字段的前几行中，我没有任何条目。访问是假设数据是一个文本字段，尽管我已经将其设置为Memo类型。
I have tried appending as a CSV. Did not work.
我试过把它附加到CSV中。没有工作。
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.
我已经在第一行中放置了超过255个字符限制的假数据，如果这样做，数据不会被截断。
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?
我不希望在每次导入Excel文件时都必须输入虚拟数据。这个过程至少每两周完成一次，也许更频繁。我想建立一种简单的方法，为使用相同数据库的未来雇员导入数据。什么好主意吗?
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.
更新:即使在前几行中有虚拟数据，当我导入Excel文件时，Access也会截断10个Memo feilds中的3个数据(虚拟数据的字符长度为785)。现在我真的想不出什么主意了。
8 个解决方案
#1
10
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.
为了避免导入过程中的错误，请确保每个源列在每一行中包含相同类型的数据。Access扫描前8行，以确定表中字段的数据类型。我们强烈建议您确保前8行不会在任何列中混合不同数据类型的值。否则，访问可能不会为列分配正确的数据类型。
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.
显然,这意味着当将excel文件附加到一个现有表,即使列是格式化并保存为备忘录字段,如果所有8 excel文件的第一行是小于256字符,访问实际上假定您要指定文本,因此在255年后继续行删除字符。我已经执行了几个测试，将“虚拟”行放在前8行中，每个测试都触发了超过255个字符的导入。
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
如果可以覆盖表中已经存在的所有数据，那么导入到新表是很方便的。但是，如果您确实需要追加，我建议将导入导入到临时表中，然后从该表中追加。一种简单的方法是保存一个导入，然后从VBA执行它，就像Elliot_et_al想做的那样。然后还可以在VBA中运行append查询。如果你正确地摆好桌子，你也许能侥幸逃脱。
INSERT INTO [MyTable]
SELECT [MyTable_temp].*
FROM [MyTable_temp];
#2
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.
什么值得....我在2013年遇到了一个类似的问题——从XLS导入到255个字符时，它将字段截断到255个字符，即使导入向导选择了LONG TEXT作为字段，甚至在前面几行中使用> 255字符的字段也是如此。
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:
编辑为ADD:在Access 2013中，如果您已经将XLS文件导入Access，并且无法返回该文件以尝试首先链接，那么请尝试以下操作:
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.
转到该表的设计视图，转到屏幕底部的字段属性，并将长文本“文本格式”设置为“富文本”。就在今天，我发现这使我不必重新创建几个月前从excel中导入的表，并且发现即使我将“Notes”列设置为Long Text，它仍然在截断我手动输入到255个字符的文本。切换到富文本使该文本可见。
#3
1
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.
我使用excel与外部合作伙伴进行通信，并将它们的报告捕获到一个访问数据库中。我发现最好的方法是将“虚拟”第一行插入到工作表中，该工作表在用户填充的数据可能超过255个字符的任何给定列中包含大于255个字符。
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..?
希望这有助于. . ?
#4
0
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.
我对Access 2010也有同样的问题。在查看了前25条记录后，我找到了两个不同的解决方案，以便在导入时确定每个列上的数据类型。
- 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.
- 按照列的长度以降序对导入记录进行排序。这意味着某些列中大于255个字符的记录将在前25条记录中。然后，Access可以导入这些记录，而无需截断。
- Created link table specifying column data type as memo and then appended to table.
- 创建链接表，指定列数据类型为memo，然后追加到表中。
#5
0
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.
我过去很幸运，上面提供了丰富的文本解决方案，并使用“虚拟行”作为导入的第一个记录。谢谢你的!然而，今天我认为我遇到了一个更有效/一致的进口解决方案，您将重复多次。我在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!)
使用导入向导，就像将数据导入到新表一样。检查所有设置规格的屏幕。最重要的是，在冗长的字段选项/数据类型区域中检查或指定每个字段的数据类型(对于我最近的文本文件，这是Import text向导的第三个屏幕)——一定要在这里指定Memo字段。(别担心，你只需要做一次!)
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.
当你到达最后的“那是所有的信息，巫师需要…”屏幕，寻找“高级…”按钮在左下角。这将显示一个屏幕，概述您刚才所做的一切。在右边找“Save as…”用一个有用的名字保存这些规格。(你可以点击下面的“spec…”来确认你保存了你的spec。)单击“ok”离开高级屏幕。
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!
如果不需要创建新表，现在可以取消向导。接下来——从现在开始，您可以每次都这样做，以避免截断——转到常规的导入向导，并“将记录的副本附加到表中……”在向导中，您应该看到相同的“Advanced…”按钮。打开它,点击“规格……”，并双击保存的规范。点击“OK”退出“Advanced”，完成向导。这将告诉Access将memo字段保留为memo字段!
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.
当将csv导入到现有的表时，我发现在进入“高级”屏幕之前，我需要经过几个普通的向导屏幕(例如指定文本限定符)。不知道为什么这会让它开心，只是供大家参考。
I hope this helps someone else who has struggled with Field Truncation import errors like me!
我希望这能帮助其他像我一样在字段截断导入错误中挣扎的人!
#6
0
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
在许多情况下，只需将memo字段的文本格式从普通文本更改为RTF，现在如果打开表数据，就可以看到所有导入的文本
#7
0
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.
Excel和Access很古怪。显然，将Excel或csv附加到具有与长文本相同属性的现有访问表的末尾是一个问题。附加数据将默认为所有长文本到短文本。解决方法是将数据输出到Excel，将数据附加到一个表中，然后将其作为一个新的表导入。访问有一个问题，把附加数据作为短文本，而不是长文本，不管你做什么。
Do make sure that when using the import wizard to change the properties of the column to Long Text.
请确保在使用import向导将列的属性更改为Long Text时。
I hope this helps.
我希望这可以帮助。
#8
0
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.
2013年，我遇到了同样的问题。当我导入一个包含大于255个字符的列文本的excel表格时，它正在被截断。我做了很多研究，终于找到了解决办法。实际上，一些MS如何访问数据库，根据第一个记录列文本长度确定文本的大小，并为后续的记录固定该长度。如果它的长度小于255，那么access将自动将进一步的记录长度限制为255，或者永远是第一个记录列的长度。我确保第一个记录具有所有记录文本列的最大长度(已排序)，然后导入，它对我来说工作得很好。
#1
10
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.
为了避免导入过程中的错误，请确保每个源列在每一行中包含相同类型的数据。Access扫描前8行，以确定表中字段的数据类型。我们强烈建议您确保前8行不会在任何列中混合不同数据类型的值。否则，访问可能不会为列分配正确的数据类型。
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.
显然,这意味着当将excel文件附加到一个现有表,即使列是格式化并保存为备忘录字段,如果所有8 excel文件的第一行是小于256字符,访问实际上假定您要指定文本,因此在255年后继续行删除字符。我已经执行了几个测试，将“虚拟”行放在前8行中，每个测试都触发了超过255个字符的导入。
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
如果可以覆盖表中已经存在的所有数据，那么导入到新表是很方便的。但是，如果您确实需要追加，我建议将导入导入到临时表中，然后从该表中追加。一种简单的方法是保存一个导入，然后从VBA执行它，就像Elliot_et_al想做的那样。然后还可以在VBA中运行append查询。如果你正确地摆好桌子，你也许能侥幸逃脱。
INSERT INTO [MyTable]
SELECT [MyTable_temp].*
FROM [MyTable_temp];
#2
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.
什么值得....我在2013年遇到了一个类似的问题——从XLS导入到255个字符时，它将字段截断到255个字符，即使导入向导选择了LONG TEXT作为字段，甚至在前面几行中使用> 255字符的字段也是如此。
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:
编辑为ADD:在Access 2013中，如果您已经将XLS文件导入Access，并且无法返回该文件以尝试首先链接，那么请尝试以下操作:
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.
转到该表的设计视图，转到屏幕底部的字段属性，并将长文本“文本格式”设置为“富文本”。就在今天，我发现这使我不必重新创建几个月前从excel中导入的表，并且发现即使我将“Notes”列设置为Long Text，它仍然在截断我手动输入到255个字符的文本。切换到富文本使该文本可见。
#3
1
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.
我使用excel与外部合作伙伴进行通信，并将它们的报告捕获到一个访问数据库中。我发现最好的方法是将“虚拟”第一行插入到工作表中，该工作表在用户填充的数据可能超过255个字符的任何给定列中包含大于255个字符。
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..?
希望这有助于. . ?
#4
0
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.
我对Access 2010也有同样的问题。在查看了前25条记录后，我找到了两个不同的解决方案，以便在导入时确定每个列上的数据类型。
- 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.
- 按照列的长度以降序对导入记录进行排序。这意味着某些列中大于255个字符的记录将在前25条记录中。然后，Access可以导入这些记录，而无需截断。
- Created link table specifying column data type as memo and then appended to table.
- 创建链接表，指定列数据类型为memo，然后追加到表中。
#5
0
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.
我过去很幸运，上面提供了丰富的文本解决方案，并使用“虚拟行”作为导入的第一个记录。谢谢你的!然而，今天我认为我遇到了一个更有效/一致的进口解决方案，您将重复多次。我在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!)
使用导入向导，就像将数据导入到新表一样。检查所有设置规格的屏幕。最重要的是，在冗长的字段选项/数据类型区域中检查或指定每个字段的数据类型(对于我最近的文本文件，这是Import text向导的第三个屏幕)——一定要在这里指定Memo字段。(别担心，你只需要做一次!)
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.
当你到达最后的“那是所有的信息，巫师需要…”屏幕，寻找“高级…”按钮在左下角。这将显示一个屏幕，概述您刚才所做的一切。在右边找“Save as…”用一个有用的名字保存这些规格。(你可以点击下面的“spec…”来确认你保存了你的spec。)单击“ok”离开高级屏幕。
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!
如果不需要创建新表，现在可以取消向导。接下来——从现在开始，您可以每次都这样做，以避免截断——转到常规的导入向导，并“将记录的副本附加到表中……”在向导中，您应该看到相同的“Advanced…”按钮。打开它,点击“规格……”，并双击保存的规范。点击“OK”退出“Advanced”，完成向导。这将告诉Access将memo字段保留为memo字段!
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.
当将csv导入到现有的表时，我发现在进入“高级”屏幕之前，我需要经过几个普通的向导屏幕(例如指定文本限定符)。不知道为什么这会让它开心，只是供大家参考。
I hope this helps someone else who has struggled with Field Truncation import errors like me!
我希望这能帮助其他像我一样在字段截断导入错误中挣扎的人!
#6
0
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
在许多情况下，只需将memo字段的文本格式从普通文本更改为RTF，现在如果打开表数据，就可以看到所有导入的文本
#7
0
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.
Excel和Access很古怪。显然，将Excel或csv附加到具有与长文本相同属性的现有访问表的末尾是一个问题。附加数据将默认为所有长文本到短文本。解决方法是将数据输出到Excel，将数据附加到一个表中，然后将其作为一个新的表导入。访问有一个问题，把附加数据作为短文本，而不是长文本，不管你做什么。
Do make sure that when using the import wizard to change the properties of the column to Long Text.
请确保在使用import向导将列的属性更改为Long Text时。
I hope this helps.
我希望这可以帮助。
#8
0
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.
2013年，我遇到了同样的问题。当我导入一个包含大于255个字符的列文本的excel表格时，它正在被截断。我做了很多研究，终于找到了解决办法。实际上，一些MS如何访问数据库，根据第一个记录列文本长度确定文本的大小，并为后续的记录固定该长度。如果它的长度小于255，那么access将自动将进一步的记录长度限制为255，或者永远是第一个记录列的长度。我确保第一个记录具有所有记录文本列的最大长度(已排序)，然后导入，它对我来说工作得很好。