Overview
Cube-IQ is able to import containers from an Excel spreadsheet. Let's take a look at how this can be done.
Requirements
You must have any currently supported version of Cube-IQ installed on your computer with a valid license, or be logged in to Cube-IQ Web and have access to Microsoft Excel.
Procedure
Below is an example table 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 |
FALSE |
Sample Medium Pallet |
48 |
40 |
88 |
2000 |
Pallet |
5.5 |
TRUE |
TRUE |
Sample 20' Rectangle |
237 |
92 |
94 |
62380 |
Rectangular |
0 |
FALSE |
FALSE |
The first 4 columns are required fields. Container Id must be a unique string. Depth, Width, and Height are required and must be a number. Weight Capacity is also required but if left blank will be 9,999,999.
The Type column is not required, it will be Rectangular if Type column is blank.
The Bottom to Top column is not required. It is for the container's loading direction. It is a boolean, TRUE means Products load from bottom to top and FALSE means back to front. It will be FALSE if left blank.
The Compacting in Height is not required. It is also a boolean. TRUE means Container is compacted in height and FALSE compacts along container's length. It will be FALSE if left blank.
You can copy and paste the spreadsheet above into Excel to follow the example below or as a template.
The Type column is not required, it will be Rectangular if Type column is blank.
The Bottom to Top column is not required. It is for the container's loading direction. It is a boolean, TRUE means Products load from bottom to top and FALSE means back to front. It will be FALSE if left blank.
The Compacting in Height is not required. It is also a boolean. TRUE means Container is compacted in height and FALSE compacts along container's length. It will be FALSE if left blank.
You can copy and paste the spreadsheet above into Excel to follow the example below or as a template.
Use the Flexi Data Import feature in Cube-IQ to open an Excel document 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 clicking on:
The Flexi Data Import, or Flexi window for short, can be launched by clicking on:
- Main menu icon
- Import & Export tab
- Import Containers or Products from CSV/Excel
Open the Excel file in the Flexi window by clicking on:
- Open File
- Navigate to and select the Excel file
- Open
Here is the Excel file opened in the Flexi window. There are three dropdown lists:
- First dropdown list is the name of the Excel sheet "Containers"
- There are 3 other unselected sheets in the Excel file named:
- Loads
- Products
- Sheet1
- There are 3 other unselected sheets in the Excel file named:
- Second dropdown list is for the type of data to be imported
- "Import Products" is listed by default instead of "Import Containers"
- Third dropdown list instructs the import to do one of the following:
- Overwrite existing data
- Overwrite only imported columns
- Only add new data
- Erase existing data in Load
- This example uses the default "Overwrite existing data"
Map Container 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 Container sheet of the "Flexi Import.xlsx" file where column titles are, and rows 2, 3, and 4 are the containers.
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 4. This is important to ensure headers are not imported as a Container.
Here is the # Header set to 1, Row 1 now has a purple tinge as a visual to not import it. Rows 2-4 do not have a purple tinge.
Row 1 is row 1 in the Container sheet of the "Flexi Import.xlsx" file where column titles are, and rows 2, 3, and 4 are the containers.
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 4. This is important to ensure headers are not imported as a Container.
Here is the # Header set to 1, Row 1 now has a purple tinge as a visual to not import it. Rows 2-4 do not have a purple tinge.
The "Skip" in each of the columns means no Cube-IQ fields are mapped to any of the columns.
Click on a dropdown menu to select the appropriate Cube-IQ field to map for each column. Column A is CONTAINERID in this example.
Here are all the columns' dropdown menus mapped to the appropriate Cube-IQ fields. Column Mapping are automatically listed in the the lower panel after mapped field is selected in the the dropdown menu.
Column A to E mappings to Cube-IQ are straight forward. A maps to CONTAINERID, B maps to DEPTH, C maps to WIDTH, D maps to HEIGHT, and E is MAXWEIGHT.
Column F's "Type" maps to Cube-IQ's TYPE field. There are different Container Types in Cube-IQ such as Rectangular, Truck/Trailer, Pallet, and Carton. The example above will import 3 of these types: 1) Box, 2) Pallet, and 3) Rectangular
Columns G's "Outside Height" in this example is the height of the container's floor; therefore, the mapping for this column G in Cube-IQ is OUTSIDEEXTRAHEIGHT. Cube-IQ's OUSIDEEXTRAHEIGHT field is used for multistage Loads where a container's height is taken into account in the next stage. Only the Pallet has a value in column G, the other containers have 0. The pallet has a value of 5.5 inches, this means 5.5 inches is added to the height of the loaded pallet in the next stage.
Column H's "Bottom to Top" maps to Cube-IQ's BOTTOMTOTOPLOADING. BOTTOMTOTOPLOADING expects a TRUE or FALSE value. TRUE means container is loaded from the bottom to top, while FALSE means the Container is loaded from the back to front.
Column I's "Compacting in Height" maps to PARTIALLOADONFLOOR. PARTIALLOADONFLOOR expects a TRUE or FALSE value, TRUE means compacting in height, while FALSE means compacting along container's length.
Column F's "Type" maps to Cube-IQ's TYPE field. There are different Container Types in Cube-IQ such as Rectangular, Truck/Trailer, Pallet, and Carton. The example above will import 3 of these types: 1) Box, 2) Pallet, and 3) Rectangular
Columns G's "Outside Height" in this example is the height of the container's floor; therefore, the mapping for this column G in Cube-IQ is OUTSIDEEXTRAHEIGHT. Cube-IQ's OUSIDEEXTRAHEIGHT field is used for multistage Loads where a container's height is taken into account in the next stage. Only the Pallet has a value in column G, the other containers have 0. The pallet has a value of 5.5 inches, this means 5.5 inches is added to the height of the loaded pallet in the next stage.
Column H's "Bottom to Top" maps to Cube-IQ's BOTTOMTOTOPLOADING. BOTTOMTOTOPLOADING expects a TRUE or FALSE value. TRUE means container is loaded from the bottom to top, while FALSE means the Container is loaded from the back to front.
Column I's "Compacting in Height" maps to PARTIALLOADONFLOOR. PARTIALLOADONFLOOR expects a TRUE or FALSE value, TRUE means compacting in height, while FALSE means compacting along container's length.
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 doing the following:
Save column mapping in Flexi Window by doing the following:
- Click Save Mapping button
- Navigate to a folder and enter a filename
- Click Save
Begin importing Loads into Cube-IQ
Containers can begin importing after columns are mapped and # Header rows is set. Column mappings for A, B, C, D, E, F, G, H, and I are also summarized in the lower panel of the Flexi window.
Complete the importing process from Flexi window by clicking on:
- Begin Import
- OK
- When Import is complete:
- Review messages in bottom panel
- Click Close
The bottom panel returns Containers imported. The footer line of the Flexi also returns how many containers are: Imported, New, Updated, Deleted, and the number of Errors. There are 3 new containers imported.
Review Imported Containers in Cube-IQ
Navigate to the Containers menu, and open each Container. Each Container's dimensions are imported as per the Excel sheet. The Loading direction and compacting have also been imported. For example, the 20' Rectangle loads from back to front along length and the Medium Pallet loads bottom to top in height.
Here are the three imported containers. The yellow highlight Container's Dimension and Weight, while the orange highlights are the loading direction and compacting.
The Sample 20' Rectangle Container is imported as loading back to front and compacting along length.
The Sample Medium Box is imported as loading back to front and along its length.
The Medium Pallet is imported as loading bottom to top and compacting in height.
Assistance
If any further assistance is needed, please contact our Support team.