将多个Excel文件中的数据导入Access，映射字段和comparin [英] Importing data from Multiple Excel Files into Access, mapping the fields and comparin

问题描述

我正在从Filemaker转到Access我需要创建一个Access数据库来管理来自某些供应商网站的一些数据源。


数据源有所不同excel格式2003和2007.目前有5种不同的Feed。


Feed有相同的一般信息，但有不同的布局。示例feed1.xls可能将第3列设为

结束日期，其中feed2.xlsx可能将第6列作为到期日期。数据是相同的但是在不同的列和列名称中。


我正在寻找的最终结果是将所有数据源合并到Access中的一个表中以进行管理。由于Feed定期更改，我还需要了解更改内容，因此突出显示新的/更改的数据也非常有用。


Filemaker曾经非常好用你可以将excel字段映射到特定列的导入工具，但我在Access中看不到这个。


Access的最佳方法是什么？

Hi, I''m moving over to Access from Filemaker I need to create an Access db that manages some data feeds from some supplier websites.

The data feeds come in differing excel formats 2003 and 2007. At the moment its 5 different feeds.

The feeds have the same general information in but come in different layouts. Example feed1.xls may have column 3 as
End Date, where feed2.xlsx may have column 6 as Expiry Date. The data is the same but in different columns and column names.

The end result I''m looking for is to merge all the data feeds into one table in Access to manage it. As the feeds change on a regular basis I also need to be aware of what has changed so highlighting the new / changed data would also be very useful.

Filemaker used to have a very good import tool that you could map excel fields to specific columns, but I cannot see this in Access.

What would be the best approach with Access?

推荐答案

这取决于您想要使用多少自动化。您想要的越多，您需要提供更好的信息，以便能够帮助和指导您。


在这个阶段，我可以告诉您导入在Excel中，Access非常缺乏灵活性和指定所需内容的能力。因此，我建议您开始考虑将电子表格导入新表格，然后检查导入的数据，以便将其智能地附加到主表格中。导入工作簿后，您可以访问所有字段及其名称等。

That depends on how much automation you want to use. The more you want, the better information you''ll need to provide in order even to be able to help and direct you.

At this stage though, I can tell you that importing in Access from Excel is quite lacking in flexibility and the ability to specify what you want. Therefore I would suggest you start looking at importing the spreadsheets into new tables and then examining the data imported in order to append it intelligently to your main table. Once the workbook has been imported you have access to all the fields and their names etc., etc.

为什么不在Excel中管理它？

创建一个新工作表，根据需要为字段（列）命名，然后通过简单的附加数据填充所需的列。

当然，该任务可以完全自动化（在Excel VBA中）。

Why do not manage that in Excel ?
Create a new sheet, name the fields (columns) as you need, then fill that columns by simple append data from where you need.
Of course that task can be fully automated (in Excel VBA).

Mihail：

为什么不在Excel中管理它？

Mihail:
Why do not manage that in Excel ?

如果您对应用程序自动化，但问题非常具体：

It''s possible to handle it in Excel if you''re comfortable with Application Automation, but the question is quite specific :

Aiko Hayashi ：

Access的最佳方法是什么？

Aiko Hayashi:
What would be the best approach with Access?

这篇关于将多个Excel文件中的数据导入Access，映射字段和comparin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！