Skip to main content
office-kit.com main content

Importing External Data

- Document for Excel Invoice Manager

 Share This Page

This version of the document, "Importing External Data", was written for Excel Invoice Manager, which was now renamed to Uniform Invoice Software. For the latest version of this document, please download Uniform Invoice Software.

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.

  1. Download the source data template.

    Click the following link to download the source data template: import_data_template.zip.

  2. Prepare the source data.

    Fill your existing data into the template.

    NOTE 1: Do not change the column headings. These headings automatically map to appropriate fields in Excel Invoice Manager database.

    NOTE 2: 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.

    NOTE 3: Do not leave any rows empty. To the Import Data Wizard, an empty row signals the end of the data. Subsequent rows will be ignored.

  3. 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/2010 and Excel 2007/2010.

  4. Click menu File -> Get External Data -> Import.
  5. 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:

    excel invoice manager - import data
    (Click to enlarge)

  6. Click Next. Check the First Row Contains Column Headings option.

    excel invoice manager - import data - 3
    (Click to enlarge)

  7. Click Next. Choose In an Existing Table and select Customer from the list.

    excel invoice manager - import data - 4
    (Click to enlarge)

  8. Click Finish. The wizard displays a message indicating the success or error messages if anything goes wrong.