SQL Script to insert list of Departamentos de Colombia to Prestashop States Table

I was currently preparing a Prestashop e-commerce site for its launch in the country of Colombia.

However, I encountered a tedious problem to be solved. Prestashop does not have the departmentos of Colombia. By the way, for those of you who do not know, Colombia does not have states. It has departmentos. Each departamento then has its cities.

I managed to search for someone who had posted a SQL script that would automatically insert into the database of Prestashop the list of Colombian departmentos (states). Not being able to find it, I thought it would be useful to share it with the Prestashop community.

Some notes before providing you the file so that you may efficiently add all the departmentos de Colombia into the Prestashop database:

Steps before adding the Colombia departamentos (States) to Prestashop States (ps_state) Table

  1. You must verify first if in your table ps_country, the country id for the country of Colombia is 69.
  2. You must verify in your table ps_zone if the id for South America is 6, since you need to add this into the states table.
  3. Make a backup of your database. This script effectively added all the states of Colombia into the Prestashop database of states, however you must take the necessary precautions in case something goes bad.
  4. I am Colombian so I was able to effectively double check this list. It is correct.

So here it is, I hope you Prestashop geeks enjoy!:

Script SQL to insert Colombia departamentos states to Prestashop ps_state Table.

(Lista de departamentos de Colombia y Script SQL para insertar los departamentos de Colombia en la base de datos)

INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Amazonas','AMA');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Antioquia','ANT');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Arauca','ARA');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Atlántico','ATL');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Bolívar','BOL');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Boyacá','BOY');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Caldas','CAL');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Caquetá','CAQ');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Casanare','CAS');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Cauca','CAU');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Cesar','CES');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Chocó','CHO');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Córdoba','COR');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Cundinamarca','CUN');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Distrito Capital','DC');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Guainía','GUA');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Guaviare','GUV');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Huila','HUI');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'La Guajira','LAG');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Magdalena','MAG');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Meta','MET');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Nariño','NAR');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Norte de Santander','NSA');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Putumayo','PUT');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Quindío','QUI');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Risaralda','RIS');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'San Andrés y Providencia','SAP');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Santander','SAN');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Sucre','SUC');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Tolima','TOL');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Valle del Cauca','VAC');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Vaupés','VAU');
INSERT INTO ps_state (id_country,id_zone,name,iso_code) VALUES (69,6,'Vichada','VID');

Following, you may find the list of departamentos of Colombia, the Colombia departamentos ISO Code, the population of each departamento in Colombia and the Capital.

List of Colombia departamentos (states) with population, iso code, capital and name.

(Lista de departamentos de Colombia con nombre, código ISO, población y capital)

Department ISO FIPS NUTE Typ Population Area(km.²) Area(mi.²) Capital
Amazonas AMA CO01 20491 c 46,950 109,665 42,342 Leticia
Antioquia ANT CO02 20505 d 5,601,507 63,612 24,561 Medellín
Arauca ARA CO03 20781 i 153,028 23,818 9,196 Arauca
Atlántico ATL CO04 20208 d 2,112,001 3,388 1,308 Barranquilla
Bolívar BOL CO35 20213 d 1,836,640 25,978 10,030 Cartagena
Boyacá BOY CO36 20615 d 1,210,982 23,189 8,953 Tunja
Caldas CAL CO37 20617 d 898,490 7,888 3,046 Manizales
Caquetá CAQ CO08 20418 i 337,932 88,965 34,350 Florencia
Casanare CAS CO32 20785 i 281,294 44,640 17,236 Yopal
Cauca CAU CO09 20319 d 1,182,022 29,308 11,316 Popayán
Cesar CES CO10 20220 d 878,437 22,905 8,844 Valledupar
Chocó CHO CO11 20327 d 388,476 46,530 17,965 Quibdó
Córdoba COR CO12 20223 d 1,462,909 25,020 9,660 Montería
Cundinamarca CUN CO33 20625 d 2,228,682 22,478 8,679 Bogotá
Distrito Capital DC CO34 20111 e 6,778,691 1,732 669 Bogotá
Guainía GUA CO15 20494 c 18,797 72,238 27,891 Puerto Inírida
Guaviare GUV CO14 20495 c 56,758 42,327 16,343 San José del Guaviare
Huila HUI CO16 20641 d 1,001,476 19,890 7,680 Neiva
La Guajira LAG CO17 20244 d 655,943 20,848 8,049 Riohacha
Magdalena MAG CO38 20247 d 1,136,819 23,188 8,953 Santa Marta
Meta MET CO19 20750 d 713,772 85,635 33,064 Villavicencio
Nariño NAR CO20 20352 d 1,498,234 33,268 12,845 Pasto
Norte de Santander NSA CO21 20554 d 1,208,336 21,658 8,362 Cúcuta
Putumayo PUT CO22 20486 i 237,197 24,885 9,608 Mocoa
Quindío QUI CO23 20663 d 518,691 1,845 712 Armenia
Risaralda RIS CO24 20666 d 859,666 4,140 1,598 Pereira
San Andrés y Providencia SAP CO25 20288 i 59,573 44 17 San Andrés
Santander SAN CO26 20568 d 1,913,444 30,537 11,790 Bucaramanga
Sucre SUC CO27 20270 d 762,263 10,917 4,215 Sincelejo
Tolima TOL CO28 20673 d 1,312,304 23,562 9,097 Ibagué
Valle del Cauca VAC CO29 20376 d 4,052,535 22,140 8,548 Cali
Vaupés VAU CO30 20497 c 19,943 65,268 25,200 Mitú
Vichada VID CO31 20799 c 44,592 100,242 38,704 Puerto Carreño

 

If this was useful for you, please leave me a comment. I would really appreciate it.

Enjoy,
Andrés Felipe Díaz

Comments

  1. pablo says

    Sabes como podria conseguir los scripts de paises con sus ciudades? (no solo colombia). O por lo menos de america

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>