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

问题描述

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

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?

更新：即使在前几行中使用虚拟数据，Access将截断10个备忘录中的3个数据导入Excel文件时的feilds（伪数据的字符长度为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可能不会将正确的数据类型分配给
列。

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