Mass upload Dimensions for Products via SQL


This article explains how to define Dimensional Shipping boxes quickly via SQL. There are three different ways to create a box in Dimensional Shipping, this article explains all of them.

It is assumed that you have knowledge of database administration and SQL.  Note: You can use csv format if preferred, but you still need some knowledge here.

1. Define Actual Boxes for products to Fit In

From under the Catalog/Manage Shipping boxes area, you can add different box definitions that can be used to assign products to each individual box listing.

Note: The following sql is for Dim Shipping 13.7 upwards. If you don’t have this please ask for an upgrade (assuming you are within support).

To save yourself the hassle of defining each box type and its applicable attributes; you can use an SQL statement, as follows:

INSERT INTO `boxmenu` (`boxmenu_id`, `title`, `length`, `width`, `height`, `multiplier`, `max_weight`, `packing_weight`, `volume`) VALUES

(1, ‘Small Box’, 6.0000, 3.0000, 2.0000, -1, -1.0000, 0.0000, (height*length*width)),

(2, ‘Medium box’, 11.0000, 6.5000, 4.0000, -1, -1.0000, 0.0000, (height*length*width)),

(3, ‘Large box’, 20.0000, 12.0000, 9.0000, -1, -1.0000, 0.0000,(height*length*width));

2. Assigning Product to a Single box

If you are using the exact packing algorithm then you need to assign products to boxes and specify the quantities that can fit.

If you are using best-fit packing algorithm then you merely need to set the dimensions of the product and assign the possible shipping boxes.

2a. Exact Packing – Assign products to Pre-Defined Shipping Boxes

To assign the Catalog/Manage Shipping Boxes to your products you will need to get the box IDs that you created in the previous SQL statement.

With the box IDs at hand, you can then assign the correct box types to your products with the following SQL statement:

INSERT INTO `shipusa_singleboxes` (`singleboxes_id`, `sku`, `box_id`, `length`, `width`, `height`, `max_box`, `min_qty`, `max_qty`) VALUES

(1, 1, 1, -1.0000, -1.0000, -1.0000, -1, 0, -1),

(2, 2, 3, -1.0000, -1.0000, -1.0000, -1, 0, -1);

The length, width and height attributes are populated with -1 values because the extension will pull the dimensional attributes from the box_menu table using the specified box_id.

2b. Exact Packing – Assign Products to Ship in a Custom Box 

If you wish to assign products to their own custom box type that isn’t defined in Catalog/Manage Shipping Boxes you can do so with the following SQL.

INSERT INTO `shipusa_singleboxes` (`singleboxes_id`, `sku`, `box_id`, `length`, `width`, `height`, `max_box`, `min_qty`, `max_qty`) VALUES

(3, 3, 0, 9.0000, 6.0000, 5.0000, -1, 0, -1);

Note that from the SQL statement above, the box_id for custom box types is “0” and the dimensional attributes reflected are those which we have defined for our custom box type.

2c. Exact Packing – Assigning Products to ship in USPS Flat Boxes

The format is the same as previous section, just use the table shipusa_flatboxes.

2d. Best-Fit and Largest Box Packing

Magento’s official documentation details how to export all products to a CSV. The required attributes are:  sku, ship_length, ship_width, ship_height and ship_possible_boxes

When the CSV has been created, assign the dimensions to the CSV file in the respective columns and follow the instructions to import.

If you do not have the attributes in the product attribute set it will not import.

If you have multiple possible boxes the additional boxes can be assigned on an additional row, as illustrated in this screenshot:

3. Assign Product to ship in Multiple Boxes

If you have products in your store that can be shipped in multiple shipping boxes these box types cannot be defined in Catalog/Manage Shipping Boxes and can only be defined on the product level of the item(s) in question.

For each box definition you will need to specify the number of boxes of each box type required for the item(s).

The following SQL statement illustrates how to specify multiple shipping boxes for the following rules:

  • ​SKU: BUNDLE-TEST-ITEM containing boxes:
    • 3 Cartons 33x19x17 actual weight 16 lbs each
    • 2 Cartons 36x18x5 actual weight 30 lbs each
    • 1 Carton 75x6x3 actual weight 16 lbs each

​INSERT INTO `shipusa_shipboxes` (`shipboxes_id`, `sku`, `length`, `width`, `height`, `weight`, `declared_value`, `quantity`, `num_boxes`) VALUES

(1, ‘BUNDLE-TEST-ITEM’, 33, 19, 17, 16, 0, 1, 3),

(2, ‘BUNDLE-TEST-ITEM’, 36, 18, 5, 30, 0, 1, 2),

(3, ‘BUNDLE-TEST-ITEM’, 75, 6, 3, 16, 0, 1, 1);

0 votes

Featuring WPMU Bloglist Widget by YD WordPress Developer