How to import products in Excel to Cube-IQ

How to import products in Excel to Cube-IQ

Cube-IQ can import Products from an Excel sheet

Below is an Excel spreadsheet of products .

ProductId
Length
Width
Height
Weight
Description
Powder
19.13
13.13
11
36
Crate of powder
Chips
15
10.75
8.25
25
Corrugated chips
Metal
15.63
10.44
2.75
24
Box of cans

ProductId, Length, Width, Height, and Weight columns are required fields. ProductId must be unique. Length, Width, Height, and Weight must be a number. Description column is optional.

There are other optional fields that will be discussed later.

Copy/paste the spreadsheet above into Excel to save it as "Flexi Import.xlsx", and follow it in the examples below or as a template.

Use the Flexi Data Import feature in Cube-IQ to open an Excel file for importing

The Flexi Data Import window is where Excel spreadsheets are opened and imported into Cube-IQ.

The Flexi Data Import, or Flexi window for short, can be launched by: 1) click the main menu, 2) click Import & Export tab, 3) click Import Containers or Products from CSV/Excel



Open the Excel file in the Flexi window by: 1) click Open File, 2) Navigate to and select the Excel file, and 3) click Open




Here is the Excel file opened in the Flexi window. There are three dropdown lists:

  1. First dropdown list is the name of the Excel sheet "Products", there is another unselected sheet named "Sheet1" in the Excel file.

  2. Second dropdown list is for the type of data to be imported, "Import Products" is listed by default instead of "Import Containers".

  3. Third dropdown list instructs the import to either "Overwrite existing data" or "Overwrite only imported columns" or "Only add new data" or "Erase existing data in Load", this example uses the default "Overwrite existing data".




Map Product columns to Cube-IQ fields in the Flexi Import Data window

Excel sheet columns can be mapped to Cube-IQ fields and header rows can be skipped. 

Row 1 is row 1 in the Products sheet in the "Flexi Import.xlsx" file where column titles are, and rows 2, 3 and 4 are the products.
 
In this example there is only one header line in row 1, therefore the # Header rows in the spinner will be set to 1 instead of 3. This is important to ensure headers are not imported as products and no product rows are missed.
 
Here is the # Header set to 1, Row 1 now has a purple tinge as a visual to not import it. Row 2, 3, and 4 do not have a purple tinge.

 

The very top row, above row 1, have dropdown menus for each column. These dropdown menus lists the available Cube-IQ fields each column can be mapped to. Here is the top row of dropdown menus.



The dropdown menus in columns A, B, and C are mapped to the wrong Cube-IQ fields and columns D, E, and F have "Skip" selected meaning columns D, E, and F will not get imported.
 
All columns in this Excel sheet are Mapped using the Column dropdown menu: A) PRODUCTID, B) LENGTH, C) WIDTH, D) HEIGHT, E) WEIGHT, and F) DESCRIPTION
 
Here is the Flexi window below after the dropdown menus in the top row have been mapped to their respective fields. The dropdown menus have been left expanded to view other available mappable Cube-IQ fields.



PRODUCTID, LENGTH, WIDTH, HEIGHT, and WEIGHT Cube-IQ Fields are always required, the DESCRIPTION field is optional.

Mapped columns can be saved

Mapped columns can be saved and reloaded for future use to save time when another similar Excel sheet needs mapping again. Excel sheet must have header columns in the same order, otherwise column dropdown menus will need to be corrected after loading a mapping configuration file.
 
Save column mapping in Flexi Window by: 1) click Save Mapping Button, 2) navigate to a folder and enter a filename, and 3) click Save




Defaults values can be modified and saved

Default values are used for fields that are not mapped. MAXINLAYER and SIDEUPOK are two fields that are not mapped. MAXINLAYER Default Value is 999999, this high number ultimately means there is no limit to how many products can stack on each other. SIDEUPOK Default Value is True, this rule means Products can get loaded on their side.

The Change Default button let's you review and change default values if necessary.

Products in this import need the MAXINLAYER and SIDEUPOK Default Values changed. MAXINLAYER Default Value should be 7 instead of 999999. SIDEUPOK Default Value should be False to prevent products from getting loaded on their side.

Open the Product Default window to change defaults in Flexi window by: 1) Click Change Defaults, 2) change 999999 beside MaxInLayer to 7, 3) click True beside SideUpOk, and select False



Products imported will now only stack 7 high and will not be loaded on their side.

Boxes unchecked in the Default Value window are Cube-IQ fields that will be unavailable in the Column Dropdown Menus for mapping, however their Default Values will still be used in the Import.

Defaults Values can be saved to a configuration file. Saved configurations can be loaded again by clicking the Load button.

Save Default Values by: 1) click Save, 2) type a File name, 3) click Save in the Save As window, and 4) click OK to close the Default Value window

 

The Apply Default Values box must now be checked in the Flexi window after making changes in Product Defaults window.

Here is the Apply Default Values box checked.




Begin importing Products into Cube-IQ

Products can begin importing once Columns are Mapped, Number of Header rows set, and Default Values applied. Column Mappings A through F are also summarized in the lower panel of the Flexi window.


 
Complete the importing process from Flexi window by: 1) click Begin import button, 2) click OK when Import is complete, 3) review messages in bottom panel, and 4) click Close.








The bottom panel returns each product imported. The footer line of the Flexi also returns how many Products are: Imported, New, Updated, Deleted, and the number of Errors.
​

Review imported products in Cube-IQ

Navigate to the Products menu and click Overview on the ribbon to list the imported products. Product Id, Length, Width, Height, Weight and Description values are listed as per the Imported Excel sheet. Default Values for On Side and Max Stack are what their Default Values were saved as: OnSideOk is False, and MaxInLayer is 7









    • Related Articles

    • How to import Loads in Excel to Cube-IQ

      Cube-IQ can import Loads from an Excel sheet Below is an Excel spreadsheet of a Sample Import Load of Products included in the sample data when Cube-IQ is installed. Load ID Product Id Quantity Sample Import Load prd 1 60 Sample Import Load prd 2 40 ...

    • How to import Containers in Excel to Cube-IQ

      Cube-IQ can import Containers from an Excel sheet Below is an Excel spreadsheet of Sample Containers. Container Id Depth Width Height Weight Capacity Type Height to be Added Bottom to Top Compacting in Height Sample Medium Box 23 15 7 30 Box 0 FALSE ...

    • CUBE-IQ V5.X: Flexi Data Import Products & Containers

      1. Click on the "System Menu" icon in the top left.   2. Click on, "Import & Export" 3. Click on, "Import Containers or Products from CSV/Excel" 4. The "Flexi Data Import" window opens. Click on "Import Products", or "Import Containers" from the drop ...

    • How to import Custom Orientations

      Products can be imported from Excel to setup Custom Orientations instead of the regular Allowed Orientations Products imported from Excel have their Allowed orientation mapped by default to "All six". All six can be changed to "As defined", or to ".. ...

    • How to Limit the Total Value/Cost of Loaded Items

      Background It is possible to limit the total cost of loaded items into a container using Cube-IQ. With this, the value of the products loaded will not exceed the maximum allowable value of goods in the container. Let the software show the Client ...