Mass CSV import problems

I’ve got a paid copy of s2member pro, and I’m really, really struggling with mass importation of users via the Import/Export functions.

I’ve read the page s2Member® | Import (or Mass Update) Users in some detail, and have spent the last 5 hours trying to import a CSV exported from an Excel spreadsheet without success. I’m using custom fields because the s2member database will become the main member database (it’s currently in Zoho which is dire). I’ve looked at the CSV exported from s2member, and have tried to edit my spreadsheet accordingly. I’ve copied the CSVs below as I can’t attach them to this post.

When I try to import a CSV with custom fields the same name as the fields defined in s2member, all that comes over is the user’s name and email – all the custom fields are blank. So, do the custom fields need to be specified in a particular order? Would it be simpler to write and run a SQL UPDATE query, and if so what form should it be? What is going wrong?

I’ve spent at least 50 hours on s2member already, a long time for a plugin which I’d expected to save me time, and in which time I could have designed and implemented my own membership database. Because I’ve spent so much time on it, which would be lost forever if I ditched s2member, and because I’ve coughed up, I’ll have to fight to get the thing working as it should. I can see that it’s a great plugin if you’re starting a site from scratch, but my organisation (www.eurocall-languages.org) has some 300 paid-up members already and I really, really need to import their details into s2member else I’ll have to manually create all these records one-by-one, which thought makes me want to jump off a building.

TIA for any advice/sympathy/solidarity.

Fred

CSVs
——
Exported from s2member:
“ID”,”Username”,”Password”,”First Name”,”Last Name”,”Display Name”,”Email”,”Website”,”Role”,”Custom Capabilities”,”Registration Date”,”First Payment Date”,”Last Payment Date”,”Auto-EOT Date”,”Custom Value”,”Paid Subscr. ID”,”Paid Subscr. Gateway”,”address_1″,”address_2″,”city”,”country”,”county”,”eurocall_id”,”id_type”,”postcode”
“1”,”admin”,””,”Fred”,”Riley”,”admin”,”fred.riley@gmail.com”,”http://www.fredriley.org.uk/”,”administrator”,””,”03/24/2013″,”a:2:{s:5:””level””;i:1364154108;s:6:””level4″”;i:1364154108;}”,””,””,””,””,”paypal”,”12 My Road”,”My district”,”My city”,”UK”,”My county”,”1000″,”IND”,”ab1 2cd”

Test CSV to import to s2member:
“ID”,”Username”,”Password”,”First Name”,”Last Name”,”Display name”,”Email”,”eurocall_id”,”id_type”,”country”,”address_1″,”address_2″,”city”,”country”,”postcode”
,”jack.jones@nowhere.com”,,”Jack”,”Jones”,”Jack Jones”,”jack.jones@nowhere.com”,1004,”IND”,”France”,”1 Some stree”,”Some district”,”Some city”,”Neverwhere”,”bc2 3de”

Hi Fred.

do the custom fields need to be specified in a particular order?

Yes, this is important. See this section of the article: http://www.s2member.com/kb/importing-updating-users/#custom-registration-profile-fields

Use the exported CSV with the columns as they are, without removing or rearranging them.

When you import new users, leave empty the ID field, then a new user will be created. If you enter a User ID, it’ll look for that ID to update an existing user.

I hope that helps. :)

Ok, thanks for the reply – I’ll give it a go when I get back to work on Friday and post back here. Could you please point me towards the script that generates the SQL query as that would help me understand s2member’s requirements?

Cheers

Fred

These?

s2member-pro/includes/classes/exports-in.inc.php
s2member-pro/includes/classes/imports-in.inc.php

Thanks for the replies, Cristián. After a few days break in the hills, I’ve come back to this problem and it’s still a problem. I decided to go ‘back to basics’ and simply export a CSV from s2member and try to reimport it, following the tip on the Import (or Mass Update) Users page:

Do not fear. If you have any trouble, please perform an export first using s2Member® Pro. s2Member’s export files are already formatted for easy re-importation. In other words, you can use them as a guideline for building your own import files.

I created a test user ‘harryhamster’ using s2member => Add a Member, to add to the existing admin user. So the exported CSV looks like:

ID;Username;Password;First Name;Last Name;Display Name;Email;Website;Role;Custom Capabilities;Registration Date;First Payment Date;Last Payment Date;Auto-EOT Date;Custom Value;Paid Subscr. ID;Paid Subscr. Gateway;address_1;address_2;city;country;county;eurocall_id;id_type;postcode
1;admin;;Fred;Riley;admin;fred.riley@gmail.com;http://www.fredriley.org.uk/;administrator;;03/24/2013;"a:2:{s:5:""level"";i:1364154108;s:6:""level4"";i:1364154108;}";;;;;paypal;12 My Road;My district;My city;UK;My county;1000;IND;ab1 2cd
231;harryhamster;;Harry;Hamster;Harry Hamster;harry.hamster@rodents.com;http://www.harryhamster.org;subscriber;;03/31/2013;;;;;;;1 Rodent Road;Hamstertown;Rodentsville;Albania;Notts;1001;IND;AB1 2CD

I then try to reimport this file, unedited, and get the results:

