39 replies [Last post]
qrios's picture
Offline
Cool profile pic award.Getting busy with the Ubercode.Internationalizationizer
Joined: 08/13/2007
Juice: 554
Was this information Helpful?

I repost an sql import that make's all(?) countries and their zone's available to Ubercart.

Truncate the MYSQL tables: uc_zones and uc_countries
Then import the attached file (run as SQL code in f.e. phpMyadmin)

I think this could be in core.

Regards,

Kees

Web development, web applicaties, internet winkel

PreviewAttachmentSize
world_zones.txt254.51 KB
Lyle's picture
Offline
AdministratoreLiTe!
Joined: 08/07/2007
Juice: 6846
Re: Country- and zone update

Thanks, Kees. Our copy of that file got corrupted in the server crash, so we couldn't repost it.

qrios's picture
Offline
Cool profile pic award.Getting busy with the Ubercode.Internationalizationizer
Joined: 08/13/2007
Juice: 554
He man, Anytime. Blame your

He man,

Anytime. Blame your shitty server crash. Did your smile crash to on your avatar Eye-wink ?

megg's picture
Offline
Getting busy with the Ubercode.Internationalizationizer
Joined: 08/08/2007
Juice: 248
Re: He man, Anytime. Blame your

haha, kees, i noticed that too! lyle's avatar suddenly looked very sober after the crash...

michels's picture
Offline
Uber DonorBug FinderInternationalizationizer
Joined: 08/12/2007
Juice: 205
Re: Re: He man, Anytime. Blame your

thanks!

megg's picture
Offline
Getting busy with the Ubercode.Internationalizationizer
Joined: 08/08/2007
Juice: 248
Re: Re: Re: He man, Anytime. Blame your

is it just me or does anyone else see something strange with the zone names containing special characters? the first of the german zones, for example, and a few in france, metropolitan? on my system, the special characters are displayed as you would see them in html. ampersand-uuml-semicolon, etc.

michels's picture
Offline
Uber DonorBug FinderInternationalizationizer
Joined: 08/12/2007
Juice: 205
I have the same problem. In

I have the same problem.

In DB:
Württemberg

Generated source:
Württemberg

So its a problem with the output of the &-Character

qrios's picture
Offline
Cool profile pic award.Getting busy with the Ubercode.Internationalizationizer
Joined: 08/13/2007
Juice: 554
Re: I have the same problem.

This has to do with the fact this list comes straight from osCommerce. Drupal filters apparently the "&" char and replaces it whith "&" I adapted the SQL code by replacing all html special chars by their unicode equivalents.

Pleas test if this breaks zone-display somewhere else in Ubercart (like in orders or...)

Kees

AttachmentSize
world_zones2p0.txt 254.18 KB
qrios's picture
Offline
Cool profile pic award.Getting busy with the Ubercode.Internationalizationizer
Joined: 08/13/2007
Juice: 554
Re: Re: I have the same problem.

strange, it might be just my setup, but in my phpMyadmin I have to run the SQL code by copy/pasting the contents to the interface instead of looking up the file itself by [browse]. The latter breaks strings containing the special chars.

megg's picture
Offline
Getting busy with the Ubercode.Internationalizationizer
Joined: 08/08/2007
Juice: 248
thanks, kees! i also had a

thanks, kees!

i also had a problem with the second file, though, so i'm putting this one up in case anyone else has problems with keesje's ones. i had to copy and paste it into the SQL window, too.

AttachmentSize
world_zones3.txt 254.25 KB
dkashen's picture
Offline
Internationalizationizer
Joined: 08/08/2007
Juice: 180
how do you truncate via phpmyadmin ?

Hi,
I'm poking through the phpmyadmin window and whatever docs I can find,
and I don't see any truncate button (or other means).

Can someone help and tell me how to accomplish the truncate op ?

Thanks,
DK

Lyle's picture
Offline
AdministratoreLiTe!
Joined: 08/07/2007
Juice: 6846
Re: how do you truncate via phpmyadmin ?

It's the red "Empty" button. It'll show an alert message with "Do you really want to TRUNCATE table name?"

amitaibu@drupal.org's picture
Offline
Bug Finder
Joined: 09/08/2007
Juice: 239
Re: Re: how do you truncate via phpmyadmin ?

