Show/hide comment form
Home 
Conticreative Blogs The Conticreative Joomla Blog Export your Joomla users to a new site
Conticreative Blogs The Conticreative Joomla Blog Export your Joomla users to a new site
Blogs - The Conticreative Joomla Blog
Export your Joomla users to a new site
Written by Marco Conti Thursday, 27 November 2008 18:35
How to move your Joomla! users to a new site with PhpMyAdmin.
NOTE: Since I wrote this article a lot of things have changed in the Joomla! world.
A new component called CBJuice now can do the same process easier and faster. Download it here.
I will be writing a new article about the process and probably I'll produce a screencast on the subject as well. All the same, knowing how to interact with PhpMyAdmin is an invaluable skill. Read the entire article and apply it to your needs.
I needed to move about 300 registered users from my site www.hondagb500.com, built on Joomla 1.5RC3 to a staging site using Joomla 1.5.8. Back when I built the site, I made the mistake of using a Joomla 1.5 RC3 version, thinking that it would not be that much different from the final release, but I was wrong. As it turned out, very wrong. To make a long story short, by the time Joomla 1.5.8 rolled around I figured I neglected my users for way too long and the guilt was eating me alive.
I read up on the upgrade path from RC3 to the stable version and it was very complicated and clearly not very straightforward. So I decided to build a brand new site and move what little content I had over. The only problem I had was how to move my users.
I searched the Extension directory for good component that could do that for me, but it turned out that there weren't any. The only one I found was for Joomla 1.0 and I did not want to risk it. Plus, the instructions were almost as complicated as the upgrade path and the results far from assured.
I proceed to open up my database in PhpMyAdmin and I compared the jos_users table (I used the default jos_ for my DB) of the RC3 version with the newer Joomla 1.5 version. They looked identical.
So far so good, so I went ahead and I dumped the table to disk and I uploaded it to the new website. No dice. It worked, but none of my users showed up in the user list in Joomla.
Fortunately, I was using Community Builder and when I went to check it, that too was empty. But I had an idea. I used the "Tools" feature and I tried syncronizing the user table. That seemed to work, so I tried checking the CB database. That didn;t work too well, but it gave me some useful input. It showed that while CB was seeing the users, there were as many missing records as I had users in these two tables:
- jos_core_acl_aro
- jos_core_acl_aro_map
The answer was clear: I needed those table too. So I dumped those tables as well and I uploaded them to PhpMyAdmin.
Sure enough, I go back to the CB tools and after running the synchronization all my users showed up. I went back to the regular Joomla user list and they were all there as well. What's more, their passwords also worked (at least my own registered user test account did).
That's great, you'll say, but how do I do it? Easy, follow these steps:
I'll assume you have one originating Joomla site and one new Joomla site without users (except for your account). Remember to make a note of your old admin account as that will be the one that will let you back into the site. Remember to back up both of them before you proceed.
- Go to your PhpMyAdmin and select your old database
- Select the table "jos_users" (if you don't use jos_" change appropriately) in the left side list
- Click on "export" on the upper tabs
- Select:
- File type: sql
- Under "Structure" select "Drop Table" and deselect "If not exist"
- Check the "Save as File" at the bottom of the page.
- Click "Go" and save to disk. It will help if you keep your folders organized.
- Now, repeat the process for the tables:
- jos_core_acl_aro
- jos_core_acl_aro_map
- Switch to your new database
- Go to "jos_users" and click on the "Import" tab.
- Click on "Browse" and select the jos_users table
- Keep the radio button below on SQL and click "GO"
- Repeat for the other tables
- Go to your site and make sure that the import went well.
Should you have any problems during the course of the transfer, reload the backups and start from scratch.
If you use Community Builder, make sure you Synchronize your users (Components > Community Builder > Tools > Sync users) and pay attention to any errors you may see.
If you are familiar with PhpMyAdmin, at this stage you can also do several things to your user tables, such as approving those that did not confirm their registration, giving different privileges to some users, etc. You can download any of those tables in .csv format and you'll see that the data actually makes sense and it allows you make changes quite easily.
In conclusion, there is no need to get fancy scripts to move users to another Joomla site. This method is easy and fairly foolproof. You can always start from scratch and unless you make a serious mistake you are not touching the original.
Set as favorite
Bookmark
Email this
Hits: 26040
Trackback(0)
TrackBack URI for this entryComments (14)
Subscribe to this comment's feedShow/hide comments
Regarding Exporting Users from One Website to Another
Hello
Greetings!!
First of all i would like to thanks the web master of this website for creating this page.
Today i am able to export my web users, because of the hint i have taken from this website post.
I would like to add one more table name for successfully Exporting joola1.5 users..
and the name is jos_core_acl_groups_aro_map
This table also need to be moved along with jos_users and jos_core_acl_aro
Greetings!!
First of all i would like to thanks the web master of this website for creating this page.
Today i am able to export my web users, because of the hint i have taken from this website post.
I would like to add one more table name for successfully Exporting joola1.5 users..
and the name is jos_core_acl_groups_aro_map
This table also need to be moved along with jos_users and jos_core_acl_aro
,
January 15, 2011
-
- +1
-
-
only super admins are transferred
hi
i used same procedure as u prescribed in the beginning for joomla 1.5, but in jooml backend it does shows only superadmins of site and others users are not shown. though in phpmyadmin it does shows all user list.
plz help urgently
thanx!
i used same procedure as u prescribed in the beginning for joomla 1.5, but in jooml backend it does shows only superadmins of site and others users are not shown. though in phpmyadmin it does shows all user list.
plz help urgently
thanx!
,
January 05, 2011
-
- +0
-
-
only super admins are transferred
hi,
i followed same procedure as u prescribed in the begening for joomla 1.5 but only super admins are transferred though in phpmyadmin it does shows all users please help what to do
i followed same procedure as u prescribed in the begening for joomla 1.5 but only super admins are transferred though in phpmyadmin it does shows all users please help what to do
,
January 04, 2011
-
- +1
-
-
only supper admins are transferred
hi
i tried to move my old users using the method u prescribed. i transferred all three tables in similar way as u told but is nt working properly. in backend administrator it does only shows super admins and other registered users are not shown there .though it shows all users in phpmyadmin .
i tried to move my old users using the method u prescribed. i transferred all three tables in similar way as u told but is nt working properly. in backend administrator it does only shows super admins and other registered users are not shown there .though it shows all users in phpmyadmin .
,
January 04, 2011
-
- +0
-
-
Migrating users from Joomla 1.0 to 1.5: Failsafe method
Migrating users from Joomla 1.0 to 1.5.
This initially was a difficult task, but using a few steps I was able to complete it using an open-source extension. Even with this extension there are a number of steps you need to take that I was not aware of, and which took me a while to get through. However, I have heard that many people find importing users a very difficult task from 1.0 and importing them into 1.5 without a full migration, this definitely worked for me.
Please be aware that this only imports the following fields:
name,username,email,password
It does not import
ID
Usertype
Block
SendEmail
gid
registerDate
lastvisitDate
activation
params
If you need those fields imported then you will probably have to do a full migration, as I was unable to find any way to export and import all of these fields easily. Usertype can be chosen when you import the data, as you will see.
1) Log into phpMyAdmin on your 1.0 site
2) Select the database of your 1.0 site
3) Click the Export tab
4) At the left, select *only* the jos_users section to export. If your prefix is different, I am not sure if this method will work when you import the database, but it should.
5) Select CSV for MS Excel
6) Check the "Put fields names in the first row" option at the right
7) Check the Save As item at the bottom, compression is none, then click Go, and save the file.
You have now exported your user database. Open this csv file in Excel or some other spreadsheet program.
9) Delete all columns except "name", "username", "email", and "password". Save this file.
10) Open this file again in a text editor. You should see multiple rows with your field items separated only by a comma.
11) Now its time to install the importer. Go here: http://extensions.joomla.org/e...30/details
And navigate your way to download the 1.7 or later version of Userport. Make sure it's at least 1.7; the older versions cause errors.
12) Install this on your 1.5 site. Navigate to this component in your backend.
13) Set your parameters for "Given passwords are already properly encoded" to YES.
Select User type as "registered" or whatever type of user you are importing. Everything else is up to you, but this should be the only two you need to set.
IMPORTANT: If you have a lot of users in multiple user groups, such as 200 authors, 300 admins, and 1000 registred users, you will probably have to import these groups one at a time, otherwise you will have to go back and change their status to the correct one. For 5 super admins, for example, this is not a problem, but for a wide variety of users, the process would be more complex. You would filter out of your csv FIRST all users who are authors, save the file with only the authors, THEN delete all the fields like in step 9, and THEN import these users as Authors into Userport. You'd need to do this for each group.
14) Click import, then check the No Initial Text radio box, then click continue.
15) On the next page, copy a single line of a user from your open CSV file (which is open in your text editor) you are able to log into the old 1.0 site with, such as an admin account. Paste this into the CSV field, then click add.
16) If all goes well, you should see no errors, and then you can navigate to your current sites front end and try logging in as the user you just imported.
17) Now that you know it works, you can import them all at once this way by pasting your entire csv file in and adding all the users. If your csv file has a "" in it for a user, it might throw an error, but this doesn’t break the process.
1 Now go into your user database and alter any admin or superadmin or special usergroups that people were assigned to. If you have a wide variety of people assigned to usergroups, this method may require additional editing of users after they have been imported.
It took me quite a while to figure this out; this is the simplest way to do this. I went through a lot of paint o figure this out, as I could never get the user fields to export properly from the old site. This method is relatively painless.
This initially was a difficult task, but using a few steps I was able to complete it using an open-source extension. Even with this extension there are a number of steps you need to take that I was not aware of, and which took me a while to get through. However, I have heard that many people find importing users a very difficult task from 1.0 and importing them into 1.5 without a full migration, this definitely worked for me.
Please be aware that this only imports the following fields:
name,username,email,password
It does not import
ID
Usertype
Block
SendEmail
gid
registerDate
lastvisitDate
activation
params
If you need those fields imported then you will probably have to do a full migration, as I was unable to find any way to export and import all of these fields easily. Usertype can be chosen when you import the data, as you will see.
1) Log into phpMyAdmin on your 1.0 site
2) Select the database of your 1.0 site
3) Click the Export tab
4) At the left, select *only* the jos_users section to export. If your prefix is different, I am not sure if this method will work when you import the database, but it should.
5) Select CSV for MS Excel
6) Check the "Put fields names in the first row" option at the right
7) Check the Save As item at the bottom, compression is none, then click Go, and save the file.
You have now exported your user database. Open this csv file in Excel or some other spreadsheet program. 9) Delete all columns except "name", "username", "email", and "password". Save this file.
10) Open this file again in a text editor. You should see multiple rows with your field items separated only by a comma.
11) Now its time to install the importer. Go here: http://extensions.joomla.org/e...30/details
And navigate your way to download the 1.7 or later version of Userport. Make sure it's at least 1.7; the older versions cause errors.
12) Install this on your 1.5 site. Navigate to this component in your backend.
13) Set your parameters for "Given passwords are already properly encoded" to YES.
Select User type as "registered" or whatever type of user you are importing. Everything else is up to you, but this should be the only two you need to set.
IMPORTANT: If you have a lot of users in multiple user groups, such as 200 authors, 300 admins, and 1000 registred users, you will probably have to import these groups one at a time, otherwise you will have to go back and change their status to the correct one. For 5 super admins, for example, this is not a problem, but for a wide variety of users, the process would be more complex. You would filter out of your csv FIRST all users who are authors, save the file with only the authors, THEN delete all the fields like in step 9, and THEN import these users as Authors into Userport. You'd need to do this for each group.
14) Click import, then check the No Initial Text radio box, then click continue.
15) On the next page, copy a single line of a user from your open CSV file (which is open in your text editor) you are able to log into the old 1.0 site with, such as an admin account. Paste this into the CSV field, then click add.
16) If all goes well, you should see no errors, and then you can navigate to your current sites front end and try logging in as the user you just imported.
17) Now that you know it works, you can import them all at once this way by pasting your entire csv file in and adding all the users. If your csv file has a "" in it for a user, it might throw an error, but this doesn’t break the process.
1
Now go into your user database and alter any admin or superadmin or special usergroups that people were assigned to. If you have a wide variety of people assigned to usergroups, this method may require additional editing of users after they have been imported. It took me quite a while to figure this out; this is the simplest way to do this. I went through a lot of paint o figure this out, as I could never get the user fields to export properly from the old site. This method is relatively painless.
,
August 27, 2010
-
- -1
-
-
joomla 1.5 component for user export import
There is a new joomla extension who can do export/import, is Joomla! 1.5 native, you can find it on JED:
http://extensions.joomla.org/e...ort/12607
There is a lot of documentation on site and is working great, is not working on Joomla! 1.0 but only on 1.5
http://extensions.joomla.org/e...ort/12607
There is a lot of documentation on site and is working great, is not working on Joomla! 1.0 but only on 1.5
,
August 17, 2010
-
- +0
-
-
You're Awsome
Thanks for posting this! Saved me hours of frustration when trying to move yet another site. You rock!
,
June 18, 2010
-
- -1
-
-
Cant log in to back end
I have a 1.0 site that I look after for a local sporting club. I decided to update it to a 1.5 site to look a little more professional. No probs there and it is online under a sub directory.
The thing is that I transferred all of the user over using your procedure:
and now I can't log in. I have tried the procedures listed via the forum searches:
http://docs.joomla.org/How_you_reset_an ... assword?
and have requested the password via the login page, then I put the token in, etc, etc. but still to no avail.
The error I get is "you do not have access to the administrator section of this site".
I have created a new user via the phpadmin but still no good. i did back up the original site but I need to be able to login as well as keep the transferred users in the new site as there is nearly 200 of them.
Any help you be very much appreciated.
original site URL is tetigers.com.au and the new one is tetigers.com.au/test
One thing to note is that as per the procedure it states to save and update jos_core_acl_aro_map entry but the 1.0 does not have such an entry. It does have a jos_core_acl_groups_aro_map entry though
Thanks very much,
John
The thing is that I transferred all of the user over using your procedure:
and now I can't log in. I have tried the procedures listed via the forum searches:
http://docs.joomla.org/How_you_reset_an ... assword?
and have requested the password via the login page, then I put the token in, etc, etc. but still to no avail.
The error I get is "you do not have access to the administrator section of this site".
I have created a new user via the phpadmin but still no good. i did back up the original site but I need to be able to login as well as keep the transferred users in the new site as there is nearly 200 of them.
Any help you be very much appreciated.
original site URL is tetigers.com.au and the new one is tetigers.com.au/test
One thing to note is that as per the procedure it states to save and update jos_core_acl_aro_map entry but the 1.0 does not have such an entry. It does have a jos_core_acl_groups_aro_map entry though
Thanks very much,
John
,
January 27, 2010
|
-
- +0
-
-
Porting J! 1.0.x to J!1.5 Users manually
First off, Thank you guys for the great start. I manage to complete a the port. "Still need to be tested though" but it worked. There are some important thing to note though, there were certain table columns that needed remaining because the were updated in 1.5. Although I used mtwMigrator to export the data to an sql file the mtwMigrator still carried the column name of the older tables, as a result the usermanger failed to display the user or query the tables on a login request. I have listed the filed change below: Please not the change in the jos_core_acl_aro table
(J!1.5)
_____________________________________________
Table: jos_core_acl_aro
id int(11)
section_value varchar(240)
value varchar(240)
order_valueint(11)
namevarchar(255)
hiddenint(11)
KeynameType
PRIMARY PRIMARY
tbl_section_value_value_aro UNIQUE
tbl_gacl_hidden_aro INDEX
____________________________________________________
Table: jos_core_acl_groups_aro_map
group_idint(11)
section_valuevarchar(240)
aro_idint(11)
Keyname Type
group_id_aro_id_groups_aro_map UNIQUE
_______________________________________________________
Table: jos_users
idint(11)
namevarchar(50)
usernamevarchar(25)
emailvarchar(100)
passwordvarchar(100)
usertypevarchar(25)
blocktinyint(4)
sendEmailtinyint(4)
gidtinyint(3)
registerDatedatetime
lastvisitDatedatetime
activationvarchar(100)
paramstext
KeynameType
PRIMARY PRIMARY
usertype INDEX
idx_name INDEX
_______________________________________________________
_
(J!1.0) ACL table Joins
Table: jos_user
idint(11)
namevarchar(255)
usernamevarchar(150)
emailvarchar(100)
passwordvarchar(100)
usertypevarchar(25)
blocktinyint(4)
sendEmailtinyint(4)
gidtinyint(3)UNSIGNED
registerDatedatetime
lastvisitDatedatetime
activationvarchar(100)
paramstextutf8_general_ci
KeynameType
PRIMARY PRIMARY
usertype INDEX
idx_name INDEX
gid_blockINDEX
username INDEX
email INDEX
Table: jos_core_acl_aro
aro_idint(11)
section_valuevarchar(240)
valuevarchar(240)
order_valueint(11)
namevarchar(255)
hiddenint(11)
jos_gacl_section_value_value_aro
jos_gacl_hidden_aro
Regards.
(J!1.5)
_____________________________________________
Table: jos_core_acl_aro
id int(11)
section_value varchar(240)
value varchar(240)
order_valueint(11)
namevarchar(255)
hiddenint(11)
KeynameType
PRIMARY PRIMARY
tbl_section_value_value_aro UNIQUE
tbl_gacl_hidden_aro INDEX
____________________________________________________
Table: jos_core_acl_groups_aro_map
group_idint(11)
section_valuevarchar(240)
aro_idint(11)
Keyname Type
group_id_aro_id_groups_aro_map UNIQUE
_______________________________________________________
Table: jos_users
idint(11)
namevarchar(50)
usernamevarchar(25)
emailvarchar(100)
passwordvarchar(100)
usertypevarchar(25)
blocktinyint(4)
sendEmailtinyint(4)
gidtinyint(3)
registerDatedatetime
lastvisitDatedatetime
activationvarchar(100)
paramstext
KeynameType
PRIMARY PRIMARY
usertype INDEX
idx_name INDEX
_______________________________________________________
_
(J!1.0) ACL table Joins
Table: jos_user
idint(11)
namevarchar(255)
usernamevarchar(150)
emailvarchar(100)
passwordvarchar(100)
usertypevarchar(25)
blocktinyint(4)
sendEmailtinyint(4)
gidtinyint(3)UNSIGNED
registerDatedatetime
lastvisitDatedatetime
activationvarchar(100)
paramstextutf8_general_ci
KeynameType
PRIMARY PRIMARY
usertype INDEX
idx_name INDEX
gid_blockINDEX
username INDEX
email INDEX
Table: jos_core_acl_aro
aro_idint(11)
section_valuevarchar(240)
valuevarchar(240)
order_valueint(11)
namevarchar(255)
hiddenint(11)
jos_gacl_section_value_value_aro
jos_gacl_hidden_aro
Regards.
,
October 24, 2009
|
-
- +1
-
-
...
I believe Marco has the right idea ... but with one little mistake. The 3 tables needed for a user transfer are ... jos_core_acl_aro, jos_core_acl_groups_aro_map, jos_users ... NOTE: jos_core_acl_groups_aro_map is needed .... NOT jos_core_acl_aro_map as outlined before.
I am pretty sure this will work between 1.0x and 1.5.x but i have not tested that recently and can't confirm, but it defn works between versions of 1.5.x including 1.5.14(current vers.).
The CB sync will work as Marco suggests once the correct 3 tables are moved across to your new install.
AS ALWAYS!! bacup any database tables before doing anything!!! ... it still amazes me how many people dont do this ... at least then you can revert back to how things were if something goes wrong.
I am pretty sure this will work between 1.0x and 1.5.x but i have not tested that recently and can't confirm, but it defn works between versions of 1.5.x including 1.5.14(current vers.).
The CB sync will work as Marco suggests once the correct 3 tables are moved across to your new install.
AS ALWAYS!! bacup any database tables before doing anything!!! ... it still amazes me how many people dont do this ... at least then you can revert back to how things were if something goes wrong.
,
August 12, 2009
|
-
- +5
-
-
Write comment
You can add your comment here
| < Prev | Next > |
|---|
Resources
Latest Blogs
Joomla Training
Conticreative offers Individual and Corporate training (in person or online) on Joomla, Wordpress, Zen Cart and other leading Open Source scripts.
Books
