convert mysql data latin1 to utf-8?

A place to discuss Joomla! translation matters.

Moderator: wendhausen

Locked
User avatar
alavi nik
Joomla! Explorer
Joomla! Explorer
Posts: 344
Joined: Wed Sep 20, 2006 7:48 pm
Location: Tehran,Iran
Contact:

convert mysql data latin1 to utf-8?

Post by alavi nik » Fri Jun 20, 2008 11:59 am

Hello
in joomla 1.0.X series,database was latin1
and saved data same : U…?آ±Uئ’?آ² Uâ€،Uâ€

but in joomla 1.5 have change,and saved good data
for example U…?آ±Uئ’?آ² Uâ€،U†==> ُسلام.تست

how may i convert mysql to utf8?and solved charsets? and chars?

com_migrator,done this work
but is just for content,users and ...
but is not for fully database

Thanks
Joomfa Team(Joomla farsi) ==> http://joomfa.org

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: convert mysql data latin1 to utf-8?

Post by infograf768 » Sat Jun 21, 2008 7:28 am

Hello Mostafa,

Just make a dump of the database choosing utf8 as encoding.
Then, to be sure, open the dump in an utf8 editor and check all is OK.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

User avatar
alavi nik
Joomla! Explorer
Joomla! Explorer
Posts: 344
Joined: Wed Sep 20, 2006 7:48 pm
Location: Tehran,Iran
Contact:

Re: convert mysql data latin1 to utf-8?

Post by alavi nik » Sat Jun 21, 2008 10:50 am

Hello jeans
thanks

i going to phpmyadmin,export joomla 1.0.x series database.
then i open this file into notepad++

and id not good

Thanks jean
Joomfa Team(Joomla farsi) ==> http://joomfa.org

aldoy
Joomla! Apprentice
Joomla! Apprentice
Posts: 13
Joined: Fri Mar 31, 2006 5:46 pm

Re: convert mysql data latin1 to utf-8?

Post by aldoy » Mon Jul 14, 2008 6:23 pm

Same problem here, how can I export as UTF-8?

yamadix
Joomla! Apprentice
Joomla! Apprentice
Posts: 33
Joined: Fri Jun 09, 2006 7:44 pm
Location: Belgium (Charleroi)

Re: convert mysql data latin1 to utf-8?

Post by yamadix » Wed Apr 01, 2009 8:31 pm

Hello,

Simply ... export the tables with phpmyadmin in SQL format. Edit the .sql file with Wordpad (or other editor), replace "latin1" with "utf8" and save the file. Then, import the file with phpmyadmin...
Cordialement,

Yamadix

xenome
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Tue May 12, 2009 10:37 pm

Re: convert mysql data latin1 to utf-8?

Post by xenome » Tue May 12, 2009 10:40 pm

@yamadix

thanx for the reply friend ... but there's a little problem ... my exported .sql backup from phpmyadmin doesn't show character sets for tables creation .. am i missing an option or so in phpmyadmin ??

yamadix
Joomla! Apprentice
Joomla! Apprentice
Posts: 33
Joined: Fri Jun 09, 2006 7:44 pm
Location: Belgium (Charleroi)

Re: convert mysql data latin1 to utf-8?

Post by yamadix » Wed May 13, 2009 7:22 pm

Hello,

In the SQL-Export, for each table, you must find this line :
ENGINE=MyISAM DEFAULT CHARSET=latin1;
If no, I am sorry ... I don't have an other response !
Cordialement,

Yamadix

xenome
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Tue May 12, 2009 10:37 pm

Re: convert mysql data latin1 to utf-8?

Post by xenome » Thu May 14, 2009 6:06 am

all that i got is Type=MyISAM ... only. Anyway i'll give it a try with ENGINE=MyISAM DEFAULT CHARSET=latin1; instead of the Type may be it'll work

User avatar
Sulpher
Joomla! Explorer
Joomla! Explorer
Posts: 289
Joined: Mon May 21, 2007 8:54 am
Contact:

Re: convert mysql data latin1 to utf-8?

Post by Sulpher » Sat Sep 26, 2009 8:53 pm

xenome, take in mind next SQL queries:
DB charset replacement: ALTER DATABASE `db_name` COLLATE utf8_general_ci
Table charset replacement: ALTER TABLE `table_name` COLLATE utf8_general_ci

Where db_name and table_name - you have to use the right data.
Joomla! extensions & ecommerce pluginshttps://norrnext.com

Unofficial Joomla channel on Telegram - @joomlahub
Follow me on Twitter: @sulpher

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: convert mysql data latin1 to utf-8?

Post by infograf768 » Sun Sep 27, 2009 8:40 am

xenome wrote:all that i got is Type=MyISAM ... only. Anyway i'll give it a try with ENGINE=MyISAM DEFAULT CHARSET=latin1; instead of the Type may be it'll work
This WILL NOT work in Joomla 1.5.
1.5 is fully utf8.
The default charset does not need to be set in the dump if the database is correctly collated.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

