Magento: How to create an international shipping table.

In my last post I explained how to setup a table of flat rates for shipping.  Magento calls this table rates, but whatever you call it you will have to create a table of values to use to calculate shipping.  In this post I will talk about how to create that table using country codes so that you can assign shipping rates for international shipping.  You will need to do this because you don’t really want to charge the same shipping for the country where you live, and somewhere much farther away.

For me, I am in the USA, so I will have domestic rates for USA, a set of rates for Canada, and then I just threw out a bigger rate for all other international shipping.  I don’t really have a market there, but if someone wants to pay it, then I will want to sell to them but not have shipping eat up all of my margin.

And I want to use a table rate as I have a variety of vendors that don’t all charge the same for shipping.  I am averaging their costs to me, and providing a simple, single shipping cost to my customers.

Making sure your online store is charging for shipping effectively is just like any other investment in your business.  Sometimes online retailers don’t think about these types of investments and then end up scrambling as they grow. I was thinking about this the other day when I was in our shipping department. Simple things like investing in good quality commercial shelving units like these parts storage systems from a trusted company can make a big difference as you grow and make your fulfillment process efficient.

Country Codes:

First we need to get the country codes to use for our table.  These codes will be the first column of data.

I got a list from here:  http://en.wikipedia.org/wiki/ISO_3166-1_alpha-3

I just cut and past them into a spreadsheet and then took only the country code column and made it into a text file for my script. I called the file countrylist.txt. And I moved it into the same directory where I made my script.  Since this is not something that you will be running every day, I just left it mostly primitive.

You can also get a list of countries from the Magento DB if you know where to look.  Here is a query that will pull that table.

SELECT *
FROM directory_country

You will want to use the iso2_code for your list of country codes.

Writing the Script:

Now you will need to write a script (I use perl) that will read the list of country codes, and creates a csv file in the right format to be uploaded to Magento.  The CSV file will have one or more lines for each country depending on how many shipping tiers you have.  My tiers are based on order dollar value, and country destination.  But you could do the same thing with a weight based table you would just need to modify the script to fit that format.

Here is a picture of my script that you can use if you like.  It is pretty primitive, but it does the job.

Things to note about this script and process:

  • (As I said, in my countries list I removed USA and CAN from and have to add those rows manually to have separate shipping charges for domestic shipping and Canada.)
  • (I also removed ROU, as Magento does not know what that country is.  So no selling to Romainia.  But if you get your list from the Magento DB you won’t have to remove anything, but you will still be missing this country.)
  • (And don’t leave a blank line at the end our you will have a blank country.)

Results

Running the script will create a table that looks like this:

If you need to create separate shipping rates for a single country you can edit the file for that country.  And in a case where you want to adjust shipping for sub-country regions, you can add lines for a country with states or zip codes  to give those areas special shipping rates.

If you want to make groups of shipping rates, you can separate the country codes in the countrylist.txt file and edit the script accordingly.  Or you can make a more complex listing of values to loop through and really edit this script to make a really complex table and set of tiers.  I kept this example simple  on purpose.

Of course, if you create multiple csv files, you will need to merge the files into one tablerate.csv file to upload to Magento.  Magento only takes one upload as the complete shipping table.  If you get crazy, you may want to create a script that will merge your multiple shipping tables into a single file.  But really this is just a CSV file and we probably won’t ever need to do that far.

Remember.  Uploading this table will replace the current table.  This does not just add the new rows or rules.  So you will have to update your table of shipping rates all at once.

Related Articles:

Posted in Magento, Perl Tagged with: , , , , , , , , , , ,
11 comments on “Magento: How to create an international shipping table.
  1. Very awesome article! Truely.

  2. Tom says:

    I want to copy another table rates just like the one in magento . What can I do ?

  3. Roy says:

    Tom,

    There are two approaches that I can see to quickly trying to do this.

    First, if you really want two table rates, then what you are talking about is building a table rates extension that will give you another shipping method with the same features but with a separate set of controls and options.

    This is not something that I have seen. And it seems a bit messy to try and move the existing table rate functions to local and rename them.

    Second is to get one of the shipping extensions like matrix rate and just use it like the table rate. It will be similar to the table rate with a few changes in structure of the csv file. And it is probably a better solution in the long run.

    If you need additional help, send me a private message at [email protected] and I will see what else I can come up with.

  4. Hai Roy,

    Thanks for this info! I downloaded the matrix rate extension and this works fine..only a lot of work to set up everything.

  5. Eddie says:

    Which format should be used for REGION/STATE abbreviations in countries other than US/Canada (format for the STATE, NOT the country) ?

    I tried the two-letter common (and official) abbreviation and also the full state name. Neither worked:

    0 records have been imported. See the following list of errors for each record that has not been imported: Invalid Region/State “BlahBlah” in the Row #2.

  6. Roy says:

    Great question. To find the answer to this I again would direct you to a query of the Magento Database.

    There should be a table like the directory_country. This one is called directory_country_region. There are 12 countries in this table. “Select distinct country_id from directory_country_region” to see the list of country codes that have region information in Magento by default.

    Getting the region codes for France would be executing a Query like this:

    SELECT *
    FROM `directory_country_region`
    where country_id = ‘FR’

    If you run this query, you will see that in France the regions_codes are numeric. That is what I would use into the import CSV file.

    If your country is not listed in this list of 12, you can be brave and insert your own region, with your own codes and names.

    Hope this helps.

  7. Eddie says:

    thats what I had to do (insert). thank you, roy!

  8. alex says:

    Hi,i would like to ask how to create international shipping table so people from different country could see their shipping cost.I choose paypal as my receiving payment menthod.Thanks help.

  9. Roy says:

    Alex,

    In Magento, a customer can see their shipping costs when they get their shipping quote. This can be done with any of the methods of shipping calculation, flat rate, table rate, usps, ect.

    On the shopping cart or checkout page, the customer can enter in their zipcode and update the order and Magento will get and display a shipping quote based on the products in the order.

    If there is more than on shipping method. (free, overnight, etc.) or more than one shipping carrier, (usps, fedex, tablerate, etc) then Magento should show more than one option to the customer to choose from. this will ether be in the form of a list with radio buttons, or a drop down depending on your theme.

  10. Manny says:

    I want to be shipping to Africa, and my shipping will be per lbs.
    so for ever 1lb the there will be an increment of $2. How can i do this please

  11. Roy says:

    I’d use the perl script that I give you in the post. Set up the lines that you need to make from the low to the high, and then include the Country codes for Africa and build the table.

    If you get creative, you can even loop through the script building the weight and shipping increases.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Categories