hmmm... can anyone you give a full workflow on how to import this?

megg's picture
Offline
Getting busy with the Ubercode.Internationalizationizer
Joined: 08/08/2007
Juice: 248
amitaibu: 1. go to php

amitaibu:

1. go to php myadmin page in your favourite web browser;

2. select your drupal database;

3. browse to uc_countries;

4. click 'empty' in the tabs above the table;

5. an alert will ask you if you're sure you want to truncate the uc_countries table, click ok;

6. click 'sql' in the tabs above the table;

7. you should see a large text field labeled "run sql query/queries on databasename". delete any default text in this field and copy and paste the sql out of the text document you downloaded from this forum topic. this is what you should copy:

INSERT INTO `uc_countries` VALUES (1, 'Afghanistan', 'AF', 'AFG', 1);
INSERT INTO `uc_countries` VALUES (2, 'Albania', 'AL', 'ALB', 1);
...
and so on until the end of the 'uc_countries' inserts

8. repeat steps 3-7 for uc_zones.

hope that helps!

amitaibu@drupal.org's picture
Offline
Bug Finder
Joined: 09/08/2007
Juice: 239
Re: amitaibu: 1. go to php

megg rules Eye-wink

offthehook's picture
Offline
Joined: 08/18/2007
Juice: 29
Not sure about this list

Hmm, whilst setting up my store the first thing I noticed is that the county of Cumbria is missing from the UK zone (because I live there).

I've done a little more digging and can see that also missing is the Isle of Man, Jersey and Guernsey. There may be others.

In fact I believe that the Royal Mail only use post codes and do not recommend using postal counties for addresses therefore I suspect it will be confusing to customers who cannot enter their known and trusted addresses if the option is mandatory and the list is pre-defined.

Perhaps the zone field could be left greyed out upon selection of the UK in the countries field?

Ryan's picture
Offline
Joined: 08/07/2007
Juice: 15438
Re: Not sure about this list

Hmm, sorry the file is incomplete. I of course would have no way of checking, but I have made the address forms on the checkout page configurable. You can turn off the zone field altogether at /admin/store/settings/checkout/edit/fields.

Tanjerine's picture
Offline
Bug FinderInternationalizationizerNot KulvikThe other woman.
Joined: 08/31/2007
Juice: 235
Re: Re: Not sure about this list

i've looked over the file and i think that it should be mentioned that people using this file shouldn't use (or rather, use with caution) the importable country files that are part of ubercart. as they might cause some conflicts.

i 'fixed' the country file for the philippines and i made some changes to it based on the internationalization notes and ISOs (i even added some other provinces!) that were discussed on the old forum boards. as an example, the philippines' zone/country code in the oscommerce file is 168 and it's 608 in the file that i 'fixed'. so be careful with them files. Smiling

yhager's picture
Offline
Joined: 10/01/2007
Juice: 59
Re: Re: Re: Not sure about this list

The countries file attached previously in this thread is incompatible with the country files that come with ubercart core. Using this may break things like existing customers, shipping rules etc.

The attached file contains information on all countries (source: iso3166-1), with country id's as defined in ISO, and is compatble with ubercart core files. It does not contain information about zones.

You can import this into your database and it will not remove existing zones you might have imported from ubercart core. You can also import new zones - just disable the relevant country and import it.

