The Integration Engineer
When it just has to work.

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.

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.

Subscribe to "The Integration Engineer" by Email
Find out about the tools and services available at The Integration Engineer's Consulting site.

This entry was posted on Thursday, January 26th, 2012 at 5:21 pm and is filed under Magento, Perl. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.