This tutorial shows how to import data from Excel and replace values on existing records.
As an example, you might want to export your products prices to Excel, to apply a formula that calulates new prices, and then want to import and replace the old prices with the new ones.
Note that we already created the Excel spreadsheet to import for you and named it ProductsUpdated.xls
It can be found in the MyImport demo database: Start > All Programs > Import and Validate > MyImports.mdb
Note: you are advised to have Excel closed when setting up the import.
In the Import an Validate form the Select Source Table tab gets focus.
A pop-up box appears with different types of data sources. It is here that we indicate we want to import from Excel. The reason for choosing Linked tables as a means of accessing the data in Excel is a technical one. It has the additional advantage of fast access to the indidual rows in the Excel table.
The Get External Data dialog opens
The File Open dialog appears.
The File Open dialog closes and the selected file name appears in the The Get External Data dialog.
The Link Spreadsheet Wizard opens. In this case the worksheet the table is located in is already selected.
The page appears where you can specify headings. The check indicating the first row is header is set, as we require here.
The page appears where you can name the new linked table.
A dialog appears indicating the creation of the Linked table was successful
Note: If you did not have Excel closed during the previous steps, at this point you may be getting messages indicating the linking is trying to resolve ODBC connection issues. Just cancel all of these.
The name of the linked table appears in the combo box. Controls appear to support importing multiple files, but we don't need that here.
Select Destination Table tab gets focus.
We now want to select the Destination Table, in our case table Products. The reason there is a button instaed of a selection combo box is that it is possible to distribute the Source records data over multiple destination records.
The [Select Destination Table] dialog appears
In the transfer pane, an matching is done of Source and Destination fields.
You will see that the Old Value is 39, while the New Value after doing the replace of UnitPrice will be 39.78.
The record UnitPrice change was applied, as can be seen if you press the [Back] navigation button . Going back shows the New Value 39.78 applied to the existing record, which now shows the Old Value is 39.78 - the original 39 has been replaced.