Import External Data - Invoice Manager for Excel
Introduction
Invoice Manager for Excel allows you to import external customer and product lists.
It is highly recommended to import your existing data into the existing Invoice Manager for Excel database tables. This makes sure the table structure and field definitions meet the requirements, including primary key definitions and indexes. It is not recommended to delete the existing database tables and create your own versions -- this will likely break down Invoice Manager for Excel.
By default Microsoft Access imports 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 Invoice Manager for Excel.
Steps
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.
- Download the source data template.
Click the following link to download the source data template: import-data-template.zip.
There are two Excel files inside the import-data-template.zip package, product.xls and customer.xls. Each contains a worksheet for you to fill in your existing data, and a "Tips" worksheet that explains key data types.
- Prepare and fill your existing data (i.e. the source data) into the templates.
NOTE 1: Do not change the column headings. These headings automatically map to appropriate fields in Invoice Manager for Excel database.
NOTE 2: The Customer table has a CustomerID column which is the primary key of the table. In most databases a CustomerID value can have up to 30 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 30 characters and uniquely identify a product or service item. 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.
- Open the database file in Microsoft Access.
NOTE: The following description and figures are based on Microsoft Access 2013 and Excel 2013.
- Click the Excel command in the Import & Link
group on the External Data Access ribbon group.
This opens the Get External Data - Excel Spreadsheet wizard.
- Click the "Browse" button to browse to and open the source data file that you filled into our templates "customer.xls" or "product.xls".
Choose the correct table name in the "Append a copy of the records to the table" section. If you're importing a customer list, then choose "Customer"; if you're importing a product list, choose "Product".
In this sample, we choose to import a customer list.
Click "OK" to go to the next step.
- The wizard asks which worksheet to import into the Access database file. In this case, we choose the "Customer" worksheet and then click the "Next" button.
- The wizard automatically checks "First Row Contains Column Headings". Click "Next".
- The wizard shows a summary of the options specified. Click "Finish" to start the actual importing process.
- Once finished, the wizard asks if you want to save the import steps. You could usually ignore this by simply clicking "Close" without checking "Save import steps".
One common error the wizard shows during the import process is key violations, which means there are duplicate values in the source and destination. The "key violation" error message looks like the figure below, which is produced by importing the same source list twice.
Please check the "Tips" worksheets in the "customer.xls" / "product.xls" templates to find out how to prepare the your customer and product lists to avoid possible errors.