Importing products using Kentico Import Toolkit

Kentico Xperience Import Toolkit is a utility that allows you to import data from external sources into the system. As a source of data, you can use:

• XML files
• CSV files
• XLSX and XLSM files (MS Excel)
• MS SQL databases

With Kentico Import Toolkit, you are able to:

• Import products from an external source of data
• Change product properties using an external source of data

You can use this procedure to import products into an Xperience website from any of the listed sources of data.

Importing products from an external source of data

Taking preparatory steps

1. Prepare your external source of data, for example an XLSX file. Make sure that all required product properties are included.

   Out of the box, the product properties required for import are:

   • Name of the product – the same value for the DocumentName, SKUName and DocumentSKUName columns.

   • Culture of the product page – the DocumentCulture column.

   • Price of the product – the SKUPrice column.

   • ID of the site where the products are imported – the SKUSiteID column.

     If you do not set the SKUSiteID, the system assigns null to the property, which indicates global products.

     Then, if you do not enable global products in the Store configuration application, the system displays an error when browsing the products (The error description is: “The object does not exist. It may have been deleted by someone else.”)

     If this has already occurred in your Xperience instance, there are several options to fix the products:

     • Enable global products. However, this may not be desirable, for example in multi-tenant instances.
       -OR-
     • Change the products’ properties via Kentico Import Toolkit and set the SKUSiteID property.
       -OR-
     • Use an SQL script to update the SKUSiteID property. If you choose this option, make sure you stop the site when you run the SQL script.
     If you want to automatically assign products to their parent section (for example, to assign Mercedes-Benz S500 to the Cars section or Apple iPhone to the Cellphones section), add another column with their ParentNodeID property.

     You can find the Node ID property while editing the desired parent page in the Pages application on the Properties -> General tab -> Advanced section.

   There can be also other required properties depending on the product type which products will use (you choose the page type later during the import).

   

2. Launch the Kentico Import Toolkit <version> in Start -> Kentico <version> or execute the ImportToolkit.exe file located in <Kentico setup files folder>\Bin (typically "C:\Program Files (x86)\Kentico\<version number>\Bin").

This opens the initial screen of the Kentico Import Toolkit wizard.

Using the Kentico Import Toolkit wizard

1. Start a new import session.

   • Alternatively, load an import profile that you created during a previous product import.

2. In Step 1, specify the root folder of the administration project for the Xperience instance into which you want to import the products.

3. Confirm that you did a backup of your project.

   

4. Step 2: Select the object type that the system will use to store the imported data.

   • Select data type to import – Select Pages. (If you want to import the products just as SKUs (without their corresponding product pages), select Objects.)
   • Import data to site – specify the site to which you want to import the products.
   • Import as – select the product type that the system will use to store the imported products.
   • Import as products – Yes (selected) – confirm that the data is imported as products.
   • Import options:
     • Always insert as new pages – imports all the data. The Toolkit does not check whether the product is already in the system and adds it always as a new product.
     • Import new and overwrite existing pages – imports all the data. If a product is already found in the system (according to the column which will be specified in the following steps), the data from the external source overwrites the product data in the system.
     • Skip existing pages – imports just products which are not found in the system. Products found in the system (according to the column which will be specified in the following steps) will not be imported.

   

5. Step 3: Specify the source of the imported data. If you select a MS SQL database, enter your login credentials.

   If you specify a source of data different from an XLSX/XLSM or CSV file, the Kentico Import Toolkit wizard displays the following step accordingly.

6. Step 4 – an XLSX/XLSM or CSV file:
   Specify the row and column of the source file that the system will take as the beginning of the heading row of the imported data.

   
   Step 4 – a MS SQL database:Specify a table, a view or a query that will be imported to the system.
   
   Step 4 – an XML file:If the XML file contains more table names, select one of them.
   

7. Step 5: Preview the source of data.

8. Step 6: Check whether the automatically added source fields were correctly assigned to the target fields. You can also assign the source fields to other target fields.

9. Configure other important mappings:

   • Import the pages under following parent path or parent node ID – specifies where you want to place the imported products.
     • Enter the path (for example “/Products/Electronics/Cellphones”) if all products have the same parent node.
     • Type the ={%ParentNodeID%} macro expression, where ParentNodeID stands for the column in which you keep the parent node IDs of the products. The system then automatically places the imported products under different parent pages as specified in your source fields.
   • WHERE condition to find existing pages based on source data –determines which of the existing products the system skips or overwrites.
     • Hidden if you configured the Kentico Import Toolkit to always insert the imported products as new pages (in the Import options field few steps before).
     • Leave the field blank if the products are recognized according to their page GUID.
     • Type a SQL macro expression to update products according to a specific column.
       • If values in the column are only numeric, type SKUNumber = {%SKUNumber%}, where SKUNumber stands for the database column and {%SKUNumber%} is the macro expression, which stands for the column in your source data (e.g., an XLSX file).
       • If values in the column are strings, type SKUNumber = ‘{%SKUNumber%}’ (i.e. with inch symbols).
   • Save the old ID to new ID mappings to file – saves mappings of source identification fields to target identification fields into an .fkmap file. You can use this file when configuring field mappings during your later import sessions.

   

10. Step 7: Preview the target data.

11. Select whether you actually want to import the data or you want just simulate the import.

12. Click Import data to perform the import of the data from the source to your on-line store. The Kentico Import Toolkit informs you about the result of the import.

13. Close the wizard.

    • Alternatively, continue with one of the available scenarios (the Continue with section).

If you open the Products application, you will see the imported products in the specified section.

Changing product properties using an external source of data

You can also use Kentico Import Toolkit when you update your external source of data and want to import the changes.

Follow the same steps as when importing new products mentioned above with the following changes:

• In the external source, you need to include a column with a property according to which the system recognizes the products, for example SKUNumber.
• In Step 2 in the Import options field, you need to select Import new and overwrite existing pages.
• In Step 6 in the WHERE condition field, type a SQL WHERE condition with a macro expression to find the products, for example SKUNumber = ‘{%SKUNumber%}’, where SKUNumbermeans a database column and {%SKUNumber%} means a column in your source data.

After finishing, if you open the Products application, you will see the updated products.