Import or export products in Optimizely PIM

Overview

Use Imports to import product data and create product data in Optimizely PIM following your initial configuration of property groups, properties and product templates. The Product Import wizard guides you through each step of the import process. 

Save your field mappings and import options as Import Templates, so you can import files that follow the same format with ease in the future. After you map your fields, you can preview records in the import file and see what values the system will import. PIM keeps a log of all imports in order to access import details and resolve issues with any imported records.

Use the Imports section of PIM to:

  • Import many new products or update many existing products at one time
  • Create product import templates
  • Map fields on an import file to PIM fields or select an import template
  • Update an existing import template
  • View and triage any records that had issues during import
  • View the status and results of imports

 Accepted file types for import include Excel (.xls and .xlsx) and Comma-Separated Values (.csv).

You can also export your total products or a filtered product list from the products section of PIM.

Importing a product without a mapped product number creates a temporary product number.

B2B Commerce Storefront View

See Review How Optimizely PIM Data Appears to Customers.

Best Practices

  • Ensure your products exist in your ERP, so that customers can order them.
  • Refresh the products from your ERP to B2B Commerce of transactional product data like part numbers, titles, tax codes, weights, units of measure, and so on.
  • Create product records in PIM using imports of data from B2B Commerce, your ERP, suppliers or data feeds. 

    PIM supports attribute name/value pairs where the column header is generic and there are different attributes in the single column for different rows.

  • Import files must have column headers, which the import identifies as the source fields when mapping fields to PIM. You can set default or static values and use advanced function builders to use more complex logic to determine what values the system will import.
  • As a safeguard, PIM has built-in functionality to prevent import issues, such as:
    • You can limit access to PIM or only assign the roles of Administrator or Product Importer to users allowed to import product data.
    • You can configure whether to overwrite existing values as part of the mapping step during import.
    • You can preview records before importing to verify accuracy.

      If you find any issues with imported data, you can restore a previous version of a product, use the bulk or in-grid editing functionality in the product grid to correct the issues or export the data to make updates.

Definitions of import-related terms:

  • Source Fields. Fields derived from the header row of an import file that you map to target properties.
  • Target Properties. Product properties in PIM that you map to the source fields.
  • Static Value. A value that is treated as a source field when you map it to a target property for all records. All imported products will have this value set for the target property.
  • Existing Field. A field already included in the import file so you can map it to multiple Target Properties. 
  • Default Value. A value that is used as the default for records that have no value for the source field.
  • Name/Value Pair. Some import files will have attributes and style traits defined in columns with the values in the next column. You will need to map these files differently from the standard header/row import fields.
  • Function. A SQL function applied to data in the specified field before the system imports the value to the mapped property.

During the import, product records with errors are segregated and the products with no issues are imported. You can then review the errors, fix the data inline, then reprocess the product records without having to redo the entire import.

Use Case

Several main faucet suppliers provide product information via spreadsheets. Carrie needs to quickly import these products into PIM so she can work with them and fill-in the missing information she needs for the website.

  1. Carrie clicks the Imports icon on the task bar, then clicks the Import icon to start the import process.
  2. Using the Product Import wizard, Carrie clicks Choose File for the Initialization step to select a spreadsheet from one of her faucet distributors, ShinyFaucets. She leaves the Select Import Template field blank, as this is her first time importing from this distributor, and chooses comma from the Select Delimiter drop-down list.
  3. A preview of the file appears. She clicks Continue.
  4. For the Options step, Carrie selects Only add new records, since she knows these are all new products. She does not select the Assign all products to product template check box to then select a product template she created earlier because this spreadsheet includes both kitchen and bathroom faucets. She will have to assign product templates later.
  5. Carrie adds most fields to the Selected Fields area, but excludes the pricing columns, since those should be managed in their ERP. Then, she clicks Continue.
  6. Carrie selects the Hide mapped fields check box for the Mapping step, then maps all the listed Source Fields to the correct Target Properties. For example, she maps the ProductName field in the spreadsheet to the productTitle property. Once finished, Carrie clicks Continue.
  7. Carrie reviews the imported values for several records on the Preview step, to make sure her mappings are correct. 
  8. She clicks Save Import Template to save the mappings for this distributor's spreadsheet as ShinyFaucetsProducts so next time she imports their products she can select this Import Template and skip all of the setup.
  9. Carrie clicks Import to start the import process.

Import Products

Use the Product Import wizard to import many products at once.

Start the Import

This steps kicks-off your import and starts the Product Import wizard.

  1. Click Imports in the task bar. The Imports list page appears.
  2. Click the Import icon. The Product Import wizard appears.

Initialization Step

This step is where you choose your file, import template and delimiter.

  1. Click Choose File. Your file explorer window opens.
  2. Search for and select the file to import and click Open. A preview of your file appears on the page.
  3. Select an option in the Select Import Template drop-down list if you've saved an import template for the file you are importing.
  4. Select an option from the Select Delimiter drop-down list if you are importing a .csv file. A preview of a subset of the records displays.
  5. Click Continue.

Options Step

