Thread

I have an Excel spreadsheet of my site members that I’d like to import. Mys issue is that I’ve also added a custom profile field for home phone number.

Is there any way to import the list (once I’ve converted my .csv into the correct XML format) and include the phone number data?

Welcome to the ExpressionEngine community, deedubya.

That’s an interesting question, and one I don’t have the answer to.

I’ll see what I can find out.

deedubya - that sort of functionality isn’t built into the existing member import.

I’ve done something similar with custom coding.

Thanks for checking into for me Sue. I appreciate it. I guess I’m just surprised that telephone number wouldn’t be part of the “standard” member info set.

Hi deedubya,

I’ve been having similar issues to you with client supplied member data that needs to be imported to EE and have trawled the forums looking for a solution.

I discovered that EE handles Member information in several different tables in its database. If you’ve installed EE successfully and are comfortable using phpAdmin then this run through may be of interest.

The member tables of interst are:

exp_member - main member info held here (including “member_id”, “username”, “screen_name”, “password”, “email” etc.)
ex_member_data - member custom profile fields held here (including member_id, m_field_id_1, m_field_id_2 etc.)

My solution was to use manipulate the client supplied excel file into the files that i needed to in order to use the member import utility to get the main member info into the system.

The fields I used for to create the xml file were “username”, “screen_name”, “password”, “email”. EE successfully imported 700+ of these in one go.

I then set up my custom profile fields which were “address_1” to “address_4”, “region” and “phone number” referred to as “m_field_id_1” to “m_field_id_6” by EE in exp_member_data.

Then I looked at exp_member_data using phpmyadmin and exported a csv of this table. Looking at this I was able to get the EE assigned member_id field for each new member. I used this to create a csv of my custom profile fields for each of the 700+ new members which I used to create a sql insert query.

The next step was to use phymyadmin and a DELETE sql query to remove those 700+ new members. I then used a SQL INSERT query to insert the custom profile fields for each new member. The query looked like:

INSERT INTO exp_member_data (member_id, m_field_id_1, m_field_id_2, m_field_id_3, m_field_id_4, m_field_id_5, m_field_id_6)
VALUES
('1001','Ithaca, Golden Hill','Manor Tilbride','Blessington','','Co Wicklow',''),
...
('1703','ABC PLANNING','4th Floor, Brown Cow Lane','71/72 street St North','Wheatfield','Dublin 7','');

So all in all it was a roundabout way to import Custom Member Profile Fields but it worked.

My most important bit of advice if you decide to try this is to do it on a fresh clean development install of EE rather than risking a FUBAR situation on a live site.

Hope this helps.

@Sue BTW How did you appraoch this? Is there an easier way?

Odran

Thanks for showing this route, works well for me too.

Only difference for me was to first ‘export’ the exp_member_data table, paste in the new Excel data to match same format e.g. (‘12’,‘xxxxx’,‘xxxxx’,‘xxxxx’) my Custom Profile Fields set up previously, I then chose to ‘drop’ the table before I would ‘import’ the table back in again.

If the Member Import Utility could import custom profile fields it could be used for web editors to update from offline databases. You can vote for a feature request here
http://ellislab.com/forums/viewthread/79299/

Thanks for sharing your solution with us. Rather than closing this thread I’ll move it to Howto to allow for additional discussion.