从 Excel 导入到 Access Memo 字段时截断的数据 [英] Truncated Data when Importing from Excel to an Access Memo Field

问题描述

当我附加 Excel 文件时，Access 会截断几个备注字段中的数据.Access 表中的字段已设置为备注类型.我认为问题在于我在某些备注字段的前几行中没有任何条目.Access 假设数据是一个文本字段，即使我已经将其设置为 Memo 类型.

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.

我尝试以 CSV 格式附加.没有用.

I have tried appending as a CSV. Did not work.

我在第一行放置了超过 255 个字符限制的虚拟数据，如果我这样做，数据不会被截断.

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.

我不想每次必须导入 Excel 文件时都必须输入虚拟数据.这是一个至少每两周完成一次的过程，可能更频繁.我想设置一种简单的方法来为使用同一数据库的未来员工导入数据.有什么想法吗?

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 文件时，Access 也会截断 10 个 Memo 字段中 3 个的数据(虚拟数据的字符长度为 785).现在我真的很茫然.

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.

推荐答案

已经有一段时间了，但我遇到了和你一样的问题.

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:

为避免导入时出错，请确保每个源列每行包含相同类型的数据.Access 扫描第一个八个源行来确定字段的数据类型桌子.我们强烈建议您确保前八个来源行不会在任何列中混合不同数据类型的值.否则，Access 可能不会将正确的数据类型分配给列.

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.

显然，这意味着当将 excel 文件附加到现有表时，即使列被格式化并保存为备注字段，如果 excel 文件中的所有 8 行第一行都小于256 个字符，Access 假定您实际上打算指定文本，从而截断 255 个字符后的剩余行.我执行了几次测试，将虚拟"行放在前 8 行中，每个测试都触发了超过 255 个字符的导入.

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.

如果您可以覆盖表中已有的所有数据，则导入新表会很方便.但是，如果您确实需要追加，我建议导入到临时表，然后从那里追加.一个简单的方法是保存导入然后从 VBA 执行它，比如 Elliot_et_al 想做.然后，您也可以在 VBA 中运行附加查询.如果你正确设置你的桌子，你可能会逃脱

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 导入到 Access Memo 字段时截断的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！