VirtueMart Forum

VirtueMart 2 + 3 + 4 => Administration & Configuration => Topic started by: bryanearl on May 15, 2018, 23:31:12 PM

Title: Complete List of States / Provinces / Regions
Post by: bryanearl on May 15, 2018, 23:31:12 PM
I am working on a store that sells to countries all over the world. I have noticed that the "state / province / region" drop-down is empty for most countries.

It's critical but there is no way that I can manually find and enter all of these myself. Is there a plugin or list or some way I can import these?
Title: Re: Complete List of States / Provinces / Regions ?
Post by: AH on May 16, 2018, 09:21:23 AM
QuoteIt's critical but there is no way that I can manually find and enter all of these myself.

http://forum.virtuemart.net/index.php?topic=79799.0 (http://forum.virtuemart.net/index.php?topic=79799.0)
Title: Re: Complete List of States / Provinces / Regions ?
Post by: jenkinhill on May 16, 2018, 10:54:57 AM
http://forum.virtuemart.net/index.php?topic=138796
Title: Re: Complete List of States / Provinces / Regions ?
Post by: bryanearl on May 16, 2018, 15:42:25 PM
Quote from: AH on May 16, 2018, 09:21:23 AM
QuoteIt's critical but there is no way that I can manually find and enter all of these myself.

http://forum.virtuemart.net/index.php?topic=79799.0 (http://forum.virtuemart.net/index.php?topic=79799.0)

Joomla 3.8.7 and VirtueMart 3.2.14
Title: Re: Complete List of States / Provinces / Regions ?
Post by: bryanearl on May 18, 2018, 18:09:28 PM
I still find it hard to believe that nobody has created an SQL import with a complete countries and states list! I realize that the official "standpoint" from VM developers is to do it manually, but that would take absolutely forever. Anyone at all?
Title: Re: Complete List of States / Provinces / Regions ?
Post by: jenkinhill on May 18, 2018, 18:22:11 PM
Quote from: bryanearl on May 18, 2018, 18:09:28 PM
I realize that the official "standpoint" from VM developers is to do it manually, but that would take absolutely forever. Anyone at all?

So it would take a long time (although maybe not for ever) to enter the states of all countries in the world - and even to write sql to do it. Maybe that's why nobody has voluntered to do it.  And who would maintain the list? 

People who sell to more than one country can add the states where required - as far as I can remember files with some of the states were submitted to the project by users such as for Armenis, and may well be already added.
Title: Re: Complete List of States / Provinces / Regions ?
Post by: AH on May 18, 2018, 19:06:03 PM
QuoteI still find it hard to believe that nobody has created an SQL import with a complete countries and states list!

You could be the one to do it and give it back to the community to use for free  :)
Title: Re: Complete List of States / Provinces / Regions ?
Post by: jflash on May 18, 2018, 21:06:00 PM
Hello!
My friend wrote me script for Estonian states, in attachment it is txt file, just rename it to php, put to root of Joomla and execute with browser :)
Title: Re: Complete List of States / Provinces / Regions ?
Post by: Studio 42 on May 18, 2018, 21:13:18 PM
Quote from: bryanearl on May 18, 2018, 18:09:28 PM
I still find it hard to believe that nobody has created an SQL import with a complete countries and states list!
First you have to find a full list of state from all the world.
Then you have to maintain it.
I have for eg do for a customer an autofill list of cities from france using zipcode.
The database is more then 2Go!
So you want state and a day someone want all cities in the world or phone numbers....
For eg here a list, but i mean it's not complete (many cities are missing) and not maintained  https://github.com/hiiamrohit/Countries-States-Cities-database
Title: Re: Complete List of States / Provinces / Regions ?
Post by: bryanearl on May 19, 2018, 04:43:17 AM
I went ahead and created a complete SQL import for all states for all countries in the VM database that aren't loaded by default in the install. It took me a while, but I located and used the latest country and state data from the United Nations.

I tested it and it works using phpmyadmin to import! All states from every country are now loaded into my VM installation  ;D

Maybe you guys can take a look and provide me with some feedback?

(ATTACHMENT AS TXT FILE)

* Also, note that I did not use "real" 2-code or 3-code values. I just put in random data to fill those fields.