This step is where you indicate how to handle new and existing records, assign a product template and select properties from the source fields in the file you are importing.

  1. Choose if this file has name/value pairs. If so, you will be prompted to select the names and values from a drop-down list pre-populated with the column headers in your import file. When finished, click Save.
  2. Select an option for handling new or existing records:
    • Add or update records
    • Only add new records
    • Only update existing records
  3. Select the product template to apply from the drop-down list. If you selected an import template in the Initialization step, the product template selection may pre-fill.
  4. Select the check boxes next to the properties to include for the products in this import in the Available Fields area.
  5.  Click Add to move the selected properties or Add All to move all properties to the Selected Fields area. If you selected an import template in the Initialization step, the Selected Fields will pre-fill.
  6. Click Continue. Click Previous if you need to return to the previous page.

Mapping Step

This step is where you map your Source Fields (columns) from the file you are importing to the Target Properties in PIM. By default, the import will search your file and make best match guesses from the Source Fields to the Target Properties.

  1. Select the Hide mapped fields check box if you want to hide fields after you've mapped them to properties.
  2. Click Add Mapping to add an additional row to be mapped and click Save in the Add Mapping window. The options are:
    • Static Value. Add a static value to import a specific value for all imported records. For example, the vendor name.
    • Existing Field. Add an existing field to select a field already included in the import file so you can map it to multiple Target Properties. For example, you may want to map the product name to the product title field and also map it as the URL segment.
    • Name Value Pair. Add a name value pair mapping to allow you to select two fields for import as a name/value match. For example, attribute_name_1 and attribute_value_1. You do not map these to a target property.
    • Function. Add a function to create a SQL function to apply to the import file before the system imports the value to the mapped property. For example, concatenate field 1 and field 2 using the SQL function CONCAT({columnname1}{columnname2}). Click the Test button to apply the function to the first 10 rows of the import file and display the resulting values.
  3. Select a property from the Target Properties drop-down list for each Source Field. You can search to find a property if the list is too long to scroll. Each Target Properties field displays any unmapped properties under their property groups. If you chose a saved Import Template, these fields pre-fill with the mapped value from the template. You can also assign an existing Product Template to this product from this step.
  4. Click Edit for advanced options for a mapping or Delete to remove a field from the mapping list. The advanced options are:
    • Function – Apply a function to the imported data. For example, you may want to strip out spaces from the data using the SQL function Replace({ColumnName}, ' ', ''). Click the Test button to apply the function to the first 10 rows of the import file and display the resulting values.
    • Set default value – If no value exists in the import spreadsheet, this value will be used. 
    • Do not overwrite existing values – Use this if you want to import new values for this specific field but not overwrite existing values. This may be useful if you are curating the data within the PIM and do not want that data to be overwritten if the same product record is included in another spreadsheet. (this may be useful with data feeds)
    • Apply mapping to additional target properties
  5. Click Continue after you have mapped all the fields to properties.

Preview Step

This step is where you verify your data mappings and complete the product import process.

  1. Click Next to view the records and verify the Imported Value makes sense.
  2. Click Save Import Template and give your import template a name if you want to save these field mappings as a new template. If you made changes to an existing template, you have the option to replace the template or to save as a new template. You can then select this Import Template later when importing files from this same source.
  3. Click Import to start the import process.

View Import Progress and Logs

After starting an import, the Import History list appears with an Importing Products progress bar at the top of the page.

  1. View the current record the import is processing and the total number of records that are part of the import.
  2. Click Cancel to stop the import and change the status of the import to Canceled.
  3. Click OK to hide the progress bar after it turns green and displays the message "X product records have been successfully imported." when the import is complete. If there were import errors, click Details to view the import job details from the progress bar after it turns orange and displays the message "X product records have been successfully imported. Y product records could not be imported." then click OK to hide the progress bar.

Filter the Import History List

  1. Click Imports in the task bar. The Imports list page appears.
  2. Select or clear the Show successful imports check box. If selected, successful imports will be included. If cleared, only import jobs with in a status other than Success will show. Successful imports are excluded by default to help you find the import jobs that need attention.
  3. Click the Filter icon to enable or disable column filters. This adds a row below the column headers with options for filtering each column.

Troubleshoot import errors

Data validation occurs when you click Import at the end of the Product Import wizard.

  • Validation issue. Product records will not be imported if they have data for a field in the spreadsheet that fails validation based on the property definition of the target property.
  • Incomplete data. This will not cause an import to fail, but you will have to clean up your data following the import.
  • In grid editing. You can make changes to fields inline on the product grid to address errors for all products following the import.

Export Products

  1. Go to Products page.
  2. Click the Export icon in the upper right area.
  3. Choose the records you want to include: either the current filtered list or any currently selected products.
  4. Select the properties to include: either the current view or all properties that belong to these products.
  5. Choose your export file type: XLSX or XML. These are the only available file types at this time.
  6. Click Continue. The export window will appear in the lower right corner.
  7. You can close the window, but the export will continue running. Once the file has downloaded, it will appear at the bottom of the screen. Click the file to open and review your export.