Import User List from Excel File into .Net Membership Provider

So you have an existing ASP.NET Membership Provider and now your just got handed a Excel file full of username’s and passwords. Today I was faced with the task of writing a simple import script to take users from a Excel file and bring them into the membership database.

The code example below assumes you have a Microsoft Excel file (.xls) with a column called “user” and another column called “password”.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Web.Security;
using System.Web.Profile;

public partial class admin_admin_Import : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\users.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;

            using (DbCommand command = connection.CreateCommand())
            {
                // Users$ comes from the name of the worksheet
                command.CommandText = "SELECT user,password FROM [Users$]";

                connection.Open();

                using (DbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {



                        if (Membership.GetUser(dr["user"].ToString()) == null)
                        {

                            Membership.CreateUser(dr["user"].ToString(), dr["password"].ToString());

                        }
                       
                    } 

                }
            }
        }
    }

}