Line #1. User ID# ID;Username;Password;First Name;Last Name;Display Name;Email;Website;Role;Custom Capabilities;Registration Date;First Payment Date;Last Payment Date;Auto-EOT Date;Custom Value;Paid Subscr. ID;Paid Subscr. Gateway;address_1;address_2;city;country;county;eurocall_id;id_type;postcode does NOT belong to an existing User.
Line #2. User ID# 1;admin;;Fred;Riley;admin;fred.riley@gmail.com;http://www.fredriley.org.uk/;administrator;;03/24/2013;"a:2:{s:5:""level"";i:1364154108;s:6:""level4"";i:1364154108;}";;;;;paypal;12 My Road;My district;My city;UK;My county;1000;IND;ab1 2cd does NOT belong to an existing User.
Line #3. User ID# 231;harryhamster;;Harry;Hamster;Harry Hamster;harry.hamster@rodents.com;http://www.harryhamster.org;subscriber;;03/31/2013;;;;;;;1 Rodent Road;Hamstertown;Rodentsville;Albania;Notts;1001;IND;AB1 2CD does NOT belong to an existing User.

When exporting, I chose the default CSV file preference for “easy re-importation”. Could you please advise as to why s2member is throwing these errors? This is now 6 hours on this particular problem on a test localhost server, so if it’s not possible to import CSV then I’ll cut my losses and enter all 300 or so users manually on the live online server.

Thanks for the pointers to the scripts, which I’ll try to look through carefully. If you get the time, could you please give me SQL examples of the UPDATE and INSERT queries that the import script generates? If not, I’ll throw some ‘echo’ statements in the code to see what comes out.

Fred

I see in your CSV that the values are not properly quoted and the separators not commas. What are you opening/saving it with? I use OpenOffice Calc.

Try updating the user with this:

231,"harryhamster",,"Harry","Hamster","Harry Hamster","harry.hamster@rodents.com","http://www.harryhamster.org","subscriber",,"03/31/2013",,,,,,,"1 Rodent Road","Hamstertown","Rodentsville","Albania","Notts",1001,"IND","AB1 2CD"

Remember that if the first value has a user ID, s2Member will expect the user account to exist to update it. If you want to create a new user, leave the user ID field empty.

D’OH!! Of course that’s the problem, and I should have seen it – sorry. I’m using Libre Office Calc and the default CSV export uses ; for delimiters and doesn’t quote values. When I changed the export options to use commas as delimiters and quote all values, the CSV imported ok into s2member.

So, what I need to remember is:

• use a correctly-formatted CSV
• put the s2member fields (username, email etc) and the custom fields in the correct order in the spreadsheet

Thanks for the help, which has got me there in the end, I hope :)

Fred

As a follow-up query, our organisation’s current members will need to be imported into s2member, with their organisation ID, membership end date, and various custom fields.

1. Is there a way to import new users and specify the ID number? For instance:

1234,"harryhamster",,"Harry","Hamster","Harry Hamster","harry.hamster@rodents.com","http://www.rodents.com/hamster","subscriber",,"04/01/2013",,,,,,,"1000 Rodent Road","Rodentsville","Rodent City","Elbonia",,10002,"IND","bc2 3de"

where the first value 1234 is the organisational membership ID. I think that the answer to this is no, going by the s2member import documentation, in that either you have to have an automatically-generated user ID in WordPress, or an existing record with that user ID. Which would be a minor pain as then we’d have two IDs per user – one for the WP database, one their organisational ID – so I might have to manually edit the ID numbers in WP.

2. When I import these users, WP will set the registration date to the date of import, but of course each will have paid their annual fee before this time. I will need to store the membership expiry dates such that the user/member will be prompted to renew a month before expiry. Do I need to set the “Automatic EOT Time:” for this, and if so how can I set up automatic renewal prompts with links to Paypal?

3. Are there examples in the Codex or Forum of other web administrators who’ve had to import existing members and how they’ve gone about it?

I hope the above makes sense, and doesn’t tax your patience. If I were starting up a membership site from scratch then there’d not be these complications, but I need to ensure a trouble-free transition for our members to the new membership site and that could be tricky.

Cheers

Fred

I’m glad the formatting was all it took to make it work. Thanks for the update. :)

1. Is there a way to import new users and specify the ID number? For instance:

No, the WP ID is generated automatically, you can’t assign it when creating the user with the Import tool. For the member ID of your organization, I think it’d be best to have a custom profile field independent of the WP user management. [hilite path]Dashboard -› s2Member® -› General Options -› Registration/Profile Fields[/hilite]

Of course you could go and edit the database to change the user IDs to match the member ID, but I think it’d be best to keep them separate.

2. When I import these users, WP will set the registration date to the date of import, but of course each will have paid their annual fee before this time. I will need to store the membership expiry dates such that the user/member will be prompted to renew a month before expiry. Do I need to set the “Automatic EOT Time:” for this, and if so how can I set up automatic renewal prompts with links to Paypal?

There’s a column for the registartion date in the CSV, so you can set it there. The EOT time can be set too, but then it’ll ignore any subscription notifications from the payment gateway, if you have those. If you tie the profile to a gateway subscription, it’ll be best to leave the EOT time empty so it’s set when the gateway says the subscription ended.

3. Are there examples in the Codex or Forum of other web administrators who’ve had to import existing members and how they’ve gone about it?

This article: Knowledge Base » How to Migrate to s2Member Pro from Other Software

Posts by site owners about this may be in the forums, but I don’t remember any particular one that gives a step by step beyond what we’ve covered here already. If you have more questions, I’ll be happy to answer them as I can.