Import product data (censhare Web)
Import product data from an external source or supplier via a Microsoft Excel file into censhare. The import is creates Product assets and Product item assets with the respective product properties in the censhare PIM application.
Context
The export is carried out in censhare Web, in a Product Information Management (PIM) application.
Prerequisites
You should be familiar with the product data structure of your censhare PIM.
Introduction
To exchange product data between censhare PIM and third-party systems or customers, you need to import the data to you censhare system. Microsoft Excel is a widely used, human-readable and machine-readable format. For example, if technical specifications or prices of your products change, customers, retailers or partners send you the updated data in a Microsoft Excel file which you import to your censhare system.
censhare PIM uses Pentaho PDI to import data from Excel. Pentaho PDI is an Extract, Transform, Load (ETL) processor from Hitachi Vantara. Based on a predefined schema for product and product item data, Pentaho transforms and splits the data into individual data records and passes them to censhare.
The import creates new Product assets and Product item assets with the respective properties in censhare. If censhare identifies existing assets that match the imported data records, it updates these. For more information see Update existing assets.
Importing product data requires an import schema. For a successful import, the source file must conform with the import schema. The import schema also defines how the data structure of the import file is transformed and split in order to match the censhare data model. You can use multiple import transformations with one configuration. This article describes the default import from Microsoft Excel. Other import formats can be available in your system.
Starting an import extracts the data from the uploaded Excel file and transforms it and splits the data into individual records. These records create new assets or update existing ones.
Import product data
Start an import from any asset page, asset list or static page. If you update assets, you do not have to select the assets you want the data to import. censhare identifies the assets to be updated automatically.
In the Page actions menu or Asset actions menu, respectively, select Import with Pentaho item. This is the default name of the action. The name can be different in your censhare system:
Icon
Action
Opens the asset menu or actions menu, respectively.
Opens the connector import dialog
Drag the Excel file with the data that you want to import to the drop zone and release it. Alternatively, click in the drop zone to open the file browser of your computer and select the file in the dialog.
In the Import with Pentaho dialog, select an import schema. The selected option must correspond to the data structure of the source file. The following options are available in the censhare standard configuration:
Pentaho imports products and product items from one Excel sheet
Pentaho imports products and product items from separate Excel sheets
Do not select the Use test data supplied with transformation. If it is preselected, deselect it before you start the import. Otherwise, censhare imports test data instead of the production data.
To start the import, click NEXT. censhare confirms the execution with the message Server action successful - Pentaho import. If an error occurs during the import, censhare aborts the import. Data that have been imported yet are not deleted. An error report is shown in the dialog field. For more information, see Troubleshooting.
To see the newly created assets, go to the Last created page in the side navigation. To see the updated assets, go to the Last edited page in the side navigation.
Update existing assets
During an import, censhare checks for existing assets and updates these assets instead of creating new assets. In the standard configuration, censhare uses the following properties as unique identifiers. They can be customized in your system:
Sorting | Property | Description | Source |
---|---|---|---|
1 | id | This is the internal ID of an asset. The ID is unique within a censhare instance. Use the ID as unique identifier only for "round-trip" data exchange, i.e. exporting data from censhare and back into censhare again. | censhare generates the ID automatically when you create an asset. For example, 4652375. |
2 | uuid | This is the universal unique identifier of an asset. The UUID is unique over all platforms and systems. | censhare generates the UUID automatically when you create an asset. It uses the MAC address of the device and the timestamp. For example: 4652375.ac3fb6b0-6035-11e8-aa39-fae17964e71d. |
3 | externalId | The external ID is used as a unique identifier in the data exchange of censhare through integrations and other interfaces. | The external ID can have different patterns. The external ID is usually generated on the system that is connected to censhare. If the field is empty, censhare generates an external ID that uses a default pattern. |
4 | gtin | The Global Trade Item Number (GTIN) is an identifier for trade items. It is part of the GS1 classification system. The GTIN is unique over different databases and organizations. | GTINs come from an external source. For example, a PIM system from a customer or supplier. |
5 | ean | The European Article Number (EAN) is another standard used to identify trade items. Its encoding is subsumed in the GTIN standard. | EANs come from an external source. For example, a PIM system from a customer or supplier. |
6 | orderNumber | Order numbers can be used additionally in your business environment. Order numbers, other than GTIN or EAN, are not globally unique. They are only unique for a particular PIM provider. | Order numbers come from an external source. For example, a PIM system from a customer or supplier. |
censhare checks in the defined order if one of these properties exists in the imported data sets. First, it checks the ID, next the UUID, and so on. It then compares the values with the censhare data base (cdb). If two values match, censhare updates the existing asset. If none of the properties are found in the imported data, or if values do not match with existing assets, censhare creates a new asset.
The following table shows the matching algorithm that results in an update of an existing asset:
Property | ID | UUID | External ID | GTIN | EAN | Order number | Result |
---|---|---|---|---|---|---|---|
ID | match | skipped | skipped | skipped | skipped | skipped | update asset |
UUID | no match | match | skipped | skipped | skipped | skipped | update asset |
External ID | no match | no match | match | skipped | skipped | skipped | update asset |
GTIN | no match | no match | no match | match | skipped | skipped | update asset |
EAN | no match | no match | no match | no match | match | skipped | update asset |
Order number | no match | no match | no match | no match | no match | match | update asset |
all the above | no match | no match | no match | no match | no match | no match | create new asset |
Notes
(1) If two properties match, the following ones are not checked. For example: The IDs of an imported data record and an existing asset match. The data record does not contain a UUID and an External ID. The GTIN and EAN do not match. The asset is updated, censhare overwrites GTIN and EAN with the values from the import data.
(2) The matching algorithm provides a common schema that can be used for different import formats. The six standard keys cover most use cases from external PIM providers. Customize the schema only if your specific use case is not applicable.
Result
The selected product data is available in censhare. New Product assets and Product item assets are created. Existing assets Product assets and Product item assets are updated.
Troubleshooting
Error | Reason | Solution |
---|---|---|
No assets are created during an import. | The selected import option and the Excel file configuration do not match. | Repeat the import and select the appropriate import option in the dialog (see step 3). |
An error message "JSON mapping import failed..." is shown during an import. | The data type of one or more cells in the source file cannot be transformed into JSON. | Open the source file in Microsoft Excel or another application. Check if mandatory fields are filled and if the cell formatting is set to "Default". |