joomlan
Joomla! Explorer
Joomla! Explorer
Posts: 345
Joined: Sun Jul 16, 2006 1:21 pm

Re: convert mysql data latin1 to utf-8?

Post by joomlan » Thu Feb 18, 2010 7:18 am

Hi,

I got similar problem, but I can't edit my dump file because it's 400 MB. I used the iconv to convert the database dump to UTF8 from Latin1, when I import it to the Joomla 1.5.15 it shows garbage and changes the system info in Joomla from UTF8 to Latin 1, Any idea how can I fix this problem?

Thanks

User avatar
emakki
Joomla! Ace
Joomla! Ace
Posts: 1686
Joined: Fri Dec 01, 2006 9:26 am
Location: Centreville, Virginia
Contact:

Re: convert mysql data latin1 to utf-8?

Post by emakki » Tue Mar 09, 2010 2:39 am

We have discussed this issue thoroughly in the Arabic forum and solve the issue. There are several things to put in mind like collision must be utf-8 for all fields as well. I used a script to do that for me and I attached it (use on your own risk and you must backup your database). go to this link:
http://forum.joomla.org/viewtopic.php?p=823601#p823601
rename the file to index.php and upload in a folder in your website. Finally, enter db info and it will change all necessary fields, tables and so on to utf-8

A trick I used as well for Arabic, I assume for Urdu it will work, is to insert the contents of your .sql file in an html page (as an article in the old joomla that used to work) then show the page online and it will show the sql code along with the correct text in your language. Copy it from the page and past in notepad++ and save as utf without BOM. Then use it to export to your db.

I hope this helps.

Good luck,
Zak
Live and learn.. No matter what you earn..Money or knowledge.. From market or college..
_______
http://alhost.org هل تبحث عن إستضافة؟ مقارنة بين أفضل شركات الإستضافة
http://alhost.me دليل ومعاينة شركات الاستضافة العربية

modhumiah
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Wed Feb 24, 2010 2:14 am

Re: convert mysql data latin1 to utf-8?

Post by modhumiah » Tue Mar 09, 2010 4:10 am

emakki wrote:We have discussed this issue thoroughly in the Arabic forum and solve the issue. There are several things to put in mind like collision must be utf-8 for all fields as well. I used a script to do that for me and I attached it (use on your own risk and you must backup your database). go to this link:
http://forum.joomla.org/viewtopic.php?p=823601#p823601
rename the file to index.php and upload in a folder in your website. Finally, enter db info and it will change all necessary fields, tables and so on to utf-8

A trick I used as well for Arabic, I assume for Urdu it will work, is to insert the contents of your .sql file in an html page (as an article in the old joomla that used to work) then show the page online and it will show the sql code along with the correct text in your language. Copy it from the page and past in notepad++ and save as utf without BOM. Then use it to export to your db.

I hope this helps.

Good luck,
Zak
Thanks for the suggestion. I will give it a try.

joomlan
Joomla! Explorer
Joomla! Explorer
Posts: 345
Joined: Sun Jul 16, 2006 1:21 pm

Re: convert mysql data latin1 to utf-8?

Post by joomlan » Sat Mar 27, 2010 9:44 pm

I finally did it. These are the steps:



--------------------------

steps:

1- Install 1.5 without sample data
2- Install com_mtwmigrator-0.2.1 on the new Joomla 1.5
3- Migrate the users and groups only using com_mtwmigrator-0.2.1!
4- Install the Comporfiler
5- Migrate the Comprofiler Plugin data com_mtwmigrator-0.2.1
6- Delete Jos_content, Jos_content_frontpage, Jos_content_rating tables on the new installations
7- Backup the UTF8 converted jos_content, Jos_content_frontpage, Jos_content_rating tables individually.
8- Restore the jos_content, Jos_content_frontpage, Jos_content_rating tables into the new site.
9- The Jos_content table on the new 1.5 has 2 additional fields. Insert them by using the following 2 commands from MyPHP Admin


ALTER TABLE jos_content ADD alias VARCHAR(255) AFTER title;

ALTER TABLE jos_content ADD metadata text AFTER hits;

10- Install the Jomcomment component
11- Migrate the jomcomment data using the com_mtwmigrator from withing the new Joomla 1.5

--------

Converstion of the database:


Drop the database and create a new UTF8 database:

mysql --user=xx--password=xxx--execute="DROP DATABASE XXX; CREATE DATABASE XXX CHARACTER SET utf8 COLLATE utf8_general_ci;"



Restore the database:

mysql --user=XXX --password=XXX --default-character-set=utf8 XXX < XXX


Change the character Set:
sudo sed -e 's/latin1/utf8/g' -i XXX

sammy80
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Mon Jun 14, 2010 7:12 pm

Re: convert mysql data latin1 to utf-8?

Post by sammy80 » Mon Jun 14, 2010 7:21 pm

