MYSQL - Insert data from a text file into the table - PHPMyAdmin

ArmoVanBuuren

ArmoVanBuuren

ArmoVanBuuren

Associate
Joined
19 Mar 2005
Posts
569
I currently have a problem, I have a countries table and want to insert every country of the world.

The table is set up as follows:

PHP: 
CREATE  TABLE  `countries` ( `countryID` INT( 255  )  NOT  NULL  AUTO_INCREMENT ,
 `countryName` VARCHAR( 250  )  NOT  NULL ,
 PRIMARY  KEY (  `countryID`  ) ) TYPE  =  MYISAM ;

I want to read in the file below and input all the country names and each have an auto_incremented id. How would I go about formatting the text file below in order to be able to do this?

United Kingdom;
Ireland (Republic of);

Australia;
Canada;
United States of America;

Afghanistan;
Albania;

Algeria;
Andorra;
Angola;
Anguilla;
Antiga & Barbuda;
Argentina;

Armenia;
Aruba;
Ascension;
Austra;
Azerbaijan;
Azores;

Bahamas;
Bahrain;
Balearic Islands;
Bangladesh;
Barbados;
Belarus;

Belgium;
Belize;
Benin;
Bermuda;
Bhutan;
Bolivia;

Bosnia-Herzegovina;
Botswana;
Brazil;
British Virgin Islands;
Brunei Darussalam;
Bulgaria;

Burkina Faso;
Burundi;
Cambodia;
Cameroon;
Canary Islands;
Cape Verde;

Cayman Islands;
Central African Republic;
Chad;
Chile;
China;
Christmas Island;

Cocos Island;
Columbia;
Comoros;
Congo, Democratic Rep. of;
Congo (People's Rep.);
Corsica;

Costa Rica;
Croatia;
Cuba;
Cyprus;
Czech Republic;

Denmark;
Djibouti;
Dominica;
Dominican Republic;
East Timor;
Ecuador;

Egypt;
El Salvador;
Equatorial Guinea;
Eritrea;
Estonia;
Ethiopia;

Falkland Islands;
Faroe Islands;
Fiji;
Finland;
France;
French Guiana;

French Polynesia;
Gabon;
The Gambia;
Gaza & Khan Yunis;
Georgia;
Germany;

Ghana;
Gibraltar;
Greece;
Greenland;
Grenada;
Guadeloupe;

Guam;
Guatemala;
Guinea (Republic of);
Guinea-Bissau;
Guyana;
Haiti2;

Honduras;
Hong Kong;
Hungary";
Iceland;
India;
Indonesia;

Iran (Islamic Republic);
Iraq;
Israel;
Italy;
Ivory Coast Cote d'Ivoire;
Jamaica;

Japan;
Jordan;
Kazakhstan;
Kenya;
Kirghizstan;
Kiribati;

Korea, Republic of (South);
Kuwait;
Laos;
Latvia;
Lebanon;
Lesotho;

Liberia;
Libya;
Liechtenstein;
Lithuania;
Luxembourg;
Macao;

Macedonia;
Madagascar;
Madeira;
Malawi;
Malaysia;
Maldives;

Mali;
Malta;
Marshall Islands;
Martinique;
Mauritania;
Mauritius;

Mexico;
Micronesia;
Moldova;
Monaco;
Mongolia;
Montserrat;

Morrocco;
Mozambique;
Myanmar (Burma);
Namibia;
Nauru;
Nepal;

Netherland;
Netherlands Antilles;
New Caledonia;
New Zealand;
New Zealand Island Ter.;
Nicaragua;

Niger;
Nigeria;
Norfolk Island;
Northern Mariana Islands;
Norway;
Oman;

Pakistan;
Panama;
Papua New Guinea;
Paraguay;
Peru;
Philippines;

Pitcairn Islands;
Poland;
Portugal;
Puerto Rico;
Qatar;
Reunion;

Romania;
Russia;
Rwanda;
St Christopher & Nevis;
St Helena;
St Lucia;

St Pierre & Miquelon;
St Vincent and Grenadines;
Samoa;
San Marino;
Sao Tome & Principe;

Sardinia;
Saudi Arabi;
Senegal;
Seyshelles;
Sicily;
Sierra Leone;

Singapore;
Slovakia;
Slovenia;
Soloman Islands;
Somali;
South Africa;

Spain;
Spanish Territories of N Africa;
Spitzbergen">Spitzbergen;
Sri Lanka;
Sudan;
Suriname;

Swaziland;
Sweden;
Switzerland
Syria;
Tiawan;
Tajikistan;

Tanzania;
Thailand;
Togo;
Tonga;
Trinidad & Tobago;
Tristan da Cunha;

Tunisia;
Turkey3;
Turkmenistan;
Turks & Caicos Islands;
Tuvalu;
Uganda;

Ukraine;
United Arab Emirates;
Uruguay;
Uzbekistan;
Vanuatu;
Vatican City State;

Venezuela;
Vietnam;
Virgin Islands (USA);
Wake Island;
Wallis & Futuna Islands;
Western Samo;

Yemen;
Yugoslavia;
Zambia;
Zimbabwe;
Click to expand...
 
I'm in a good mood today, so I've done it for you.

What I do when I wnat to do stuff like this is copy the list into Excel then set up a formula that adds the country name into the middle of the SQL statement, copy formula for each value, then paste the result into a text file.

Quick and simple :)

