Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • npanguuluri 30 posts 57 karma points
    Apr 05, 2010 @ 17:36
    npanguuluri
    0

    Export Member Data

    Hi all,

    Is there a way to export member data to a csv file from Umbraco Admin side.. i can export by writing usercontrols.. but it would be nice if there is a tool to export member data from the admin side

     

    Thanks

    Naren

  • Lee Kelleher 3987 posts 15554 karma points MVP 11x admin c-trib
    Apr 05, 2010 @ 18:31
    Lee Kelleher
    0

    Hi Naren,

    Unfortunately, there is no way to export member data from the Umbraco admin back-office.  If you already have a way of doing it via a user-control, then that's currently the best way of doing it.

    You could always package up your user-control and release it here? Might help out other users in future? (just an idea)

    Cheers, Lee.

  • Julius Bartkus 51 posts 76 karma points
    Feb 01, 2011 @ 21:01
    Julius Bartkus
    1

    Hey,

    I wrote some kind of simple member export/import script using /base, therefore wanted to share.. However, it is a bit slow as it is using exporting to xml file and then loading back using umbraco API, so if anyone has any ideas how the script could be improved, you are more then welcome to share :)

     [RestExtensionMethod]
        public static void exportMembers()
        {
            String path = HttpContext.Current.Server.MapPath("~/App_Data/xml/members.xml");  //Upload a file somewhere to reachable place    
            TextWriter tw = new StreamWriter(path);
    
            tw.WriteLine("<members>");
    
            foreach (Member m in Member.GetAllAsList())
            {
                tw.WriteLine("<member>");
                tw.WriteLine("<nodeid><![CDATA[" + m.Id + "]]></nodeid>");
                tw.WriteLine("<name><![CDATA[" + m.Text + "]]></name>");
                tw.WriteLine("<username><![CDATA[" + m.LoginName + "]]></username>");
                tw.WriteLine("<password><![CDATA[" + m.Password + "]]></password>");
                tw.WriteLine("<email><![CDATA[" + m.Email + "]]></email>");
                tw.WriteLine("<groups>");            
    
                foreach (String group in Roles.GetRolesForUser(m.LoginName))            
                    tw.WriteLine("<group><![CDATA[" + group + "]]></group>");            
    
                tw.WriteLine("</groups>");
                tw.WriteLine("<properties>");
    
                foreach (Property p in m.GenericProperties)
                {
                    if (!String.IsNullOrEmpty(p.Value.ToString()))
                    {
                        Type t = p.PropertyType.GetType();
                        tw.WriteLine("<" + p.PropertyType.Alias + " type=\"" + p.Value.GetType().ToString() + "\">");
                        tw.WriteLine("<![CDATA[" + p.Value.ToString() + "]]>");
                        tw.WriteLine("</" + p.PropertyType.Alias + ">");
                    }
                }
    
                tw.WriteLine("</properties>");
                tw.WriteLine("</member>");
            }
    
            tw.WriteLine("</members>");
    
            tw.Close();
    
        }
    
        [RestExtensionMethod]
        public static String importMembers()
        {
            String output = "";
            XmlDocument doc = new XmlDocument();
    
            String path = HttpContext.Current.Server.MapPath("App_Data/xml/members.xml");  //Upload a file somewhere to reachable place
    
            FileStream stream = new FileStream(path, FileMode.Open);
            XmlReader r = XmlReader.Create(stream);
            doc.Load(r);
            XmlNode root = doc.SelectSingleNode("/members");
    
            if (root.HasChildNodes)
                foreach (XmlNode memberNode in root.ChildNodes)
                   output += importMember(memberNode);
    
            return output;
        }
        private static String importMember(XmlNode mn)
        {
    
            String output = "";
    
            int nodeId = Int32.Parse(mn.SelectSingleNode("./nodeid").InnerText);
            String name = mn.SelectSingleNode("./name").InnerText;
            String username = mn.SelectSingleNode("./username").InnerText;
            String password = mn.SelectSingleNode("./password").InnerText;
            String email = mn.SelectSingleNode("./email").InnerText.Trim();
    
            if ((email != "" && String.IsNullOrEmpty(Membership.GetUserNameByEmail(email))) ||  Membership.GetUser(username) == null)
            {
    
                MemberType memberType = MemberType.GetByAlias("Basic");
                Member m = Member.MakeNew(name, username, email, memberType, new umbraco.BusinessLogic.User(0));
    
                XmlNode groupsNode = mn.SelectSingleNode("./groups");
                if (groupsNode.HasChildNodes)
                    foreach (XmlNode groupNode in groupsNode.ChildNodes)
                        Roles.AddUserToRole(username, groupNode.InnerText);
    
                XmlNode propertiesNode = mn.SelectSingleNode("./properties");
                if (propertiesNode.HasChildNodes)
                {
                    foreach (XmlNode propertyNode in propertiesNode.ChildNodes)
                    {
                        Type propertyType = Type.GetType(propertyNode.Attributes["type"].Value);
                        MemberHelper.setProperty(m, propertyNode.Name, Convert.ChangeType(propertyNode.InnerText, propertyType));
                    }
                }
    
                //srvc.setHashedPassword(m.Id, password); //update password in cmsmeber table            
            }
            else
            {
                output += "bad email " + email + " " + username + " ";
            }
            return output;
        }


    Cheers,
    Julius Bartkus

  • Daniel Lindstrom 454 posts 271 karma points
    Mar 10, 2011 @ 13:07
    Daniel Lindstrom
    0

    Julius, I'm trying to use your code in a umbraco 4.6.2 installation. However I cannot find the MemberHelper used by your code class? 

     

    Any ideas?

  • Julius Bartkus 51 posts 76 karma points
    Mar 10, 2011 @ 13:10
    Julius Bartkus
    0

    Hey,

    sorry, forgot to put it on:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using umbraco.cms.businesslogic.member;
    
    public class MemberHelper
    {
        public static T getProperty<T>(Member m, String alias, T defaultValue)
        {
            if (m.getProperty(alias).Value.ToString() == String.Empty)
                return defaultValue;
            else
                return (T)Convert.ChangeType(m.getProperty(alias).Value, typeof(T));
        }
    
        public static void setProperty<T>(Member m, String alias, T value)
        {
            m.getProperty(alias).Value = value;
        }   
    }
    
  • Daniel Lindstrom 454 posts 271 karma points
    Mar 10, 2011 @ 13:25
    Daniel Lindstrom
    0

    Thanks for a very fast and helpful answer!

    Now the site compiles, but when I try to access this I get an error:

    p.p1 {margin: 5.0px 0.0px 5.0px 0.0px; font: 12.0px 'Times New Roman'}

    Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

     

    I have never used base before, so I am clueless...

    Any ideas? Or any other suggestions on how to transfer members from 4.0 to 4.6.2?

  • Daniel Lindstrom 454 posts 271 karma points
    Mar 10, 2011 @ 13:45
    Daniel Lindstrom
    0

    Well, I just used your class from a template instead. Seems to work, haven't tried importing yet though.

    Thanks a lot for sharing your code! :)

  • Daniel Lindstrom 454 posts 271 karma points
    Mar 10, 2011 @ 13:59
    Daniel Lindstrom
    0

    Well, strangely enough now I get an error 

    umbraco.cms.businesslogic.member.Member' does not contain a definition for 'GetAllAsList'

     

    But it does, doesn't it?

     

  • Daniel Lindstrom 454 posts 271 karma points
    Mar 10, 2011 @ 14:02
    Daniel Lindstrom
    0

    No, not in 4.1....

     

  • Jeroen Breuer 4866 posts 12165 karma points MVP 3x admin c-trib
    Mar 10, 2011 @ 14:18
    Jeroen Breuer
    0

    Why not use MemberExport?

    Jeroen

  • Daniel Lindstrom 454 posts 271 karma points
    Mar 10, 2011 @ 14:28
    Daniel Lindstrom
    0

    Well, searched this site but did not find that package. 

    Have used it now, worked great!

    Now I guess I'll have to tweak Julius code above to do the import from that file.

    Thanks! :) 

  • Julius Bartkus 51 posts 76 karma points
    Mar 10, 2011 @ 14:39
    Julius Bartkus
    0

    2 Jeroen Breuer: 

    MemberExport is not free.

  • Jeroen Breuer 4866 posts 12165 karma points MVP 3x admin c-trib
    Mar 10, 2011 @ 15:09
    Jeroen Breuer
    0

    If you want to import that file you can use CMSImport :).

    Jeroen

  • Richard Soeteman 3968 posts 12470 karma points MVP
    Mar 10, 2011 @ 17:14
    Richard Soeteman
    0

    @Julius MemberExport is free up to the export of 200 members.  

    @Daniel indeed you can use CMSImport to import the members again. Also free up to 500 records.

    Cheers,

    Richard

  • Julius Bartkus 51 posts 76 karma points
    Mar 10, 2011 @ 22:55
    Julius Bartkus
    0

    Well I had 10.000 members in my system and used the script I shared to move members from one system to another.
    The work took like 10 minutes and didn't cost anything :-)

  • Tom 713 posts 954 karma points
    Jul 28, 2011 @ 02:14
    Tom
    0

    Hi Julius sorry not familiar with restextensionmethods.. how would i make use of your code in umbraco? or would i just create a user control macro and call your code?

     

    Cheers,

    Tom

  • CH 5 posts 70 karma points
    Nov 18, 2011 @ 12:31
    CH
    0

    I needed to export all members on a daily basis to Excel, and have tried out several solutions

    I wouldn't recommend using Member.GetAll and other API methods since these seems to rely heavily on multiple queries
    do some SQL profiling on an page with several controls retrieving member properties and you'll be impressed by the number of queries on the database 

    so as soon as you reach thousands of members, and you need say +10 properties in your export, exporting with the API is really not a good idea

    altough direct sql queries could potentially be broken by future upgrades of the CMS, they still are the best solution in terms of performance

    like for the API, a query with subqueries will soon hit its limits:

    select (select ...) as propertyname from ...

    the MemberExport package uses an inner join solution to fetch all the properties

    http://our.umbraco.org/projects/developer-tools/cmsimport/using-cmsimport/12443-Exporting-members

    I'm not sure if this is less suitable in terms of performance, but i implemented a solution with a pivot tabel (which requires at least SQL 2005 I believe) allowing me to swap the property rows into columns; pivot is meant to be used for aggregation, but with the max it can be tricked into normal row swapping

    based on my experience it is fast (2 secs on a +13K member base with +10 properties)

    you can implement it as a stored procedure with a temp table (as shown here) or in two parts with an indexed view (use the schemabinding clause and then create indexes) + a text query, the latter offers you more flexibility 
    an indexed view of course causes slight overhead, because the server needs to update its indexes; however, I haven't come across speed differences so far (after the initial execution & compilation)

    this example is just for two properties and works with integer, boolean and varchar fields 

     

    create procedure sp_GetMemberProperties as begin set nocount on; create table #MemberData ( Id int, Alias nvarchar(255) NOT NULL, DataValue nvarchar(500) NULL ) insert into #MemberData select dbo.cmsPropertyData.contentNodeId, dbo.cmsPropertyType.Alias, IsNull(dbo.cmsPropertyData.dataNvarchar, dbo.cmsPropertyData.dataInt) from dbo.cmsPropertyData inner join dbo.cmsPropertyType on dbo.cmsPropertyType.id = dbo.cmsPropertyData.propertytypeid inner join dbo.cmsMember on dbo.cmsMember.nodeId = dbo.cmsPropertyData.contentNodeId select Id as MemberId, firstName as FirstName, name as Name from #MemberData pivot (max(datavalue) for Alias in (firstName, name)) p order by Id asc drop table #MemberData end go

     

     

  • Richard Soeteman 3968 posts 12470 karma points MVP
    Nov 18, 2011 @ 12:44
    Richard Soeteman
    0

    Might be nice to know that I'm currently working on native export to excel for MemberExport PRO. And indeed using direct SQL in Memberexport. Reason for this is that it's really fast, depending on the amount of properties it exports thousands of members in milliseconds. I'm not that afraid that the DB schema will change since there will not be new functionality in Umbraco V4.x. And it's a commercial package so when something breaks on a supported version expect a new release within a few days.

    Cheers,

    Richard

     

  • Barry Fogarty 493 posts 1129 karma points
    Nov 18, 2011 @ 12:51
    Barry Fogarty
    0

    Great news, hopefully this will address the issue of Excel dropping leading zeros on phone numbers (not a fault of the package).

  • Richard Soeteman 3968 posts 12470 karma points MVP
    Nov 18, 2011 @ 14:45
    Richard Soeteman
    0

    Yes it will, in fact that triggered me to update the package :). Hopefully done by next week, I will ping you for a test.

    Thanks,

    Richard

Please Sign in or register to post replies

Write your reply to:

Draft