Title: Re: Complete List of States / Provinces / Regions ?
Post by: Studio 42 on May 19, 2018, 09:19:59 AM
bryanearl, have you compared your states with the link i sended before?
The number of state in the list is 4120
Title: Re: Complete List of States / Provinces / Regions ?
Post by: bryanearl on May 19, 2018, 15:06:26 PM
Quote from: Studio 42 on May 19, 2018, 09:19:59 AM
bryanearl, have you compared your states with the link i sended before?
The number of state in the list is 4120

It should be very close. My list does not include all of the states that are already included with the default VM install. Also, for countries that don't necessarily have multiple states, I added the country name itself as a state so that the field can be required (something can always be chosen regardless of the country selected).
Title: Re: Complete List of States / Provinces / Regions ?
Post by: bryanearl on May 19, 2018, 20:32:43 PM
I successfully finished and tested my complete world-states import today and it works great! Note that I do not offer ANY guarantees, but I am providing this for others for free.

I updated my post above with the newest, working TXT file. Here are a couple notes:

   1. This is meant for those that have NOT entered in any additional states, other than what is automatically brought in by the latest VM install.

   2. I did not use actual 2-code or 3-code values for each state. It was not needed for what I am doing, so the 2-code and 3-code data is just generic.

   3. Before importing, you will need to do a "find and replace" in the text file to add your own database prefix to the "virtuemart_states" table identifier.
Title: Re: Complete List of States / Provinces / Regions
Post by: jjk on May 19, 2018, 23:20:08 PM
Just a note - for worldwide shipping the 'state/province/region' is useful only if those location names are required in the postal address. In many countries this is not the case.
Title: Re: Complete List of States / Provinces / Regions
Post by: Studio 42 on May 19, 2018, 23:44:37 PM
I checked the file.
You should not add a state_3_code, state_2_code
For eg in france 67 = bas-rhin for postal code
And in your database Bas-Rhin','68','68'
So the code can have right values in some case, but not in other cases.
An empty value is better then a generic one.
Here you can see the ZIP codes per states in france https://fr.wikipedia.org/wiki/Num%C3%A9rotation_des_d%C3%A9partements_fran%C3%A7ais, so you understand the problem.
Title: Re: Complete List of States / Provinces / Regions
Post by: bryanearl on May 20, 2018, 00:23:49 AM
Quote from: Studio 42 on May 19, 2018, 23:44:37 PM
I checked the file.
You should not add a state_3_code, state_2_code
For eg in france 67 = bas-rhin for postal code
And in your database Bas-Rhin','68','68'
So the code can have right values in some case, but not in other cases.
An empty value is better then a generic one.
Here you can see the ZIP codes per states in france https://fr.wikipedia.org/wiki/Num%C3%A9rotation_des_d%C3%A9partements_fran%C3%A7ais, so you understand the problem.

Well, that would make it much easier! However, I assumed that the 3-code and 2-code were required fields because when entering in a state manually in VM backend, those fields are required.
Title: Re: Complete List of States / Provinces / Regions
Post by: bryanearl on May 20, 2018, 00:26:48 AM
Quote from: jjk on May 19, 2018, 23:20:08 PM
Just a note - for worldwide shipping the 'state/province/region' is useful only if those location names are required in the postal address. In many countries this is not the case.

Yes, I know. However for my case, the business I am building the site for had all states in their previous webstore and wanted to keep it that way.
Title: Re: Complete List of States / Provinces / Regions
Post by: schiffsratte on November 23, 2020, 19:59:37 PM
For France the best resources I have found are those:

The most complete is maybe the INSEE site
https://www.insee.fr/fr/information/4316069 (https://www.insee.fr/fr/information/4316069)

But those two are good also
https://github.com/Stanislas-Poisson/French-zip-code (https://github.com/Stanislas-Poisson/French-zip-code)
and
https://sql.sh/1879-base-donnees-departements-francais (https://sql.sh/1879-base-donnees-departements-francais)

even the streets are available (thanks to fiberglass deploiement, I guess  ;) )
https://sql.sh/2716-base-donnnees-rues-france (https://sql.sh/2716-base-donnnees-rues-france)

But I used other files for eg Odoo from geonames.org.
They have postcode/city files for many (but not all) countries:
https://download.geonames.org/export/zip/ (https://download.geonames.org/export/zip/)