Thread: import excel file into asp_net membership

Good morning everyone it's peeb, hope everyone is well. I"m currently working on a login for a client. They initially will have about 1,000 users that will need to be created to login into the asp.net (sql) system.

Instead of the system admin sitting and creating all 1,000 users, we thought of using an excel file and importing the users into the system.

Now I have never coded something like that, but i'm researching things online and there are a few good articles.

My question is can you import excel file into the asp_net membership tables, because the passwords are encrypted, etc. So my thoughts are you may not be able to import into those tables unless you build a customized login?

Can this be done? If so does anyone have any good articles that will help in coding to import excel file into sql, again i've never coded something like that?

As alway everyone, I appreciate any and all help.

thanks.

Hey thanks wolffy, I thought you couldn't import into the asp.net membership tables, but I wasn't for sure.

Sorry dude, i'm a little confused on your idea. Now you're saying I should look at the membeship Stored proc's and have it import the excel data as entries into the database. Create and application that does this, right?

Originally Posted by Wolffy

My initial guess is that you will not be able to import directly into the tables from Excel, as you pointed out the passwords are encrypted, and you have things like application id to worry about. Rather, it should be possible to create an application (even a console app, tho I'm think at about 100,000 feet at the moment) that could take you Excel SS (with Passwords in plain text? Oh the Horror) and using the Membership class methods to create the entries in the the DB. (Consider exporting the SS as a flat file -- just way easier).

Just another half baked idea -- look at the stored procs used by the membership db (if any) and see if you can use them along with the flat file idea.

Okay thanks to you both Rich and Wolf, I'll try that.

I'll let you know how it turns out.

Keep an eye out for me, because I know I will post again on this.

Thanks.

Originally Posted by Wolffy

Take a look back at RR post right before yours. If you combine his idea with mine, you should have a workable(?) solution. Where he has the comment 'save into membership table is where you would call the necessary sprocs to actually do the inserts.

And thanks to RR -- I needed something like that just today to import Excel data into a database.

Okay everyone, this is what I got and I Knew this would be a little bit of a problem. I'm using the aspnet_membership_creatUser stored procedure to create a new user from an excel file.

The excel file upload works and it reads the excel file (THANKS TO RR & wolffy) but i'm having problem insert it into the DB.

I get and error:

Procedure 'aspnet_Membership_CreateUser' expects parameter '@PasswordSalt', which was not supplied.Problem

In the procedure is does ask for @PasswordSalt, but I have no idea what it is.

In the table the @PasswardSalt is encrypted.

Any ideas on what values do I need to insert into the table?

Code:

Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.IO
Imports System.Text
Imports System.Web.mail
Imports System.IO.stream
Imports System.Data
Imports System.Data.OleDb
Imports System.Net.Mail
Imports System.Text.regularexpressions
Imports System
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.UserControl
Imports System.Web.UI.WebControls
Imports System.Web.Management
Imports System.Diagnostics.Process
Partial Class exceluserimport
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim doctxt As String = Right(FileUpload1.FileName, 3).ToString 'upload text file in upper and lower case "txt"
        Dim fileName As String = Server.MapPath("uploads/testexcelmembership.xls")
        If doctxt.ToLower = "xls" Or doctxt.ToLower = Nothing Or doctxt = "" Then
            If FileUpload1.HasFile And doctxt.ToLower = "xls" Then
                Try
                    File.Delete(Server.MapPath("uploads/testexcelmembership.xls"))
                    FileUpload1.SaveAs(Server.MapPath("uploads\" & ID + FileUpload1.FileName))
                Catch ex As Exception
                    Response.Write("file not uploaded" & ex.Message)
                End Try
            End If
        Else
            Label2.Visible = True
        End If

        Dim app As String = ""
        Dim pass As String = ""
        Dim email As String = ""
        Dim lowemail As String = ""
        Dim passwardformat As String = ""
        Dim isappr As String = ""
        Dim islock As String = ""
        Dim username As String = ""




        Dim excelConn As String = "Provider=Microsoft.Jet.OleDb.4.0;" _
                            & "data source=C:\Documents and Settings\peebman2000\My Documents\Visual Studio 2005\WebSites\testhelp3\uploads\testexcelmembership.xls;" _
                            & "Extended Properties=Excel 8.0;"
        'First DataGrid
        Dim excel_obj As New OleDbConnection(excelConn)
        Dim excelcom As New OleDb.OleDbCommand("Select ApplicationId,Password,Email,LoweredEmail,PasswordFormat,IsApproved,IsLockedOut, UserName From [Sheet1$]", excel_obj)
        'Dim strExcel As String = "Select ApplicationId,Password,Email,LoweredEmail,PasswordFormat,IsApproved,IsLockedOut From [Sheet1$]"
        Dim excel_dtr As OleDb.OleDbDataReader

        excel_obj.Open()
        excel_dtr = excelcom.ExecuteReader()
        Do While excel_dtr.Read()
            app = excel_dtr("ApplicationID")
            pass = excel_dtr("Password")
            email = excel_dtr("Email")
            'lowemail = excel_dtr("LoweredEmail")
            passwardformat = excel_dtr("PasswordFormat")
            isappr = excel_dtr("IsApproved")
            islock = excel_dtr("IsLockedOut")
            username = excel_dtr("UserName")



            Dim dataadapter As SqlDataAdapter
            Dim datacommand As SqlCommand

            dataadapter = New SqlDataAdapter
            datacommand = New SqlCommand

            datacommand.CommandText = ("aspnet_Membership_CreateUser")
            datacommand.Connection = New SqlConnection(ConfigurationManager.ConnectionStrings("dave").ToString)

            dataadapter = New SqlDataAdapter("aspnet_Membership_CreateUser", datacommand.Connection)

            datacommand.CommandType = Data.CommandType.StoredProcedure

            datacommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = app

            datacommand.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = pass

            datacommand.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email

            'datacommand.Parameters.Add("@LoweredEmail", SqlDbType.NVarChar, 256).Value = lowemail

            datacommand.Parameters.Add("@PasswordFormat", SqlDbType.Int).Value = CType(passwardformat, Int32)

            datacommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username

          '

            datacommand.Connection.Open()
            Try
                datacommand.ExecuteNonQuery()
                Response.Write("inserted data")
                Response.End()
            Catch ex As Exception
                Response.Write(ex.Message.ToString() + "Problem")
                Response.End()
            End Try

            datacommand.Connection.Close()

        Loop

        excel_dtr.Close()
        excel_obj.Close()
        'Try
        '    Response.Write(app & pass & email & lowemail & passwardformat & isappr & islock)
        '    Response.End()

        'Catch ex As Exception
        '    Response.Write(ex.Message.ToString() + "error with code")
        '    Response.End()
        'End Try




        'lblSql1.Text = strExcel
        'Dim objCmd As New OleDbCommand(strExcel, excel_obj)
        'Try
        '    excel_obj.Open()
        '    GridView1.DataSource = objCmd.ExecuteReader()
        '    GridView1.DataBind()
        'Catch exc As Exception
        '    Response.Write(exc.ToString())
        'Finally
        '    excel_obj.Dispose()
        'End Try

    End Sub
End Class

Thanks wolffy, that worked, I like that. I may have some questions about creating a new user with roles. I'm practicing with that now, so you may or may not hear from me again on this topic.

But again I appreciate your help dude, take care.

Code:

  Dim newUser As MembershipUser = Membership.CreateUser(username, pass, email)
            'newUser.Comment = comment.Text
            Membership.UpdateUser(newUser)

Originally Posted by Wolffy

Yeah, I thought that trying to use the sprocs directly would be kind of a hack. Now that I see you code and have a second think, it would actually be easier to use the Membership.CreateUser method and avoid a lot of this BullFeathers. I bet you have all the info you need now from reading the Excel SS to just call one the CreateUser methods.