如何在Excel中导入表中的Access中分配外键 [英] How to assign foreign keys in Access within imported table from Excel

问题描述

我将使用Access数据库而不是Excel。但我需要将一个巨大的Excel工作表中的数据导入到Access中的几个预先准备好的规范化表中。在核心Access表中，我主要有来自其他表的外键（当然其他一些字段是文本或日期）。

I will use Access database instead of Excel. But I need to import data from one huge Excel sheet into several pre-prepared normalized tables in Access. In the core Access table I have mainly the foreign keys from other tables (of course some other fields are texts or dates).

我应该如何在最简单的地方执行导入办法？我不能直接执行导入，因为在Access字段'Country'中没有例如United States字符串;必须有外键没有。来自表tblCountries的84。我想到Excel中的DLOOKUP函数并替换FK的字符串...你知道更简单的方法吗？

How should I perform the import in the easiest way? I cannot perform import directly, because there is NOT, for example, "United States" string in the Access field 'Country'; there must be foreign key no. 84 from the table tblCountries. I think about DLOOKUP function in the Excel and replace strings for FK... Do you know any more simple method?

谢谢你，Martin

Thank you, Martin

推荐答案

您没有提到如何将Excel数据导入多个Access表，因此我假设您将整个Excel文件导入到一个大表中然后从那里分解数据。我假设导入的数据可能与现有的Access键不匹配（即拼写错误，新值等），因此您需要找到这些数据以便进行更正。这将涉及创建一些不匹配的查询，然后创建一些更新查询，最后您可以使用追加查询将数据从导入表中提取到最终的休息位置。使用您的示例，您已导入'Country = United States'，但您需要将该值与键84相关联？

You don’t mention how you will get the Excel data into several Access tables, so I will assume you will import the entire Excel file into ONE large table then break out the data from there. I assume the imported data may NOT match with existing Access keys (i.e. misspellings, new values, etc.) so you will need to locate those so you can make corrections. This will involve creating a number of ‘unmatched queries’ then a number of ‘Update queries’, finally you can use Append queries to pull data from your import table into the final resting place. Using your example, you have imported ‘Country = United States’, but you need to relate that value to key "84"?

让我们设置一些示例：

1. 假设您将Excel数据导入一个大型Access表。另外假设您的导入有三个字段需要获取密钥。


2. 您已在Access中拥有多个控制表，类似于以下内容：

a。 tblRegion：包含RegionCode，RegionName（即1 = Pacific，2 = North America，3 = Asia，...）

a. tblRegion: contains RegionCode, RegionName (i.e. 1=Pacific, 2=North America, 3=Asia, …)

b。 tblCountry：包含CountryCode，Country，Region（即84 |美国| 2

b. tblCountry: contains CountryCode, Country, Region (i.e. 84 | United States | 2

c.tblProductType：包含ProdCode，ProductType（即VEH | vehicle; ELE | electrical; etc 。）

c. tblProductType: contains ProdCode, ProductType (i.e. VEH | vehicles; ELE | electrical; etc.)

d。假设导入的数据有字段

d. Assume your imported data has fields

以下是我要采取的步骤：

Here are the steps I would take:

1. 如果您的Excel文件还没有用于保存键值的列（即84），请在导入之前添加它们。或者在导入之后，修改表以添加列。



2. 为需要关联的每个关键字段创建不匹配的查询。（使用查询向导，查找不匹配的查询向导。这将显示您的密钥表中没有匹配项的所有导入数据，您需要更正这些值。即：

1. If your Excel file does not already have columns to hold the key values (i.e. 84), add them before the import. Or after the import, modify the table to add the columns.

2. Create ‘Unmatched query’ for each key field you need to relate. (Use ‘Query Wizard’, ‘Find Unmatched Query Wizard’. This will show you all imported data that does not have a match in your key table and you will need to correct those valuse. i.e.:

SELECT tblFromExcel.Country，tblFromExcel。 Region，tblFromExcel.ProductType，tblFromExcel.SomeData
FROM tblFromExcel LEFT JOIN tblCountry ON tblFromExcel。[Country] = tblCountry。[CountryName]
W HERE（（（tblCountry.CountryName）为空））;

SELECT tblFromExcel.Country, tblFromExcel.Region, tblFromExcel.ProductType, tblFromExcel.SomeData FROM tblFromExcel LEFT JOIN tblCountry ON tblFromExcel.[Country] = tblCountry.[CountryName] WHERE (((tblCountry.CountryName) Is Null));

使用匹配值更新FK：

UPDATE tblCountry
INNER JOIN tblFromExcel ON tblCountry.CountryName = tblFromExcel.Country
SET tblFromExcel.CountryFK = [CountryNbr];

UPDATE tblCountry INNER JOIN tblFromExcel ON tblCountry.CountryName = tblFromExcel.Country SET tblFromExcel.CountryFK = [CountryNbr];

对所有其他关键字段重复上述不匹配/匹配。

Repeat the above Unmatched / Matched for all other key fields.

这篇关于如何在Excel中导入表中的Access中分配外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！