Importing Data into Excel Invoice Manager
Generally Microsoft Access allows you to import external data into
a new table. But if you can carefully organize and prepare the source
data, you can also import external data from a worksheet into an existing
Access table. In this tutorial, we will go through the steps required for
importing existing data from an Excel worksheet into Excel Invoice Manager.
To import data from Excel workbooks into an existing table,
the source data must meet specific requirements: the first row of the source
data must contain column headings, and the column headings must match field names
in the table.
- Get a list of field names of the Access database table.
Let's say you want to import a contact list in an Excel worksheet into the Customer table.
The first thing you need to do is to get a list of field names of the Customer table.
This can be done with either the Database Designer tool or Microsoft Access.
To use Microsoft Access, simply double click the database file to open it. Select the Customer table.
In Microsoft Access 2000 - 2003, click the Design button to open the table structure view;
In Microsoft Access 2007, right-click the table name and choose Design View.
To use Database Designer, launch it and open the database definition file working
with the database. Click the Next button to go to the 'Step 2 - Define custom fields
in Database Definition File' screen, and then select the Customer table and check the
Display Standard Fields checkbox.
- Preparing the source data.
Open the workbook containing the contact list. If you prefer, you can backup the workbook before
modifying it. Now adjust the contact list. Make sure the first row contains column headings that
match field names in the Customer table.
Note: The Customer table has a CustomerID column which is the primary key of the
table. A CustomerID value can have up to 10 characters and uniquely identify a customer. If your
contact list don't have this column, you need to add it.
Similarly, the Product table has a ProductID column which is the primary key of the table.
A ProductID value can have up to 10 characters and uniquely identify a product. If your product list
don't have this column, you need to add it.
(Click to enlarge)
- Open the database file in Microsoft Access.
Note: The following description and figures are based on Access 2003 and Excel 2003.
You can easily find the similar commands in the new Ribbon based interface in Access 2007 and Excel 2007.
- Click menu File -> Get External Data -> Import.
- Choose Microsoft Excel in the Files of Type box. Browse to and open the Excel
workbook that contains the contact list. This is the Import Spreadsheet Wizard:
(Click to enlarge)
- Click Next. Check the First Row Contains Column Headings option.
(Click to enlarge)
- Click Next. Choose In an Existing Table and select Customer from
the list.
(Click to enlarge)
- Click Finish. The wizard displays a message indicating the success or
error messages if anything goes wrong.
|