I find this good enough for me, since I do need a list of all countries, but not all zones in the world (US, CA are good enough for now.. if more are needed, I'll import them).

AttachmentSize
iso3166-1.sql_.txt 15.81 KB
Tanjerine's picture
Offline
Bug FinderInternationalizationizerNot KulvikThe other woman.
Joined: 08/31/2007
Juice: 235
Re: Re: Re: Re: Not sure about this list

great! maybe we can just add an empty field for the zones of the other countries (those without zones in the db)?

appel's picture
Offline
Joined: 02/14/2008
Juice: 9
Re: Re: Re: Re: Not sure about this list

Great, thanks a lot! Perhaps a link to your post should be included in the docs?

psynaptic's picture
Offline
Early adopter... addicted to alphas.Not KulvikTheminator
Joined: 08/28/2007
Juice: 731
Re: Re: Re: Re: Not sure about this list

That .sql file contains Yugoslavia which leads me to believe it's outdated. Anyone know different?

TomIslander's picture
Offline
Bug Finder
Joined: 03/22/2008
Juice: 73
Yugoslavia

Well I believe that it's not a problem since Serbia and Macedonia are listed as well.

Kosovo is missing ("de jure" still part of Serbia), probably they don't yet have seperate postal addresses so that won't make any problem either.

Tom Szigeti

adamo's picture
Offline
Getting busy with the Ubercode.
Joined: 02/17/2009
Juice: 229
Re: Re: Re: Re: Not sure about this list

Thanks for that! Here's the same file, but more friendly casing for country names instead of all caps.

AttachmentSize
uc_countries.txt 16.27 KB
psynaptic's picture
Offline
Early adopter... addicted to alphas.Not KulvikTheminator
Joined: 08/28/2007
Juice: 731
Re: Not sure about this list

I have noticed that there is no Cleveland or Redcar and Cleveland options. I live there and have been trying to figure out what zones actually exist. I've found it hard to find solid info online about it. We need the zones so we can show different payment modules for different zones within the UK.

Tanjerine's picture
Offline
Bug FinderInternationalizationizerNot KulvikThe other woman.
Joined: 08/31/2007
Juice: 235
Re: thanks, kees! i also had a

Hi! I was just wondering if the files here are still wholly based on OsCommerce's world_zones? Or have they been updated with the country files that have been added to Ubercart?

TomIslander's picture
Offline
Bug Finder
Joined: 03/22/2008
Juice: 73
Zones

Hungary's zones in this file are a crazy mix of counties and a few cities _and_ we don't need to include the county name in postal address at all. I've just started to play around with Übercart and I really like it but I found this part quite flaky. I'll simply go with the full list of countries with an optional State/Region field.

Tom Szigeti

TR
TR's picture
Offline
Bug FinderFAQ ModeratorGetting busy with the Ubercode.
Joined: 11/05/2007
Juice: 3424
TomIslander wrote: I'll
TomIslander wrote:

I'll simply go with the full list of countries with an optional State/Region field.

That's what I've done too, for similar reasons. I have customers in 53 countries other than the US, and trying to impose Zones is more trouble than it's worth.

<tr>.
TomIslander's picture
Offline
Bug Finder
Joined: 03/22/2008
Juice: 73
State/Region

May I ask that how specifically did you implement the custom address field?

Tom Szigeti

george@dynapres.nl's picture
Offline
Joined: 01/02/2008
Juice: 209
Disabling zones

In UC2 you can change settings at: admin/store/settings/checkout/edit/fields

---
George on Drupal

davegan@drupal.org's picture
Offline
Joined: 07/21/2008
Juice: 26
Update for yhager's list

Here's an update for yhager's list that doesn't have all the country names in all caps.

Just in case someone else is as picky as I am.

AttachmentSize
iso3166-2.sql_.txt 15.81 KB
alexku's picture
Offline
Joined: 10/06/2009
Juice: 50
Re: Update for yhager's list

Here is an update of #28 iso3166-2.sql_.txt (by davegan@drupal.org).

I have added one country (taken from Wikipedia):
INSERT INTO uc_countries VALUES (652,"Saint Barthelemy",'BL','BLM',1);

And removed one also:
INSERT INTO uc_countries VALUES (891,"Yugoslavia",'YU','YUG',1);

Hope this helps

AttachmentSize
iso3166-3.sql_.txt 16.06 KB
Ole Martin's picture
Offline
Joined: 01/31/2008
Juice: 15
Prefix

Great thread. But we must remember that many use "Prefix" and those who use it need to replace "uc_countries" with "prefix_uc_countries". Search and replace with Notepad. Right now I create a demopage of Ubercart here:
http://ubercart.drupal.no/

buysnakewine's picture
Offline
Joined: 03/31/2009
Juice: 104
I have just set up Ubercart and see only Canada and US

Hello,

Is it the most up to date countries list ?

Can you tell me how to use it ?

Thanks

Linxor's picture
Offline
Joined: 03/25/2009
Juice: 18
Re: I have just set up Ubercart and see only Canada and US

The list posted in #28 is complete (as of the date of this post) -- according to http://en.wikipedia.org/wiki/ISO_3166-1 there are 246 countries, and that file contains 246 countries.

How to use it:

The file contains SQL code to import all countries into your database.
You'll need some way to run SQL code on your server -- typically through phpMyAdmin in your server's control panel.

1 ) Open the database where you have Ubercart installed.

