• twitter image
  • facebook image
  • youtube image
  • linkedin image
Language: CMS Made Simple Czech CMS Made Simple France CMS Made Simple Spain CMS Made Simple Hungary CMS Made Simple Russia CMS Made Simple Netherlands

All times are UTC




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: [Tip] FEU - importing contacts
PostPosted: Thu Aug 06, 2009 12:31 pm 
Offline
Forum Members
Forum Members
User avatar

Joined: Tue Nov 06, 2007 9:04 am
Posts: 239
Location: Brighton, UK
I've been doing some work recently with Calguy's FrontEndUsers module. Having now spent some time with it I thought I'd share some tips about importing data.

***Download my spreadsheet working example here***

We didn't have much luck using the built-in importer mechanism, so we opted to import directly to MySQL using phpMyAdmin.

These are the tables concerned

* cms_module_feusers_users : this holds users and passwords

* cms_module_feusers_belongs : this holds the id of a user + the id of the group they belong to

* cms_module_feusers_properties : this holds the id of each property, the id of the associated user, the name of database field, and its value

Here's what we did

1. Exported member data from the legacy system's database into an Excel worksheet (Master). Next, we created new worksheets for the 3 tables: Users, Belongs, and Properties. These worksheets referenced the appropriate columns from the Master worksheet. Get my spreadsheet example here.

2. Back in FEU, we created all our Properties fields and also a Group. Note: 1) You can find the Group's id on the Group tab of FEU, pop this in your Belongs worksheet; 2) In phpMyAdmin, look at the User id field and make sure your import users follow the sequence. I'd created a test user, so our user ids started at 2 not 1.

3. We then saved off the worksheets for Users, Belongs, and Properties as individual .csv files. Note: You should not have column headings in any of your .csv files, just the rows of data in the order they appear in FEU's tables. Take a look at my attached spreadsheet to get an idea of the formatting.

4. Next, we imported the 3 .csv files.

Our first attempt looked to be successful, however, when logging in as a Front End User and saving changes random errors were thrown (e.g. "field member_category could not be updated"). Additionally, other fields were blanked by the update. We tracked this problem down to our usernames containing spaces; removing these spaces and doing a clean import fixed it.

Using and modifying my spreadsheet for your import

Adding more columns (Properties) is easy:

1. In the Master sheet, add the column heading and your data underneath it. Easy.

2. In the Properties worksheet you should only have 1 user (delete the data if you have more); this will form the template for other users pulled from the Master sheet. Now add a row for your new Property column.

3. In the Properties sheet, column A is the unique id of a property and should following the sequence. Get this to auto-increment by setting A2's value to be A1+1 and copy/pasting it down for each Property for your user.

4. Column B should reference the user id from the Mastersheet  (=Master!A2 in my case). Column C, the database field name, should statically reference the column heading in the Mastersheet (use something like =Master!$D$1). The $ symbols make sure Excel doesn't increment down to the next row. Row D should reference the actual data under your column in the Master sheet (e.g. =Master!D2).

5. Once you have it in place for one user, copy/paste all the fields with a drag select of lots of rows. You need to select as many rows as you have data for. The number of columns is the number of users multiplied by the number of properties. I just selected a few thousand rows, pasted, and then trimmed the ones that had blank data.

I hope this is helpful to someone. It would have saved me a fair few hours, I know that much. :)

_________________
Ocean Theme exclusively for CMS Made Simple - jQuery, cufón, 3x page templates, and more!

View the demo site | Download the Ocean Theme

Latest site: Sentori email marketing


Top
 Profile  
 
 Post subject: Re: [Tip] FEU - importing contacts
PostPosted: Tue Nov 03, 2009 10:35 am 
Offline
Forum Members
Forum Members
User avatar

Joined: Tue May 02, 2006 1:46 pm
Posts: 26
Location: Apeldoorn
Hi Deke,

Do you still have that Excel example sheet for download somewhere. The posted url seems dead.


Top
 Profile  
 
 Post subject: Re: [Tip] FEU - importing contacts
PostPosted: Tue Sep 07, 2010 4:45 pm 
Offline
Forum Members
Forum Members

Joined: Thu Jul 02, 2009 3:51 pm
Posts: 41
Location: Cambridge UK
Seconded!  I too have had no luck with FEU's built-in import scheme, which doesn't seem to cope with importing blank fields.  I think I've got my head round Deak's method, but an example spreadsheet would be very helpful, if it's still possible.

TIA.


Top
 Profile  
 
 Post subject: Re: [Tip] FEU - importing contacts
PostPosted: Tue Sep 07, 2010 6:12 pm 
Offline
Forum Members
Forum Members
User avatar

Joined: Tue Nov 06, 2007 9:04 am
Posts: 239
Location: Brighton, UK
I'll take a look for the spreadsheet tomorrow and re-upload it.

_________________
Ocean Theme exclusively for CMS Made Simple - jQuery, cufón, 3x page templates, and more!

View the demo site | Download the Ocean Theme

Latest site: Sentori email marketing


Top
 Profile  
 
 Post subject: Re: [Tip] FEU - importing contacts
PostPosted: Tue Sep 07, 2010 7:51 pm 
Offline
Forum Members
Forum Members

Joined: Thu Jul 02, 2009 3:51 pm
Posts: 41
Location: Cambridge UK
that's kind of you - thanks.


Top
 Profile  
 
 Post subject: Re: [Tip] FEU - importing contacts
PostPosted: Thu Sep 09, 2010 1:26 pm 
Offline
Forum Members
Forum Members
User avatar

Joined: Tue Nov 06, 2007 9:04 am
Posts: 239
Location: Brighton, UK
Hey, I've re-uploaded the spreadsheet. The above links should now work again.

_________________
Ocean Theme exclusively for CMS Made Simple - jQuery, cufón, 3x page templates, and more!

View the demo site | Download the Ocean Theme

Latest site: Sentori email marketing


Top
 Profile  
 
 Post subject: Re: [Tip] FEU - importing contacts
PostPosted: Thu Sep 09, 2010 1:49 pm 
Offline
Forum Members
Forum Members

Joined: Thu Jul 02, 2009 3:51 pm
Posts: 41
Location: Cambridge UK
that's great - much appreciated!


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC


Who is online

Users browsing this forum: pgj


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Arvixe - A CMSMS Partner