[Mysql] Import character Issue with worldcitiespop.txt

This section is where GeoLite users can find support from other users.

[Mysql] Import character Issue with worldcitiespop.txt

Postby maforast » Wed Oct 19, 2011 9:04 am

Hi, I have imported the worldcitiespop.txt file on my mysql db, but the character are illegible. I have wrong anything?

I make this step:

Code: Select all
-- Database: `world`
CREATE DATABASE `world`;
USE `world`;

-- Table: `cities`
CREATE TABLE IF NOT EXISTS `cities` (
  `Country` CHAR(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `City` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `AccentCity` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `Region` CHAR(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `Population` INT(10) UNSIGNED NOT NULL,
  `Latitude` FLOAT NOT NULL,
  `Longitude` FLOAT NOT NULL,
  KEY `AccentCity` (`AccentCity`(5)),
  KEY `City` (`City`(5))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


LOAD DATA LOCAL INFILE 'worldcitiespop.txt'
  INTO TABLE `cities`
  FIELDS
    TERMINATED BY ','
  LINES
    TERMINATED BY '\n'
  IGNORE 1 LINES;


In this state my db is useless!

tnx in advance
maforast
 
Posts: 1
Joined: Wed Oct 19, 2011 8:54 am

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby djlaslo » Sun Nov 06, 2011 4:12 pm

Hi,

Same question, still no answer, even from MaxMind support which forwarded me to the forum...

Anybody? I know it sounds newbee, the .txt seems to be bytecode, however anybody could help us?

Thanks,
djlaslo
 
Posts: 5
Joined: Sun Nov 06, 2011 4:10 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby djlaslo » Sat Nov 12, 2011 4:12 pm

Nobody can help us? Even Maxmind seems not want to support us...
djlaslo
 
Posts: 5
Joined: Sun Nov 06, 2011 4:10 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby petermm » Fri Nov 18, 2011 6:52 pm

City names are in ISO-8859-1 encoding, try that instead of UTF-8.

Peter
petermm
Site Admin
 
Posts: 22
Joined: Mon May 09, 2011 6:33 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby djlaslo » Sun Nov 20, 2011 5:29 pm

Hi,

Thank you foryour answer, but unfortunately it still does not work.

The problem is the content of the file is "un-usable" as it is. In fact, it seems to be bytecode (database encrypted data, double tar.gz data...i do not know) when you open it in notepad...

Anyone could review quickly the file to see what is the problem? here is the link : http://www.maxmind.com/download/worldci ... pop.txt.gz

I asked maxmind for support, but they answered that they do not provide support for free features.

Thanks in advancce,
djlaslo
 
Posts: 5
Joined: Sun Nov 06, 2011 4:10 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby djlaslo » Tue Nov 22, 2011 1:51 pm

Hi,

I sent a mail back to maxmind support but still no answer.

Sorry for the spam, but coudl anybody help us?

Thanks in advance,
djlaslo
 
Posts: 5
Joined: Sun Nov 06, 2011 4:10 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby citruscaesar » Mon Nov 28, 2011 9:18 pm

I'm having this problem as well, I have no idea what the solution is! If anyone COULD help, it would be great.

I've contacted MaxMind, and they said the "encoding was latin1", but this does not fix anything.
citruscaesar
 
Posts: 3
Joined: Mon Nov 28, 2011 9:15 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby petermm » Tue Nov 29, 2011 7:54 pm

Can someone send me a sample of the text file (not the URL, but just a copy/paste of the actual data). I am looking at it in Notepad and it seems fine to me.

Peter
petermm
Site Admin
 
Posts: 22
Joined: Mon May 09, 2011 6:33 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby citruscaesar » Tue Nov 29, 2011 10:08 pm

Here's a sample from the notepad file:

‹i‚¸M worldcitiespop.txt ¤ýYs$·’
¾Ï¯È7Θ‘e±/”Ô§ÔªSjIÔ9gZO VÁÜŠ™•üµÝf×ìÚ¼Îý¾‘ ÕWVJ ÁϬ‡ÃýÇÍ°Þo·?öûãíý—/ízÑ?Ú—~³¾ýmóuX6{ˆþÝûaÞÞþ}³~ÁØÿ«™ß6ý5»Ûûþ¿þ{w··Yò!«Røï6þð_ɸ~Ûk–=€%*„3ù»´Ø¥k¡~äF€›Ë8.9¢×óÍvÛÜÞs•ˆÍ!·Ø~ýu¶lfßÚåRáðàŸøÀÐ%Q–F׉ï¾õm¿\¶B{÷ONû^)è ênyš=>šô†3ÂÕ®Yšžu ±(#¤mܘŽ³>þ_¦ãä„K¥ãp q[“óí=þJ~ÚÁÜü¶=¼× )<{·Û¸ÃS¿>š’bøßÚrí+Ã7Pxxê¿ô»f»íoèÿOŽùºÿSÿ_íN4 Ð7üׄ7|iÖ¦u6·?r%·iR'ã:(¹kv³o½é?bôŸÒÓ6pòn·ûv»»ý‘ÃË Qúÿ]ôv<ÿ[hh·ËÙס¿ý·åì7H{žt»v¹›=mLßø7ù"çbÞd¹kÍÛC®㌋X_žKÆ_—
çüÆLŽû_‘ºYï¨ÈúOŠãåÃöÛvE¥ø“bRêü¬ÀÐP˜é?1"yŽÛ´]iV_oÿ“Y'Iv½Ö/›íÜ AC!±¨¢ŒOëaûÅTüœfþû;üýýêß[ø{{ýï{È5ÿÝ—f97kÌ¿ID
:ž'×
®õáôß»k}êy³Þ7ëvÞÜþMcî àŒº×á©…‘ù ‡³•N¨oŒúæA™5ãËf»ï׍yßß

just copied and pasted. I personally have tried opening the file in Microsoft Word in every single encoding type available, but it looks messy like this each time. I assume the file's data should be able to be viewed just by opening the file?

Thanks
citruscaesar
 
Posts: 3
Joined: Mon Nov 28, 2011 9:15 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby djlaslo » Wed Nov 30, 2011 2:21 pm

Hi,

I found the solution with: the file is twice zipped (tar.gz), but in the wrong way.

So here is the process : gunzip the tar.gz file. You gonna have a worldcitiespop.txt. Rename this file as a tar.gz. Gunzip (force if it's required) this file. You gonna obtain a worldcitiespop.tar file. Rename this file as a txt and here is it!
djlaslo
 
Posts: 5
Joined: Sun Nov 06, 2011 4:10 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby citruscaesar » Wed Nov 30, 2011 5:36 pm

Thank you Dijaslo, you star!! So glad it now works!

Please could an employee at MaxMind fix the file so that it doesn't frustrate any more people!
citruscaesar
 
Posts: 3
Joined: Mon Nov 28, 2011 9:15 pm

Re: [Mysql] Import character Issue with worldcitiespop.txt

Postby petermm » Wed Nov 30, 2011 8:11 pm

I used 7zip and it worked fine for me the first time (hence my confusion above).

Peter
petermm
Site Admin
 
Posts: 22
Joined: Mon May 09, 2011 6:33 pm


Return to GeoLite Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron