Importing User Databases

13 years 8 months ago #8421 by bascherz
Yeah, I did the same thing. I went through everything there was to read here. I am on a different computer now, but someone posted a list of the tables that need to be updated for Joomla so that CB recognizes the users. From memory, I believe it was the following:
  • jos_users
  • jos_core_acl_aro
  • jos_core_acl_groups_aro_map
  • jos_comprofiler
I discovered that the backend CB user synch tool only does what it can, which is to copy the id and user_id fields from jos_users into jos_comprofiler. That's not the complete job, but it's all CB has enough information to do. It doesn't make any attempt to split the "Name" field in jos_users into "firstname", "lastname", and "middlename". But MS Access handles this nicely.

My user database is in MS Access, so I simply had MySQL export each of these tables as INSERT commands. I then created MS Access queries and reports to produce the exact syntax required to get my data into the above tables using the user/id fields in a JOIN query to combine jos_users and jos_comprofiler. I ran Access and created text sql files, copy/pasted them into the SQL window in MySQL, and voila! my database was populated. I even did the custom user fields.

It was a bit more involved than described above, but that's the basic gist of it. My CB database is populated and everything works perfectly.

I really love the community spirit these products foster. Half the fun of working on this stuff is to come here and share the success stories.

Cheers!
Bruce

______________________
Bruce S - Vienna, VA
13 years 8 months ago #8432 by ptkho
Great to hear you worked it out as well! It's clear there are more ways to import/export the database just by using phpMyAdmin. MS Acces through the INSERT-command is one way like Bascherz describe, I've done it through 'CSV-for MS Excel data' and opened it in a spreadsheet. The spreadsheet is either MS Excel or in OpenOffice.org. They are both fine, but OpenOffice.org has a more user friendly way to open the CSV-file ,while in MS Excel you have to figure out how to open it (tip: in Excel go to menu 'data' and search for 'text to columns' or something like that (I don't exactly know in English because I've only the Dutch version of the spreadsheets). Then follow the steps as described).

And I agree Bascherz, there's a bit more involved then what I described before, but these are the necessary basics to help those who want to import/export.
13 years 8 months ago #8542 by mikko
I have a rather large DB to import and decided to make JUICE to work with CB and one of my plugins (listtab) to avoid manual work.

The idea is to import CSV and let user assign each column a field where the data will be imported. The component will also allow users to create fields based on the first row (header) of the CSV file.

A screen shot of the component is attached.

Post edited by: mikko, at: 2006/03/20 10:32
Attachments:
13 years 8 months ago #9314 by bastiaan
Hey Mikko, great work and lot of thanks for making this component. Just one question, could you make a little better help file/instructions?

May be my question will help with that. (Usually when you make programs it all seems so obvious :-) )

There some option fields that could use a bit of explanation. THe checkboxes on the top e.g.

run CB plugins? I guess it does the secondary action somewhere like mailman syncs but...

diff between user and Tablists

Little explanation of the fields below?
For example when I choose a field in the first drop down box, do I still have to set the field type?

Is the field selected sa CB field or Joomla?

The difference between name and title? Guess field name and ???

What does the "identity" radio button do?
Seems I can select many.

More steps after import? Sync Joomla? or is it all done in one go? New users also created in Joomla or more steps needed?

Is there a way to add more fields later to already excisting users? My CVS was too big and too long, so I had it cut down to the important fields first.

Again great works, just have questions and it would be good to add the answers in the zip file.

Bastiaan
13 years 8 months ago #9315 by mikko
I will write documentation after the component is more finnished. I am curently quite busy with my work and do not know when this might be done.

Identity is meant for updating userDB. For example, if you choose username, then the for each row the present username is compared to existing usernames. If found, then the data is used to update that user rather than add a new one. With this you can insert data from several cvs files. If you do not have the fields configured before, the component should preonfigure new fields for you from the data.

ListTab meast that instead of importing the data as users it imporsts listtab entries. See the plugins at downloads for listtab.

Plugins means that the component will run whatever plugins you have that affect the user creation. For example mailman or yanc autosubscribe.

Some of this functionality is unfinnished and one of it is tested, so I recommend not to use it.

Name and title are obvious after you have created your first fields with CB backend. Name is a techical name and title is what is shown to users.

After you have impported the users there should not be anything else to do.

-Mikko
13 years 8 months ago #9316 by bastiaan
Wow glad you can answer so quick.

Well wish I could make a quick help file, will see if I have a little time left somewhere.

Seems your plugin does everything I wish for! Hope your answers will also help others.

Thanks again and keep up the good work!
Moderators: beatnantkrileon
Time to create page: 0.448 seconds
Facebook Twitter Google LinkedIn