Batch Import Database Items

Note: Only available to a PlanogramBuilder Administrator

Video tutorial on this topic: 9: Batch importing items in the database

 

If you have many materials, products or accessories to import to your PlanogramBuilder database, you can use the import commands to add or modify multiple items in one shot.

The Import L+I command lets you add or update items in the PlanogramBuilder database from a spreadsheet file on your computer. It also uploads the item image files referenced in your input file.

The Import L command does the same, but without uploading the image files. Any image column (image1, image2, image3, etc.) present in your input file is simply ignored.

Important Tip: As a starting point to prepare your Item List for import, we strongly suggest exporting the current database from PlanogramBuilder and then using the exported file as a template for importing. This is especially useful to easily get the proper header names if you have set the user interface to a language other than English.

Tip: Once you have successfully tested batch importing some items, we advise to Clear Database to remove the sample products and accessories provided by zVisuel before importing your own products.

Here are the instructions to prepare your data for import:

1. Create a spreadsheet with one line per material, product or accessory, and define a column for each item property labeled exactly as in Database Editor. To import values into your Custom Properties, the header of each column must match exactly the labels that you have set in the Settings (see Database Setup). A description of each available property is available in the section Database Item Properties.

Example of spreadsheet for import

Reference

Category

Brand

Product line

Name

Width

Height

Depth

Image1

Shape

Behavior

Color

Type

045631

Beverage

Coca-Cola

Zero

Coca-Cola Zero 500ml

6

20

6

045.png

cylinder

standard

#ff0000

054256

Beverage

Coca-Cola

Cherry

Coca-Cola Cherry 500ml

6

20

6

042.png

cylinder

standard

136853

Beverage

Sprite

Cherry

Sprite Cherry 330ml can

8

11

8

153.jpg

cylinder

standard

#6C0232

bjh002

Beverage

Coca-Cola

Coke shelf strip 80cm

80

2.5

0.3

002.jpg

box_front

shelf_strip

#6C0232

1