2 ) Backup your database incase you mess anything up (only necessary if you are new at doing this).

3 ) Go to the tab in your database program that allows you to run SQL code.

4 ) Copy the contents of this file and paste it into the SQL window.

5 ) Click "GO" or somehow run ALL queries.

6 ) Test it out by going to your shopping cart and adding a product, then go to the checkout page and you should see all the countries listed in the address section.

jstushnoff@drupal.org's picture
Offline
Joined: 10/25/2009
Juice: 2
Re: Country- and zone update

Any more recent solution for UC2?
My client also sells worldwide, and of course we don't want to discourage anyone in any country from placing an order.
I've imported the most recent list of countries from this thread, and kept the zones in the zone table that were created upon installing all the original country files.
From reading this and other forum threads I think it would be excellent to have a slightly more flexible system that could be somewhat maintained by the customers themselves. What I'd propose for core: (should I be doing this in a developers thread?)

  • Keep the current database tables as is
  • Keep the .cif files system of import (for those who only sell to certain countries and like that system)
  • Add an option to 'import all countries' including the ones there are no .cif files for (probably with further explanation that not all countries have their zones included)
  • ADD an 'Other' option to the "state/province" select list (it would appear for any country selected)
  • ADD a corresponding 'Other' field
    • have the input from this field added to the uc_zones table (of course the zone_code would be the only thing missing, but that could be put in by picky administrators later)
    • (ideally, AJAXify the field so it only shows up when the 'Other' option is selected)

Did I miss anything??

I'm sure there's many others who have already thought of this. As a novice PHP 'tweaker' I could probably even manage to do this myself, however I am not yet familiar with developing for Drupal (starting to get there), and I don't know if there would be a way to hook into the appropriate function or if it would have to be hacked. Because it will take me a while to figure out how to do this properly, It'd be great if someone could assist me with this (or just do it Eye-wink ) I can't see it being more than an hour's work for someone who knows what they're doing.
Of course, I'm interested to know if/where there are already solutions/discussions that I just haven't found yet.
I'd love to hear from the lead team on this, and let me know how I can help; I'm devoted to getting everything in our current stores moved over from ZenCart and functioning at least equivalently by month's end (hope I don't have to hack anything).

BigMike's picture
Offline
Joined: 10/20/2008
Juice: 1057
Re: Country- and zone update

Wow, this thread is a real life saver. Thank you to everyone who spent time developing that import. It worked perfectly into my UC 1.x.

Mike

TR
TR's picture
Offline
Bug FinderFAQ ModeratorGetting busy with the Ubercode.
Joined: 11/05/2007
Juice: 3424
Re: Re: Country- and zone update

I've said this before many times in more detail in other threads, but let me repeat it here too:

Importing all 246 countries is almost never the right thing to do. Do you really have a way to accept payment from all those countries? I don't know of any payment processor that supports that. Are you even allowed to ship your goods to all those countries? Both the origin and destination countries impose restrictions of what can be exported or imported. Your package carrier may not offer service to many or most of those other countries, or may not offer insurance or tracking to many of those destinations. So the list of countries you support really has to be subject to some consideration from a business point of view, not just blindly added.

<tr>.
BigMike's picture
Offline
Joined: 10/20/2008
Juice: 1057
Re: Re: Re: Country- and zone update

That is a very good point. We had some customers in France, Russia, and recently Sweden, so I came here looking for a solution.

Well, I've got a new problem and I am not sure if its from this country patch or not.

Our FedEx stopped giving out shipping rate quotes sometime today (not sure when but certainly after I installed this patch). Here is the error:

* Error in processing FedEx Shipping Quote transaction.
* FAILURE
* crs
* 264
* Error connecting to CAAS for validateAccountMeter().

Anyone know what this is about? FedEx is so difficult to use compared to UPS

BigMike's picture
Offline
Joined: 10/20/2008
Juice: 1057
Re: Re: Re: Re: Country- and zone update

Okay, I posted this at http://drupal.org/node/817268