Import updated prices from Excel

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.

New Import - Name and Purpose

  1. start New Import - Name and Purpose 
  2. Enter the name 'Update Prices'
  3. Select in group box 'Purpose of import' > Replace: replaces the values if a matching record is found'
  4. Press [OK]

In the Import an Validate form the Select Source Table tab gets focus.

Select Source Table

  1. In the combo box 'Select Source Table or Query', select the <Import Or Link Table>. 

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.

  1. Select 'Excel'
  2. Press button 'Start Wizard'

The Get External Data dialog opens

  1. Select radio 'Link to the data source by creating a linked table'
  2. Press [Browse...] to select the file you want to import from

The File Open dialog appears.

  1. Select 'ProductsUpdated.xls'
  2. Press [Open]

The File Open dialog closes and the selected file name appears in the The Get External Data dialog.

  1. Press [OK]

The Link Spreadsheet Wizard opens. In this case the worksheet the table is located in is already selected.

  1. Press [ Next]

The page appears where you can specify headings. The check indicating the first row is header is set, as we require here.

  1. Press [ Next]

The page appears where you can name the new linked table.

  1. Change the name to 'ProductsUpdated'
  2. Press [ Finish]

A dialog appears indicating the creation of the Linked table was successful

  1. Press [OK]

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.

Select Destination Table(s) 

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.

  1. Press button [Add destination table ...]

The [Select Destination Table] dialog appears

  1. Select the Products table.
  2. Press OK

In the transfer pane, an matching is done of Source and Destination fields.

  1. Make sure in the first row, in the 'New Value' column the value 'Replace' is selected.
  2. Change the matching (column Source) of row UnitPrice to IncreasedPrice.

You will see that the Old Value is 39, while the New Value after doing the replace of UnitPrice will be 39.78.

Import record

  1. Switch to tab Start Import.
  2. Press [Process Record] button to import the current record

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.