Code: 
INSERT INTO `countries` (`countryName`) VALUES ('United Kingdom');
INSERT INTO `countries` (`countryName`) VALUES ('Ireland (Republic of)');
INSERT INTO `countries` (`countryName`) VALUES ('Australia');
INSERT INTO `countries` (`countryName`) VALUES ('Canada');
INSERT INTO `countries` (`countryName`) VALUES ('United States of America');
INSERT INTO `countries` (`countryName`) VALUES ('Afghanistan');
INSERT INTO `countries` (`countryName`) VALUES ('Albania');
INSERT INTO `countries` (`countryName`) VALUES ('Algeria');
INSERT INTO `countries` (`countryName`) VALUES ('Andorra');
INSERT INTO `countries` (`countryName`) VALUES ('Angola');
INSERT INTO `countries` (`countryName`) VALUES ('Anguilla');
INSERT INTO `countries` (`countryName`) VALUES ('Antiga & Barbuda');
INSERT INTO `countries` (`countryName`) VALUES ('Argentina');
INSERT INTO `countries` (`countryName`) VALUES ('Armenia');
INSERT INTO `countries` (`countryName`) VALUES ('Aruba');
INSERT INTO `countries` (`countryName`) VALUES ('Ascension');
INSERT INTO `countries` (`countryName`) VALUES ('Austra');
INSERT INTO `countries` (`countryName`) VALUES ('Azerbaijan');
INSERT INTO `countries` (`countryName`) VALUES ('Azores');
INSERT INTO `countries` (`countryName`) VALUES ('Bahamas');
INSERT INTO `countries` (`countryName`) VALUES ('Bahrain');
INSERT INTO `countries` (`countryName`) VALUES ('Balearic Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Bangladesh');
INSERT INTO `countries` (`countryName`) VALUES ('Barbados');
INSERT INTO `countries` (`countryName`) VALUES ('Belarus');
INSERT INTO `countries` (`countryName`) VALUES ('Belgium');
INSERT INTO `countries` (`countryName`) VALUES ('Belize');
INSERT INTO `countries` (`countryName`) VALUES ('Benin');
INSERT INTO `countries` (`countryName`) VALUES ('Bermuda');
INSERT INTO `countries` (`countryName`) VALUES ('Bhutan');
INSERT INTO `countries` (`countryName`) VALUES ('Bolivia');
INSERT INTO `countries` (`countryName`) VALUES ('Bosnia-Herzegovina');
INSERT INTO `countries` (`countryName`) VALUES ('Botswana');
INSERT INTO `countries` (`countryName`) VALUES ('Brazil');
INSERT INTO `countries` (`countryName`) VALUES ('British Virgin Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Brunei Darussalam');
INSERT INTO `countries` (`countryName`) VALUES ('Bulgaria');
INSERT INTO `countries` (`countryName`) VALUES ('Burkina Faso');
INSERT INTO `countries` (`countryName`) VALUES ('Burundi');
INSERT INTO `countries` (`countryName`) VALUES ('Cambodia');
INSERT INTO `countries` (`countryName`) VALUES ('Cameroon');
INSERT INTO `countries` (`countryName`) VALUES ('Canary Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Cape Verde');
INSERT INTO `countries` (`countryName`) VALUES ('Cayman Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Central African Republic');
INSERT INTO `countries` (`countryName`) VALUES ('Chad');
INSERT INTO `countries` (`countryName`) VALUES ('Chile');
INSERT INTO `countries` (`countryName`) VALUES ('China');
INSERT INTO `countries` (`countryName`) VALUES ('Christmas Island');
INSERT INTO `countries` (`countryName`) VALUES ('Cocos Island');
INSERT INTO `countries` (`countryName`) VALUES ('Columbia');
INSERT INTO `countries` (`countryName`) VALUES ('Comoros');
INSERT INTO `countries` (`countryName`) VALUES ('Congo, Democratic Rep. of');
INSERT INTO `countries` (`countryName`) VALUES ('Congo (People''s Rep.)');
INSERT INTO `countries` (`countryName`) VALUES ('Corsica');
INSERT INTO `countries` (`countryName`) VALUES ('Costa Rica');
INSERT INTO `countries` (`countryName`) VALUES ('Croatia');
INSERT INTO `countries` (`countryName`) VALUES ('Cuba');
INSERT INTO `countries` (`countryName`) VALUES ('Cyprus');
INSERT INTO `countries` (`countryName`) VALUES ('Czech Republic');
INSERT INTO `countries` (`countryName`) VALUES ('Denmark');
INSERT INTO `countries` (`countryName`) VALUES ('Djibouti');
INSERT INTO `countries` (`countryName`) VALUES ('Dominica');
INSERT INTO `countries` (`countryName`) VALUES ('Dominican Republic');
INSERT INTO `countries` (`countryName`) VALUES ('East Timor');
INSERT INTO `countries` (`countryName`) VALUES ('Ecuador');
INSERT INTO `countries` (`countryName`) VALUES ('Egypt');
INSERT INTO `countries` (`countryName`) VALUES ('El Salvador');
INSERT INTO `countries` (`countryName`) VALUES ('Equatorial Guinea');
INSERT INTO `countries` (`countryName`) VALUES ('Eritrea');
INSERT INTO `countries` (`countryName`) VALUES ('Estonia');
INSERT INTO `countries` (`countryName`) VALUES ('Ethiopia');
INSERT INTO `countries` (`countryName`) VALUES ('Falkland Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Faroe Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Fiji');
INSERT INTO `countries` (`countryName`) VALUES ('Finland');
INSERT INTO `countries` (`countryName`) VALUES ('France');
INSERT INTO `countries` (`countryName`) VALUES ('French Guiana');
INSERT INTO `countries` (`countryName`) VALUES ('French Polynesia');
INSERT INTO `countries` (`countryName`) VALUES ('Gabon');
INSERT INTO `countries` (`countryName`) VALUES ('The Gambia');
INSERT INTO `countries` (`countryName`) VALUES ('Gaza & Khan Yunis');
INSERT INTO `countries` (`countryName`) VALUES ('Georgia');
INSERT INTO `countries` (`countryName`) VALUES ('Germany');
INSERT INTO `countries` (`countryName`) VALUES ('Ghana');
INSERT INTO `countries` (`countryName`) VALUES ('Gibraltar');
INSERT INTO `countries` (`countryName`) VALUES ('Greece');
INSERT INTO `countries` (`countryName`) VALUES ('Greenland');
INSERT INTO `countries` (`countryName`) VALUES ('Grenada');
INSERT INTO `countries` (`countryName`) VALUES ('Guadeloupe');
INSERT INTO `countries` (`countryName`) VALUES ('Guam');
INSERT INTO `countries` (`countryName`) VALUES ('Guatemala');
INSERT INTO `countries` (`countryName`) VALUES ('Guinea (Republic of)');
INSERT INTO `countries` (`countryName`) VALUES ('Guinea-Bissau');
INSERT INTO `countries` (`countryName`) VALUES ('Guyana');
INSERT INTO `countries` (`countryName`) VALUES ('Haiti2');
INSERT INTO `countries` (`countryName`) VALUES ('Honduras');
INSERT INTO `countries` (`countryName`) VALUES ('Hong Kong');
INSERT INTO `countries` (`countryName`) VALUES ('Hungary"');
INSERT INTO `countries` (`countryName`) VALUES ('Iceland');
INSERT INTO `countries` (`countryName`) VALUES ('India');
INSERT INTO `countries` (`countryName`) VALUES ('Indonesia');
INSERT INTO `countries` (`countryName`) VALUES ('Iran (Islamic Republic)');
INSERT INTO `countries` (`countryName`) VALUES ('Iraq');
INSERT INTO `countries` (`countryName`) VALUES ('Israel');
INSERT INTO `countries` (`countryName`) VALUES ('Italy');
INSERT INTO `countries` (`countryName`) VALUES ('Ivory Coast Cote d''Ivoire');
INSERT INTO `countries` (`countryName`) VALUES ('Jamaica');
INSERT INTO `countries` (`countryName`) VALUES ('Japan');
INSERT INTO `countries` (`countryName`) VALUES ('Jordan');
INSERT INTO `countries` (`countryName`) VALUES ('Kazakhstan');
INSERT INTO `countries` (`countryName`) VALUES ('Kenya');
INSERT INTO `countries` (`countryName`) VALUES ('Kirghizstan');
INSERT INTO `countries` (`countryName`) VALUES ('Kiribati');
INSERT INTO `countries` (`countryName`) VALUES ('Korea, Republic of (South)');
INSERT INTO `countries` (`countryName`) VALUES ('Kuwait');
INSERT INTO `countries` (`countryName`) VALUES ('Laos');
INSERT INTO `countries` (`countryName`) VALUES ('Latvia');
INSERT INTO `countries` (`countryName`) VALUES ('Lebanon');
INSERT INTO `countries` (`countryName`) VALUES ('Lesotho');
INSERT INTO `countries` (`countryName`) VALUES ('Liberia');
INSERT INTO `countries` (`countryName`) VALUES ('Libya');
INSERT INTO `countries` (`countryName`) VALUES ('Liechtenstein');
INSERT INTO `countries` (`countryName`) VALUES ('Lithuania');
INSERT INTO `countries` (`countryName`) VALUES ('Luxembourg');
INSERT INTO `countries` (`countryName`) VALUES ('Macao');
INSERT INTO `countries` (`countryName`) VALUES ('Macedonia');
INSERT INTO `countries` (`countryName`) VALUES ('Madagascar');
INSERT INTO `countries` (`countryName`) VALUES ('Madeira');
INSERT INTO `countries` (`countryName`) VALUES ('Malawi');
INSERT INTO `countries` (`countryName`) VALUES ('Malaysia');
INSERT INTO `countries` (`countryName`) VALUES ('Maldives');
INSERT INTO `countries` (`countryName`) VALUES ('Mali');
INSERT INTO `countries` (`countryName`) VALUES ('Malta');
INSERT INTO `countries` (`countryName`) VALUES ('Marshall Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Martinique');
INSERT INTO `countries` (`countryName`) VALUES ('Mauritania');
INSERT INTO `countries` (`countryName`) VALUES ('Mauritius');
INSERT INTO `countries` (`countryName`) VALUES ('Mexico');
INSERT INTO `countries` (`countryName`) VALUES ('Micronesia');
INSERT INTO `countries` (`countryName`) VALUES ('Moldova');
INSERT INTO `countries` (`countryName`) VALUES ('Monaco');
INSERT INTO `countries` (`countryName`) VALUES ('Mongolia');
INSERT INTO `countries` (`countryName`) VALUES ('Montserrat');
INSERT INTO `countries` (`countryName`) VALUES ('Morrocco');
INSERT INTO `countries` (`countryName`) VALUES ('Mozambique');
INSERT INTO `countries` (`countryName`) VALUES ('Myanmar (Burma)');
INSERT INTO `countries` (`countryName`) VALUES ('Namibia');
INSERT INTO `countries` (`countryName`) VALUES ('Nauru');
INSERT INTO `countries` (`countryName`) VALUES ('Nepal');
INSERT INTO `countries` (`countryName`) VALUES ('Netherland');
INSERT INTO `countries` (`countryName`) VALUES ('Netherlands Antilles');
INSERT INTO `countries` (`countryName`) VALUES ('New Caledonia');
INSERT INTO `countries` (`countryName`) VALUES ('New Zealand');
INSERT INTO `countries` (`countryName`) VALUES ('New Zealand Island Ter.');
INSERT INTO `countries` (`countryName`) VALUES ('Nicaragua');
INSERT INTO `countries` (`countryName`) VALUES ('Niger');
INSERT INTO `countries` (`countryName`) VALUES ('Nigeria');
INSERT INTO `countries` (`countryName`) VALUES ('Norfolk Island');
INSERT INTO `countries` (`countryName`) VALUES ('Northern Mariana Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Norway');
INSERT INTO `countries` (`countryName`) VALUES ('Oman');
INSERT INTO `countries` (`countryName`) VALUES ('Pakistan');
INSERT INTO `countries` (`countryName`) VALUES ('Panama');
INSERT INTO `countries` (`countryName`) VALUES ('Papua New Guinea');
INSERT INTO `countries` (`countryName`) VALUES ('Paraguay');
INSERT INTO `countries` (`countryName`) VALUES ('Peru');
INSERT INTO `countries` (`countryName`) VALUES ('Philippines');
INSERT INTO `countries` (`countryName`) VALUES ('Pitcairn Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Poland');
INSERT INTO `countries` (`countryName`) VALUES ('Portugal');
INSERT INTO `countries` (`countryName`) VALUES ('Puerto Rico');
INSERT INTO `countries` (`countryName`) VALUES ('Qatar');
INSERT INTO `countries` (`countryName`) VALUES ('Reunion');
INSERT INTO `countries` (`countryName`) VALUES ('Romania');
INSERT INTO `countries` (`countryName`) VALUES ('Russia');
INSERT INTO `countries` (`countryName`) VALUES ('Rwanda');
INSERT INTO `countries` (`countryName`) VALUES ('St Christopher & Nevis');
INSERT INTO `countries` (`countryName`) VALUES ('St Helena');
INSERT INTO `countries` (`countryName`) VALUES ('St Lucia');
INSERT INTO `countries` (`countryName`) VALUES ('St Pierre & Miquelon');
INSERT INTO `countries` (`countryName`) VALUES ('St Vincent and Grenadines');
INSERT INTO `countries` (`countryName`) VALUES ('Samoa');
INSERT INTO `countries` (`countryName`) VALUES ('San Marino');
INSERT INTO `countries` (`countryName`) VALUES ('Sao Tome & Principe');
INSERT INTO `countries` (`countryName`) VALUES ('Sardinia');
INSERT INTO `countries` (`countryName`) VALUES ('Saudi Arabi');
INSERT INTO `countries` (`countryName`) VALUES ('Senegal');
INSERT INTO `countries` (`countryName`) VALUES ('Seyshelles');
INSERT INTO `countries` (`countryName`) VALUES ('Sicily');
INSERT INTO `countries` (`countryName`) VALUES ('Sierra Leone');
INSERT INTO `countries` (`countryName`) VALUES ('Singapore');
INSERT INTO `countries` (`countryName`) VALUES ('Slovakia');
INSERT INTO `countries` (`countryName`) VALUES ('Slovenia');
INSERT INTO `countries` (`countryName`) VALUES ('Soloman Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Somali');
INSERT INTO `countries` (`countryName`) VALUES ('South Africa');
INSERT INTO `countries` (`countryName`) VALUES ('Spain');
INSERT INTO `countries` (`countryName`) VALUES ('Spanish Territories of N Africa');
INSERT INTO `countries` (`countryName`) VALUES ('Spitzbergen');
INSERT INTO `countries` (`countryName`) VALUES ('Sri Lanka');
INSERT INTO `countries` (`countryName`) VALUES ('Sudan');
INSERT INTO `countries` (`countryName`) VALUES ('Suriname');
INSERT INTO `countries` (`countryName`) VALUES ('Swaziland');
INSERT INTO `countries` (`countryName`) VALUES ('Sweden');
INSERT INTO `countries` (`countryName`) VALUES ('Switzerland');
INSERT INTO `countries` (`countryName`) VALUES ('Syria');
INSERT INTO `countries` (`countryName`) VALUES ('Tiawan');
INSERT INTO `countries` (`countryName`) VALUES ('Tajikistan');
INSERT INTO `countries` (`countryName`) VALUES ('Tanzania');
INSERT INTO `countries` (`countryName`) VALUES ('Thailand');
INSERT INTO `countries` (`countryName`) VALUES ('Togo');
INSERT INTO `countries` (`countryName`) VALUES ('Tonga');
INSERT INTO `countries` (`countryName`) VALUES ('Trinidad & Tobago');
INSERT INTO `countries` (`countryName`) VALUES ('Tristan da Cunha');
INSERT INTO `countries` (`countryName`) VALUES ('Tunisia');
INSERT INTO `countries` (`countryName`) VALUES ('Turkey3');
INSERT INTO `countries` (`countryName`) VALUES ('Turkmenistan');
INSERT INTO `countries` (`countryName`) VALUES ('Turks & Caicos Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Tuvalu');
INSERT INTO `countries` (`countryName`) VALUES ('Uganda');
INSERT INTO `countries` (`countryName`) VALUES ('Ukraine');
INSERT INTO `countries` (`countryName`) VALUES ('United Arab Emirates');
INSERT INTO `countries` (`countryName`) VALUES ('Uruguay');
INSERT INTO `countries` (`countryName`) VALUES ('Uzbekistan');
INSERT INTO `countries` (`countryName`) VALUES ('Vanuatu');
INSERT INTO `countries` (`countryName`) VALUES ('Vatican City State');
INSERT INTO `countries` (`countryName`) VALUES ('Venezuela');
INSERT INTO `countries` (`countryName`) VALUES ('Vietnam');
INSERT INTO `countries` (`countryName`) VALUES ('Virgin Islands (USA)');
INSERT INTO `countries` (`countryName`) VALUES ('Wake Island');
INSERT INTO `countries` (`countryName`) VALUES ('Wallis & Futuna Islands');
INSERT INTO `countries` (`countryName`) VALUES ('Western Samo');
INSERT INTO `countries` (`countryName`) VALUES ('Yemen');
INSERT INTO `countries` (`countryName`) VALUES ('Yugoslavia');
INSERT INTO `countries` (`countryName`) VALUES ('Zambia');
INSERT INTO `countries` (`countryName`) VALUES ('Zimbabwe');
 
Last edited:
That's one way to do it... or you could use MySQL's LOAD DATA syntax.

For ease of processing, you would replace those double line-breaks, e.g. between Algeria and Albania, with a single line break so every country is on its own line.

Your query would then take the form of something like:
Code: 
LOAD DATA INFILE 'countries.txt' 
INTO TABLE countries 
LINES TERMINATED BY ';\n';
 
Augmented said:
That's one way to do it... or you could use MySQL's LOAD DATA syntax.

For ease of processing, you would replace those double line-breaks, e.g. between Algeria and Albania, with a single line break so every country is on its own line.

Your query would then take the form of something like:
Code: 
LOAD DATA INFILE 'countries.txt' 
INTO TABLE countries 
LINES TERMINATED BY ';\n';
Click to expand...

Well I've learnt something new here today anyway!
 
wow thanks very much fishfluff and Augmented. I too have learnt something today, I used Augmenteds method. Though I appreciate the work it took fishfluff to do your method. I thank you lots both! Cheers!
 
You must log in or register to reply here.
Back
Top Bottom