Import Pre-Existing Spreadsheet Data
You have now created a workbook in BAU DB. Most probably you have some Excel spreadsheets holding rows of records in tabular format, or you have some data exported from other sources in spreadsheet format. You could easily convert these spreadsheet data to the newly created workbook and continue your work on BAU DB with a few simple steps.
Using MDB Viewer Plus
You can utilize the free tool, MDB Viewer Plus, to import the spreadsheet data into the database file. The first step is to open the database file, let’s say Book1.mdb, in MDB Viewer Plus:
Book1.mdb opened in MDB Viewer Plus:
Mapping Column Headers to Field Names
In this demonstration, we are going to import some invoice records from a spreadsheet file, invoice.xls, into the database file, Book1.mdb. The spreadsheet invoice.xls looks like this:
The Import Wizard uses the column header names in the first row of the spreadsheet to map with the data field names of the table. Therefore, we have to change the header names to the database field names first.
Since we are going to import the invoice record into the database table “a”, Tab “a” has to be selected in MDB Viewer Plus:
In MDB Viewer Plus, we use the Import Wizard to import the spreadsheet data. Click Data -> Import Wizard:
In the sample database file, Book1.mdb, the database field names are some single characters, “a”, “b”, “c”, …, which collide with the column names of Excel and confusions are made to MDB Viewer Plus when mapping the spreadsheet columns to the database fields. For other data field names, such as DocNo, the mapping will be correct.
It’s lucky that MDB Viewer Plus allows you to make adjustments manually for mapping the correct data fields. In the header of the wrongly mapped column, click the downward arrow to select the correct mapping field:
In this example, we select “Append” to add the imported records to the current set of records in the table, and the last step of the Import Wizard is to press “Execute” to move the records into the database file.
Using MS Access
If you have Access, you can also use its importing feature for importing the spreadsheet data. Like MDB Viewer Plus, Access treats the first row of the spreadsheet as column headers and maps these headers to the database field names. Therefore the conversion also starts off with the spreadsheet data updated with the database field names:
Open the database file, Book1.mdb, in Access and press the button in External Data -> Excel. Then follows through the below screens to import the spreadsheet data into table “a” :
Import Related Tables
Usually, a record entry involves more than one table. In the above example, the data in the spreadsheet invoice.xls are only the invoice header records, which is also corresponding to the single cells in the worksheet.
An Invoice entry also includes the multiple occurrence of product or service items which are stored in the multiple value cells. These records are stored in another spreadsheet or table. To complete the conversion of invoice data from spreadsheet, the invoice items also needed to import into the database.
Although Invoice Header records and Invoice Item records are stored in different tables, the two related tables are linked up by a common field – in this case DocNo (Invoice no.). In the worksheet of BAU DB, Invoice No. is the unique entry number and is stored in the common field, in this case DocNo, of both of the tables “a” and “a1”.
Having understood the data structures and the relationship between the two tables, the rest of the job is to also import the invoice item spreadsheet into the database table “a1” using the steps of import wizard in MDB Viewer Plus as shown above.