Ms Access 简明教程

MS Access - Data Import

在本章中，我们将讨论在 Access 中导入数据以及您可以使用 Access 导入哪些类型的数据。通常，数据存储在各种格式、文件和位置，这使得获取和使用数据变得很困难。如果您在电子表格、SharePoint 列表或其他格式中拥有数据，则只需几个步骤即可将其导入 Access 数据库，使其在 Access 中更易于获得。

In this chapter, we will be talking about importing data in Access and what kinds of data you can import using Access. Normally data is stored in various formats, files, and locations, which makes it hard to get and use it. If you have data in a spreadsheet, a SharePoint list, or some other format, you can import it into an Access database with just a few steps, making it much more, easily available in Access.

  1. The Save As command is generally used to save a document in another format, so that you can open it in another program.

  2. In Access you can’t use the Save As command in the same way, you can save Access objects as other Access objects, but you cannot save an Access database as a spreadsheet file.

  3. To save Access as a spreadsheet file, you will need to use the import feature on the External Data tab.

Different Types of Data Access Can Import

要了解您可以在 Access 数据中导入哪种类型的数据，让我们打开您的数据库并转到“外部数据”选项卡。

To understand what kind of data you can import in the Access data, let us open your database and go to the External Data tab.

import

在“导入和链接”组中，您可以看到 Access 中可用于数据导入的不同类型选项。下面是使用最广泛的数据导入格式。

In the import & Link group, you can see the different kind of options available for data import in Access. Following are the most commonly used data import formats.

  1. Microsoft Office Excel

  2. Microsoft Office Access

  3. ODBC Databases (For example, SQL Server)

  4. Text files (delimited or fixed-width)

  5. XML Files

Example

让我们看一个从 Excel 文件导入数据的简单示例。以下是 Access 文件中的数据。

Let us look at a simple example of data importing from an Excel file. Here is the data in Access file.

access file

要在 Access 中导入数据，我们首先需要打开 Access 数据库，然后转到“外部数据”选项卡，如下面的屏幕截图所示。

To import the data in Access, we first need to open the Access database and then go to the External Data tab as in the following screenshot.

external data

在“导入和链接”组中，您会看到一个选项 Excel。让我们单击该选项。

In Import & Link group, you will see an option Excel. Let us click on that option.

link group

浏览您想要从中导入数据的 Excel 文件，然后我们有不同的选项来存储数据。让我们选择第一个选项并单击确定。

Browse the Excel file from which you want to import data and the then we have different options to store data. Let us select the first option and click Ok.

spreadsheet

在这里您将看到您数据的预览。现在，单击下一步。

Here you will see the preview of your data. Now, click Next.

preview data

在预览中，你现可以看到第一行包含列标题。现在我们勾选该复选框，然后点击下一步。

In the Preview, you can now see that the first row contains the column headings. Let us now check the check box and click Next.

field option

你现在将看到一个对话框，你可以在其中为每列/字段设置数据类型。如果你不想导入任何字段，只需勾选该复选框。完成“FirstName”字段之后，只需点击“MiddleInitial”字段。

You will now see a dialog box where you can set the data type for each column/field. If you don’t want to import any field, just check the check box which says do not import field. Once you are done with the FirstName field, just click on the MiddleInitial field.

middleinitial

现在让我们遍历所有字段，然后点击下一步。

Let us now go through all the fields and then, click Next.

select option

以下是主键的不同选项。让我们选择第一个选项并点击下一步。

Here are the different options for primary key. Let us select the first option and click Next.

tblcontacts

在最后一个对话框中，你可以输入你选择的表名称，然后点击完成。

In the last dialog box, you can enter the table name of your choice and click Finish.

last dialog box

如果你希望保存所有这些步骤，则勾选复选框，然后关闭该对话框。

If you want to save all these steps, then check the checkbox and close the dialog box.

现在让我们到导航窗格。你将看到此处添加了一个新表，当你打开新添加的表时，你会在 Access 中看到所有数据。

Let us now go to the Navigation pane. You will see a new table is added here and when you open the newly added table you will see all of your data in Access.

data in access

Example

现在让我们看看从 Access 数据库导入数据的另一个示例。让我们再次进入外部选项卡。

Let us now look at another example of importing data from the Access database. Let us go to the External Tab again.

external tab

在导入和链接组中，点击 Access 选项。

In Import & Link group, click on the Access option.

access database

浏览你想从中导入数据的 Access 数据库，然后选择第一个选项，即导入表、查询、窗体等。现在，点击确定。

Browse the Access database from which you want to import the data and then select the first option which says Import tables, queries, form etc. Now, click Ok.

import objects

在上述对话框中，你可以看到表、查询、窗体等的不同选项卡，你可以从中选择希望导入哪种类型的数据。

In the above dialog box, you can see different tabs for Tables, Queries, Forms etc. from where you can select what kind of data you want to import.

import reports

让我们进入 Reports 标签，然后选择你想导入的任何报表；你还可以通过点击 Select All 按钮选择所有数据。让我们选择 Projects 并点击 Ok 。

Let us go to the Reports tab and select any report you want to import; you can also select all the data by clicking on the Select All button. Let us select Projects and click Ok.

select all

现在，关闭对话框。在导航窗格中，你将看到添加了一个新报表。让我们打开此报表，你将看到该报表中的所有数据。

Now, close the dialog box. In the navigation pane, you will see that a new report is added. Let us open this report and you will see all the data in that report.

new report