Archive for April, 2008

Import .csv users file into Community Server 2007

Today I had to import an existing file of users into a new Community Server 2007 application.  At first thought I thought it would be simple to just use the import/export wizard in Management Studio.  However after looking into it further I realized it wasn’t quite that simple.  There are about 6 tables that are affected when adding a new user.

 What I ended up doing is writing a little import method to do this.  A couple things to prep CS2007 before I could execute the method:

  • Make sure all the roles that you are importing have been added
  • Make sure that the password requirements are going to be met by all the passwords in your csv.  I ran into an issue where some users didn’t have the default required minimum password length.  This is found in the web.config minRequiredPasswordLength in the Member section.
  • I placed the .csv file in the web root, here I knew aspnet would have rights to access the file.

After that I was ready to run the method.  Since I am not really familiar with CS2007 I just added the method in the ~/Themes/default/Common/Master.Master file.  I added the Page_Load () and called my import() method inside this.  I knew as soon as the page was loaded the method would be called here.  Originally I tried creating a small console app to do this, but it just wasn’t working.  It was missing a bunch of things from the web.config and CS dlls so I just figured why fight it. It’s a one time thing I’ll just throw it in the page and get it over with. Just Remember to remove all this after the users have been imported. 

The fields in the .csv file were:  FirstName,LastName,Username,Password,Email,Role,Territory,State

Now those fields don’t match up exactly in the CS database schema.  I ended up moving FirstName and LastName into commonName and Territory and State into location.  You can see all the properties that exist by looking into Profile section in the web.config.

One other thing I found out is that when creating a user you have set 4 properties.  username, password, email, and IsAnonymous = false. 

 

    void Page_Load()
    {
        import();
    }

 

    void import()
    {
        string fileLocation = @”C:\[path to your file goes here]\users.csv”;
        using (System.IO.StreamReader sr = new System.IO.StreamReader(fileLocation))
        {
            string line; // the current line being read from the file
            // File header: FirstName,LastName,Username,Password,Email,Role,Territory,State
            string userName;            //*********************************
            string location;               //* These are variables derived
            string commonName;     //* from my file. Obviously modify  
            string password;            //* to fit your needs.
            string email;                  //*
            string roleName;            //**********************************

            while ((line = sr.ReadLine()) != null)
            {
                string[] importFile = line.Split(new char[] { ‘,’ });

                commonName = importFile[0].ToString() + ” ” + importFile[1].ToString();
                userName = importFile[2].ToString();
                password = importFile[3].ToString();
                email = importFile[4].ToString();
                roleName = importFile[5].ToString();
                location = importFile[6].ToString() + “, ” + importFile[7].ToString();

                /* 1. Create the cs User and Profile
                 * 2. Add the aspnet Profile properties
                 * 3. Assign the aspnet User to it’s role(s).
                 */

                // 1.
                User csUser = new User();
                csUser.AccountStatus = UserAccountStatus.Approved;
                csUser.Username = userName;
                csUser.Password = password;
                csUser.Email = userName;
                csUser.IsAnonymous = false;
                
                CreateUserStatus createUserStatus;
                createUserStatus = CommunityServer.Users.Create(csUser, false);

                if (createUserStatus == CreateUserStatus.Created)
                {
                    // 2.
                    ProfileBase profileBase = ProfileBase.Create(userName, true);
                    profileBase.SetPropertyValue(“commonName”, commonName);
                    profileBase.SetPropertyValue(“location”, location);
                    profileBase.Save();

                    // 3.                        
                    System.Web.Security.Roles.AddUserToRole(userName, roleName);
                }
            }
        }
    }

 

Thanks to the following sites that help me create this:
http://mishler.net/2006/04/07/How+To+Import+Existing+AspNet+Membership+Data+Into+CommunityServer.aspx
http://www.igloocoder.com/archive/2007/08/22/1290.aspx
http://communityserver.org/forums/t/485629.aspx
http://communityserver.org/forums/p/490538/592779.aspx#592779