Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
By trumpcar on
Hello Drupal Community,
I recently needed to convert users from a Joomla database to Drupal 5.x. After doing some research, I settled on a "manual" approach using an Excel spreadsheet. The original spreadsheet was created by gareth_w at this URL. I've modified the spreadsheet, and enhanced it. Here's what it does:
- generates SQL you can use to populate the user table, map users to roles, and map other values to profile fields.
- Retain the timestamp that this user was created
- Corrected insertion syntax on USER table
- Added the ability to add data into profile_field table (i.e. the Joomla "name" field, for example)
Process:
1. Export the joomla user table into CSV
2. Open the CSV in Excel
3. Do any formatting required for the date field (this part was a bit weird, but the expected input format is in the spreadsheet)
4. Paste the required values into the appropriate fields (login, md5 password hash, email)
5. Optional stuff to change: timezone, status (1 = active, 0 = blocked), profile fields (add more if necessary)
6. Copy the generated SQL and run it through mysql using your favorite method.
Download here: Joomla to Drupal user conversion script.
I hope this assists others!
Regards,
Dev
DISCLAIMER - Your results may vary. Always make a backup!
Comments
Brilliant
I found some bugs in my version which it looks like you've fixed. I've also just got around to adding the roles table and that in, which again you've fixed. The only slight issue is I don't quite understand how the UNIX_TIMESTAMP function works, unless it is processed by the SQL on important.
In the whole a very useful package of improvements. I'm glad that together we've got something of use to the community. Hey - guess that finally means I did something open source! (feeling smug now!)
Update to user conversion script
Hey everyone,
I found out the hard way that we also need to populate the Drupal user table field "access", instead of leaving this as 0. This is important because, per this thread, Drupal needs to have this field populated with something in order to allow this user profile to be displayed on the front-end (i.e http://drupal-site.com/user/53, etc) by non-admin users. If you're an admin, this will work fine. If not, then you won't be able to view this profile until that particular user logs in (or you populate that field accordingly.
In my case, I don't really care to retain the previous information, so I'm going to bulk-update that field for all of my users to be now().
Here's an updated version of the script that ensures that the access and login fields are also timestamped correctly when generating the SQL:
http://www.devinbreen.com/files/drupal/joomla_to_drupal_user_table_sql.xls
And to answer your question, gareth_w, the UNIX_TIMESTAMP MySQL method is what generates the appropriate Drupal timestamp format.
Enjoy, everyone.
Plugin Joomla to Drupal
Hello,
I'm using Joomla in my Web Site www.i-biladi.com, and i want use Drupal, can i export my Joomla Plugins to Drupal ?
No
Totally different API. Sorry :(
You can do almost anything from joomla in Drupal - but while Joomla uses has a discrete module/plug-in for it, Druapl you often need a little more effort to tailor toe functions from the core platform,.
how do I export users to CSV?
I have a Joomla site I need to convert. I can only log into phpmyadmin and can't log into joomla at all [thus, reason why I need to switch--I've been wanting to switch anyway]. Is there a way to export to CSV through phpmyadmin? If not, is there a way to manually transfer users by adding them into the database one by one?
UPDATE: I got it to export to CSV.
Possible to manually edit "access"?
I transferred all my users over using the script, but then I noticed the second script, which takes care of the "access" thing. I can't get the second script to work, because now phpmyadmin is telling me I am not authorized to "INSERT". Even though I have done it before. Maybe I can pay someone to look at it for me?
There's a couple of issues here
The first is which I've now found more tables to edit - such as sequences - to make things fly.
Your first step should be to analyse the data by hand/eye, and then using phpmyadmin's tools try to insert a row from their interface (not by typed in or copy/pasted SQL). If this works, it will give you a syntax - compare the syntax against that in the query and see what's adrift. Mine seems especially twitchy with where spaces live.
If you still can't this to work then please post back here, or send me a message through my contact page and I'll try and find time to take a look at it for you. No guarantees though; if it's a permission error this is probably beyond what the code was designed to do and what I know how to do with it!
Gareth
Did this once before
I did this once before but now I can't figure it out again. I would like to just drop all the tables I transferred over from before and start over from scratch, with the second script this time. I'm still willing to pay someone to do this.
Don't drop the tables just
Don't drop the tables just empty them
delete * from tablename
then reset the auto increment row count for each table:
ALTER TABLE tablename AUTO_INCREMENT=0
You can create a mysql query for each table using the above simple code and empty your database completely
Mambo > Drupal 6.x
This also works for mambo (just tested).
I only need to find out where the "Member for" values are stored, at the moment the are active for: 38 years 17 weeks
That's cool of course, I don't think there is a website with this kind of loyal users :)
I''l update when I found it.
[EDIT:] Found it, it is the "Created" column in the Excell Sheet, in my case it is '1208881485' equals to: 2 days 2 hours
[UPDATE:]Remove: UNIX_TIMESTAMP('1208881485') just leave the number with quotes e.g.: '1208881485'
Or else the values will revert to '0' when inserted.
Kind Regards, Starf1x
PS, Cool Tool 'Drupal', takes a bit getting used to as a 'Migrating' Mambo Admin, but in the end it is more extensive then other CMS solutions, and far more easier to maintain.
PPS, Thank You Trumpall for this excellent way to migrate.
Hi, I am still confused on
Hi, I am still confused on converting my joomla passwords to Drupal. I am new to Drupal and don't really understand password hashes and all that so if anyone could explain it in dummy terms lol. My problem is that the joomla passwords are two long numbers and characters separated by a ":". The Drupal passwords are not. I only have about 500 people on the site right now so if there is a way to get the joomla password back to a form that can be converted to a Drupal password, I can do it manually in a short time. Thanks in advance :)
Patrick