2. In order to import accessories or materials, an additional column with a header named Type must be present in the spreadsheet: if a cell in this column has the value accessory, the item will be imported as a accessory. A value of material is for materials. Leave this cell empty or enter product for items that are products.
3. Save your spreadsheet as an Excel file (*.xls or *.xlsx), or if you can’t save as Excel files from your application, save as a Unicode Text file (*.txt). (Note: legacy Tab Separated *.txt files are still supported as in past versions, but this format doesn't support non-Latin characters.)
4. Place all the image files to upload in the same folder as your spreadsheet File, or in any of its sub-folder(s). If your images are already in a folder on a different path on your computer, you can also create a shortcut to this folder in the folder where the Excel file is located. This way you don’t have to copy the actual image files to the required folder. PlanogramBuilder will automatically upload the referenced images to the database upon import and generate the 3D models using these images. Please refer to Guidelines for Images for further instructions.

Example of suggested folder structure for database import with images

5. In PlanogramBuilder, go to the Database task, click on the Import Import L+I button, then select the spreadsheet file you have prepared and wait for all items to be generated.
6. Your items are now shown in the Database list as well as in the Materials, Products and Accessories catalogs.

Warning: If you edit your spreadsheet in Excel, References (SKUs, Gencode or EANs) or other values expressed as long numbers or numbers starting with “0” may not display correctly, because Excel doesn’t support these numbers. In this case, as a workaround, you have two solutions:

• If you want to save the file as native Excel files (xls or xlsx), set the cells containing such numbers to text so your item Reference will be displayed correctly.
• If you want to save the file as Unicode text, you can add a number sign [#] as a prefix to an item Reference. PlanogramBuilder then automatically removes the first character upon import if it’s a [#] so your item Reference will be displayed correctly in PlanogramBuilder.

Ex: A 13 digit reference code 0235485217568 is not supported as a number by Excel. So change the containing cell to text and Excel will then display it correctly. PlanogramBuilder will therefore also display 0235485217568 in its database after import.

Notes:

Cancelling the import in progress: You can cancel the command Import L+I by clicking on its icon when a batch image upload is going on.

Column header names: The header name of each column in your spreadsheet must match exactly each available property in Database Editor.

Column header names for non-English languages: If you have set the language of the user interface to a language other than English, the names of the database properties are displayed in the language you chose. When importing, you must also name your source spreadsheet column headers as they appear in your language in Database Editor.

Non-Latin characters: You can use non-Latin characters for most text entries. For example, you can enter product names in Greek language. Please see Database Item Properties to find out which text columns support Unicode characters.

Column order: The order of the columns in your spreadsheet does not matter for importing, as the importer compares the header values to match columns.

Customizable properties: Please see the section Database Setup for help on defining the names of the 3 classification properties and the 3 custom properties in PlanogramBuilder.

Product dimensions: You can force a measurement unit for the dimensions of your items by adding the unit abbreviation within square brackets after the header. Here are the 4 possible options:

• No unit specified (example: Width). The unit used for import will be the current preferred PlanogramBuilder unit. See Measurement unit for details on this setting.
• [mm] (ex. header: Width [mm]). The product width will be interpreted as millimeters.
• [cm] (ex. header: Width [cm]). The product width will be interpreted as centimeters.
• [in] (ex. header: Width [in]). The product width will be interpreted as decimal inches.

Color column: Color values are specified using the corresponding HTML color code including the # sign. Please see the Database Item Properties for details on the Color property.

Type column: Please see the Database Item Properties for details on the Type property.

Rule to determine if an item will be added or modified

For each item listed in your spreadsheet, the Reference property value determines this:

• If an item Reference in your spreadsheet cannot be found in your PlanogramBuilder database, a new item will be created.
• If an item Reference in your spreadsheet already exists in your PlanogramBuilder database, the item will be updated to match the values from your spreadsheet.

Rules for missing columns

When you import an item, the only compulsory column in your spreadsheet is the Reference. If you omit other columns, PlanogramBuilder fills their values according to the following rules:

• If the item Reference already exists in the database and the missing column value is already defined there, the existing database value is kept unchanged.
• If the item Reference already exists in the database but the missing column value is not defined there, or if the imported item Reference doesn’t exist yet in the database, default values are applied for properties when applicable, otherwise values are left empty.

Example 1: Add new items to the database from scratch. Create a spreadsheet with properties Reference, Name, Category, Brand, Product Line, Width, Depth, Height, Image1 and any additional optional columns if you wish. Then use the Import L+I command.

Example 2: Update the Names of several items that are already in the database by simply importing a spreadsheet with two columns: one with the existing References and one with the new Names. Use the Import L command.

Example 3: Update several Database Item Properties such as Names, Category or Dimensions without re-uploading the item images. To accomplish this, use the Import L command. Any image column in your source file will just be ignored.

Rules for empty cells

When you import an item, if you leave some cells empty under supported columns, the corresponding existing database values are overwritten by default values if applicable, otherwise left as blank values. The same rule applies to new item References.

Example 4: Remove images from one or several products by importing a spreadsheet with blank cells under the Image1, Image2 and/or other ImageN columns. Use the Import L+I command.

Example 5: Update images of products by importing a spreadsheet with new images referenced in the Image1, Image2 and/or other ImageN columns. Use the Import L+I command. All images referenced in your file will be re-uploaded. Tip: If you only want to update images of some products, don’t put empty cells because this would remove the images from the corresponding products. Instead, create an input file with only the lines for products that need updated images.

Example 6: Import a spreadsheet with blank cells under the Collisions column to reset all existing item collision values to the default (Yes). Use the Import L command.

Example 7: Import new items from a spreadsheet with blank cells under the Behavior column. This will set all item behaviors to the default value (Standard for products, Panel for accessories). Use the Import L command.