Defining Import Settings for Price Lists

eCatalog price list terms (commercial data) are imported into eCatalog price lists using standard Import Manager functionality.

After creating a price list object for your eCatalog, the next steps are to create a sample import file, in Excel or CSV format, then define the import settings for importing actual price data. The columns in the sample file will be used to map fields in the Import Manager wizard, which will allow the system to later ingest a sheet that contains actual data.

Sample Excel Price List Load File

The following two screenshots show examples of price list import files, one containing price break and quantity information, and the other one containing price break, quantity, unit, start date, and end date information.

Price Example

This example sheet is an Excel file that contains 10 columns and 8 products. Each product has three prices with price breaks that change based on the quantity of the product being sold.

For example, using the first product row shown in the above screenshot (ID = 121171), the price breaks are as follows:

Break No.

Quantity

Price

1

1 - 3

2.79

2

4 - 9

1.79

3

10 - 100

0.89

Note: To only import a price file with three prices, you must set the Minimum and Maximum values (even if they are fake values) to each of the prices. You cannot just load three columns of prices and nothing else. To load three price breaks, you must load nine columns of data. Unit, Start Date, and End Date are optional.

Prices with Units and Dates

To also import Unit, Start Date, and End Date values, which are optional, a corresponding column for each value must also be present on the sheet. In the below example—which shows columns for 'Price1' only—there is a 'P1Unit,' 'P1StartDate,' and 'P1EndDate' column. Each subsequent price (Price2, Price 3, and others) will also have its own unit, start date, and end date columns.

Note: When importing units, the STEP ID of the unit must be used and not the symbol. In the below example, iso4217.unit.USD is the STEP ID for U.S. dollar ($).

Creating an Import Configuration for a Price List

The following steps for creating a price list import configuration use a sheet that contains columns for all available price list (commercial terms) elements: value, unit, quantities, and dates. These steps assume that you have already created a sample file. Throughout the Import Manager, the elements within the price list (terms) are referred to as commercial data.

  1. In the Tree, expand the relevant eCatalog.

  2. Select and right-click the price list that you want to import prices to, and then click Edit import configuration.

  3. The Import Manager displays on the Select Data Source screen.

    Note: All steps of the Import Manager wizard, and the options available for each step, are described in detail in the Creating a Data Import topic of the Data Exchange documentation. The information in this topic only contains detailed descriptions of the steps that are of particular importance to commercial data.

  4. On this screen, select File. The data source will always be File for commercial data import configurations.

  5. While still on the Select Data Source screen, click the ellipsis button () next to the Filename field and choose the sample upload file, then click Next.

  6. On the Select Format screen, the format will be prepopulated with either Excel or CSV, depending on the format of your sample sheet.

  7. Make any additional configurations ('Has Header,' 'Trim whitespace,' 'Use date and number formatting from sheet'), then click Next.

    Note: It is strongly recommended to keep the default selection of 'no' for 'Use date and number formatting from sheet', especially if importing date values. This ensures that date values are standardized into an ISO format. Refer to the Excel Format topic of the Data Exchange documentation for more information.

Mapping Commercial Data in the 'Map Data' Step

In the Map Data step, a number of mappings must be specified manually. The 'Auto Map' feature will not work for commercial data mappings, though it can be used to map the ID or Name columns. ID is required.

  1. To start the mappings, click a source column, then click Map. The Map Column to dialog displays.

  2. Select the column to map, then click Map. The Map to dialog displays.

  3. In this example, the first column has already been mapped to ID; the first price column, 'Price1,' has been selected; and the Commercial Data radio button has been selected in the Map to dialog.

  4. The name of the price list will display in the Data Set field.

  5. From the Group list, select the preferred quantity break.

  6. Choose one of the following options to determine which field of the commercial list that the data from the Excel sheet should be mapped to:

    • Select Value to map the column to the value field.

    • Select Unit to map the column to the unit field and have the unit display along with the term value.

    • Select Min. Quantity to map the column to minimum quantity in the selected group or price break.

    • Select Max. Quantity to map the column to maximum quantity in the selected group or price break.

    • Select Start Date to map the column to the start date when the price becomes valid.

    • Select End Date to map the column to the end date of the selected group or price break.

  7. Check the Mandatory box if you want rows without a term value to be skipped on import so none of the information that may be related to a product without a specified term value will be imported. Click OK.

  8. Follow the previous steps until all columns of the sheet are mapped. For example, in this sheet, after mapping the 'Price1' column to Commercial Data > Group: 1 > Value, the remaining columns will be mapped as follows:

    • The 'P1min' column will be mapped to Commercial Data > Group: 1 > Min Quantity

    • The 'P1max' column will be mapped to Commercial Data > Group: 1 > Max Quantity

    • The 'P1Unit' column will be mapped to Commercial Data > Group: 1 > Unit

    • The 'P1StartDate' column will be mapped to Commercial Data > Group: 1 > Start Date

    • The 'P1EndDate' column will be mapped to Commercial Data > Group: 1 > End Date

    • The 'Price2' column will be mapped to Commercial Data > Group: 2 > Value, and so forth.

    • The remainder of the columns will follow the same pattern.

  9. Click Next when finished.

Completing the Import Configuration

  1. On the Identify Objects screen, if the products in the source file are not identified as existing products in the system, the 'Match Result' will be 'New.' Click Next.

  2. On the Identify Destination screen, several options are available, which are detailed at length in the Import Manager - Identify Destination topic in the Data Exchange documentation. For the purposes of commercial data imports:

    • Reject Updates should be left unchecked, since a setting on the Advanced Settings screen of the wizard will control how commercial lists are updated.

  3. On the Select Business Rules screen, business rules can be specified to run upon import if required for your business needs. For more details, refer to the Import Manager - Select Business Rules topic in the Data Exchange documentation. Click Next.

  4. On the Advanced Settings screen, any options can be selected based on your business needs, but the only options relevant to commercial data appear in the Replace/Update Options area.

    • To delete all existing content of the commercial list(s) and replace it with the content of the new file, select 'Replace entire existing list(s) with content of import file'.

    • To keep the existing content of the commercial list(s) and only replace the commercial data of the product IDs included in the new file, select 'Only update commercial data for objects included in import file'.

    For more information on all options available on the Advanced Settings screen, refer to the Import Manager - Advanced Settings topic in the Data Exchange documentation.

  5. Click Finish to complete the wizard. The Save Import Configuration dialog displays. It is recommended to save the import configuration and check 'Don't import now' in case changes need to be made to the configuration before importing live data. Click OK.

    Refer to the Running a Data Import topic in the Data Exchange documentation for information on how to save the import configuration.

  6. The price list import configuration is saved in the specified classification structure as an XML file of the object type Import Manager Configuration (stibo.ImportManagerConfig).

  7. A price list can now be imported. Refer to the Importing Prices topic, for details.

Editing a Saved eCatalog Import Configuration

To make changes to an existing eCatalog import configuration, do the following.

  1. Right-click on the price list and select 'Edit import configuration'.

  2. The Import Manager wizard displays, starting on the 'Select Data Source' screen.

  3. Make the desired edits following the same steps listed above for creating a new configuration.

  4. Alternatively, you can edit the configuration by right-clicking on the saved import configuration object in the classification hierarchy. Refer to the Maintaining a Saved Import Configuration topic in the Data Exchange documentation for details.