Ventil8.com Blog

Import legacy Customers into Nop Commerce 2.XX

Filed under Development , SQL

This was no easy task and took arund 3 days of banging my head against the wall but got there in the end...

We had an old ecommerce system which we were migrating to the new (and Fantastic) Nop Commerce 2.30, there is currently no import option in this version, removed in version 2.0 i believe. So here is a step by step process of what I had to do.

Step 1 -  Create Hashed passwords and Salt Key

Using the old database table for Customers, in my case "RegisteredUsers" which only stored the passwords in plain text i created two new columns, NopPassword and PasswordSalt.

Against that table i ran the following SQL Query.

DECLARE @NopPassword VarChar(50)
DECLARE @MyUserID INT
DECLARE myMaintblCursor CURSOR FOR
SELECT RegisteredUsers.UserID FROM RegisteredUsers

OPEN myMaintblCursor

--Read the initial User ID value from the cursor
FETCH NEXT FROM myMaintblCursor
INTO @MyUserID

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Salt VarChar(50)
EXEC dbo.sp_GenerateRandomString 8, @Salt OUTPUT

UPDATE RegisteredUsers

SET NopPassword = SUBSTRING(UPPER(CONVERT(VarChar(50), HASHBYTES('SHA1', Password + @Salt),1)),3,40), PasswordSalt = @Salt
--CONVERT(NVARCHAR(MAX), HASHBYTES('SHA1', Password + @Salt),1), PasswordSalt = @Salt
--SUBSTRING(UPPER(master.dbo.fn_varbintohexstr(CAST(HASHBYTES('SHA1', Password + @Salt)as varbinary ))),0,40), PasswordSalt = @Salt

WHERE RegisteredUsers.UserID = @MyUserID

--get the next User ID value from the cursor
FETCH NEXT FROM myMaintblCursor
INTO @MyUserID
END

DEALLOCATE myMaintblCursor

Probably not the best way to do this but worked for me, I had to use a cursor so that I could generate a Randon Salt key for every row, then i added this to the origonal user password.  This was where i had the biggest problem, i intitally created the new Columns with NVARCHAR(MAX) to match the NOP DB, which meant the HASHED version of the password was coming in wierd symbols, Then i tried to convert this using "master.dbo.fn_varbintohexstr" but all i ever ended up with was a HEX STRING value which was wrong.

Solution was to change the NopPassword and Password salt to VARCHAR(50) which gave me the Key, but 2 characters to big and lowercase. Nop does not like the password hash in lower case so thats where the following line came in.

SUBSTRING(UPPER(CONVERT(VarChar(50), HASHBYTES('SHA1', Password + @Salt),1)),3,40), PasswordSalt = @Salt

I used the following website to check if things were correct, at last it was! Woop woop!

Also you will need the following SP to generate the random Salt Key

PROCEDURE [dbo].[sp_GenerateRandomString]

    (

      @sLength INT = 10 ,

      @randomString NVARCHAR(255) OUTPUT

    )

AS

    BEGIN

        DECLARE @counter INT ;

        DECLARE @nextChar NCHAR(1) ;

        SET @counter = 1 ;

        SET @randomString = '' ;

 

        WHILE @counter <= @sLength

            BEGIN

                SET @nextChar = NCHAR(ROUND(RAND() * 93 + 33, 0)) ;

                IF ASCII(@nextChar) NOT IN( 34, 39, 40, 41, 44, 46, 96, 58, 59 )

                    BEGIN

                        SET @randomString = @randomString + @nextChar ;

                        SET @counter = @counter + 1 ;

                    END ;

            END ;

    END ;

Step 2 -  Import User details

From here i wrote an import script to import the users from the old DB into NOP.

INSERT INTO Nop.dbo.Customer
(Id,CustomerGuid, Username, Email, Password,PasswordFormatId, PasswordSalt, TaxDisplayTypeId, IsTaxExempt, VatNumberStatusId, UseRewardPointsDuringCheckout, Deleted,IsSystemAccount, CreatedOnUtc, LastActivityDateUtc, Active, BillingAddress_Id, ShippingAddress_Id)
SELECT UserID, NEWID(),EmailAddress, EmailAddress, NopPassword,'1', PasswordSalt, '0','0', '10', '0', '0', '0', GETDATE(),GETDATE(), '1',UserID, UserID  FROM OldSite.dbo.RegisteredUsers
WHERE OldSite.dbo.RegisteredUsers.ApprovedStatus = 'Approved'

NOTE: Indenity needs to be set to NO as we need to keep the same ID for the users across all the relavent tables, remember to turn that back to Yes.

Step 3 - Setup Address mapping

next up is the address.

INSERT INTO Nop.dbo.CustomerAddresses
(Customer_Id, Address_Id)
SELECT Id, Id FROM Nop.dbo.Customer
WHERE Nop.dbo.Customer.Id > 7

I only used the where statement becuase I had some existing data in the new Nop DB.

NOTE: You will need to turn of the Foreign Key retrainst on both Shipping and billing

Step 4 - Setup Roles

Then the Role mapping

INSERT INTO Nop.dbo.Customer_CustomerRole_Mapping
(Customer_Id, CustomerRole_Id)
SELECT Id, '3' FROM Nop.dbo.Customer
WHERE Nop.dbo.Customer.Id > 7

3 is the ID for registered users, but you could use any ID you wanted.

Step 4 - Import Address's

Then the Users address's.

INSERT INTO Nop.dbo.Address
(Id,FirstName, LastName, Email, Company, City, Address1, Address2, ZipPostalCode, PhoneNumber, CreatedOnUtc)
SELECT UserID, FirstName, Surname, EmailAddress, CompanyName, TownCity, Address1, Address2, Postcode, Telephone, GETDATE() FROM Live.dbo.RegisteredUsers
WHERE Live.dbo.RegisteredUsers.ApprovedStatus = 'Approved'

Optional

The only thing which i did not do which you could is add in the address/name and email details into the CustomerAttribute table, you need this if you want the users to see thier address on the first page of "My Account".

Hope this helps anyone having to do the same thing and saves them some time.!