reinnovate database

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.
uploading data from spreadsheet to work on bau worksheet.

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:
database file 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:
invoice record in spreadsheet
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.
change the header names to database field names

Import Spreadsheet

Since we are going to import the invoice record into the database table “a”, Tab “a” has to be selected in MDB Viewer Plus:
select table
In MDB Viewer Plus, we use the Import Wizard to import the spreadsheet data. Click Data -> Import Wizard:
use import wizard to import spreadsheet data.
select excel file for import
select the import file invoice.xls
select data import options
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.
incorrect mapping of column to database field
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:
mapping of column to database field
corrected mapping of column to database field
just click next in this screen
add some criteria here to load or skip some records.
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.
select append the import records to the current records in the table
successfully imported the records.

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:
invoice record in spreadsheet
database field names mapped to the column headers in the spreadsheet.
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 using Access - step 1
import using Access - step 2
import using Access - step 3
import using Access - step 4
import using Access - step 5

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.
invoice items are stored in the multiple cells of the Invoice 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.
the invoice item records in the spreadsheet
the invoice item stored in another table
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”.
the invoice item records and header records share the common field of Invoice No.
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.