Thanks Joomlan,
I was stuck in this same UTF-8 problem for the last 3 days. So far your solution seems to work for me as well. Mostafa, yamadix, emakki .. thanks you guys too.
Gradually trying to learn Joomla.
http://www.biotechnologyforums.com - for biotech students
http://www.weightism.org - a [* spam *] community

iovcho
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Tue Feb 03, 2009 2:41 pm
Location: localhost
Contact:

Re: convert mysql data latin1 to utf-8?

Post by iovcho » Thu Jun 24, 2010 1:02 pm

Hello everybody!
I have the same problem.
I have website with 20MB database on Mambo 4.5.5 Stable [ Arpie ] 2-Feb-2007
The collation on mysql database is latin1_swedish_ci. The charset in index.php is windows-1251

I create a new website with joomla 1.5.18 with encoding and collation UTF8.
So , I try to convert the database from latin1 to utf8 with notepad++ , but not work - the cyrillic symbols are not correct.

All database from mambo is latin1_swedish_ci - mos_users, mos_content and etc.


This tips posted in this topic not work for cyrillic. :-(

Can you have a idea how can I migrate from Mambo 4.5.5 to Joomla with different collation in database and different charset in the templates?

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: convert mysql data latin1 to utf-8?

Post by infograf768 » Fri Jun 25, 2010 9:08 am

Have you tried to first migrate from Mambo to Joomla 1.0 and THEN migrate to 1.5?
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

iovcho
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Tue Feb 03, 2009 2:41 pm
Location: localhost
Contact:

Re: convert mysql data latin1 to utf-8?

Post by iovcho » Wed Jun 30, 2010 10:20 am

infograf768 wrote:Have you tried to first migrate from Mambo to Joomla 1.0 and THEN migrate to 1.5?

Thank you!
First I update from Mambo to Joomla 1.0.15 Stable
After Update from Joomla 1.0.15-Stable to Joomla 1.5.18

Thank you for the reply!
Best regards!

jasonsamual
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Fri Mar 16, 2012 4:11 am

Re: convert mysql data latin1 to utf-8?

Post by jasonsamual » Fri Mar 16, 2012 4:16 am

infograf768 wrote:Have you tried to first migrate from Mambo to Joomla 1.0 and THEN migrate to 1.5? Law of Attraction
I'm looking to go from 1.0.13 to 1.6 by way of going to 1.5 first.

I went down the long struggling road of getting Joomla, Coppermine and SMF integrated with a single login - handled by SMF (Joomlahacks).

I'm curious...will I lose these bridges while processing my upgrade to 1.5, or to 1.6 for that matter?

After much Research, i found:
No extensions available for anything that I have...I might have to stick with 1.0.13...too much stuff to sacrifice to make the move to 1.5...unless, there is a better migration script that does everything
Last edited by jasonsamual on Mon Mar 19, 2012 6:38 pm, edited 1 time in total.

swervebush
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Mon Mar 19, 2012 12:40 pm

Re: convert mysql data latin1 to utf-8?

Post by swervebush » Mon Mar 19, 2012 12:44 pm

joomlan wrote:I finally did it. These are the steps:



--------------------------

steps:

1- Install 1.5 without sample data
2- Install com_mtwmigrator-0.2.1 on the new Joomla 1.5
3- Migrate the users and groups only using com_mtwmigrator-0.2.1!
.
.
.
Change the character Set:
sudo sed -e 's/latin1/utf8/g' -i XXX

Working several hours. Trying to resolve this problem.
This method work for me. ;D

S. Bush

***** Manual signatures are NOT allowed *****_____
My personal website
http://www.ecerdc.com
Last edited by swervebush on Fri Mar 23, 2012 8:57 am, edited 3 times in total.

bogeyhooper
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Sat Apr 07, 2012 3:19 am

Re: convert mysql data latin1 to utf-8?

Post by bogeyhooper » Sat Apr 07, 2012 3:25 am

If you are running language that not English. I recommend to check if it's UTF-8 or not. As UTF-8 support almost every language.

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: convert mysql data latin1 to utf-8?

Post by infograf768 » Sat Apr 07, 2012 8:39 am

Use Phoca script.
Joomla HAS to be full UTF8

http://forum.joomla.org/viewtopic.php?f=617&t=606004
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

Justinaschwarz
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Tue Sep 13, 2011 7:07 am

Re: convert mysql data latin1 to utf-8?

Post by Justinaschwarz » Tue Nov 27, 2012 10:21 am

Actually pretty valuable concoction of encoding tricks. I've been searching around google for this solution. But I would like to share how I did it with a littly different way of steps, including a SED script to whack all those weird characters back into line, and the necessary adjustments for your MYSQL server.

Pariya
I've been banned!
Posts: 33
Joined: Tue Jan 15, 2013 7:06 am

Re: convert mysql data latin1 to utf-8?

Post by Pariya » Fri Jan 25, 2013 4:46 pm

ihave this problem too
i use a script that load the data from my database that have unicode latin
and then i instert in database with utf-8 unicode


Locked

Return to “Translations”