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?
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)
http://forum.virtuemart.net/index.php?topic=138796
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
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?
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.
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 :)
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 :)
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
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.
bryanearl, have you compared your states with the link i sended before?
The number of state in the list is 4120
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).
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.
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.
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.
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.
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.
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/)