How to import customer info

Hi,

How can I import customer information from a data source, such as Excel file, by SQL statements?

After studied the customer table structure, I found fields "CustomerGuid", "Password" and "PasswordSalt" are some characters generated by system, how can I generate them manually?

Pls help.

Cheers,
Daniel

It's possible in SQL.  Normally the user would select their password when they register for the site so what do you want to set the passwords to?  Do you have access to the users' passwords in plain text format (or something that can be decrypted to plain text) from some other system?

Thanks for your advices. Regarding to customers' password, i have solutions to resolve it, but don't know how to generate the CustomerGuid fieldvalues.

Any Guid will do since it doesn't relate to the rest of the fields so just use NEWID() in your select statement when you insert the customer records:

INSERT INTO Customer
           (CustomerGuid
           ,Username
           ,Email
           ,[Password]
           ,PasswordFormatId
           ,PasswordSalt
           --,AdminComment
           ,IsTaxExempt
           ,AffiliateId
           ,VendorId
           ,HasShoppingCartItems
           ,Active
           ,Deleted
           ,IsSystemAccount
           --,SystemName
           --,LastIpAddress
           ,CreatedOnUtc
           --,LastLoginDateUtc
           ,LastActivityDateUtc
           --,BillingAddress_Id
           --,ShippingAddress_Id
       )
     SELECT NEWID()
           ,CUSTNMBR
           ,Email
           ,HashedPassword
           ,1 --PasswordFormatId
           ,Base64Salt --PasswordSalt
           --, AdminComment
           ,0 --<IsTaxExempt, bit,>
           ,0 --<AffiliateId, int,>
           ,0 --<VendorId, int,>
           ,0 --<HasShoppingCartItems, bit,>
           ,1 --<Active, bit,>
           ,0 --<Deleted, bit,>
           ,0 --<IsSystemAccount, bit,>
           --,<SystemName, nvarchar(max),>
           --,<LastIpAddress, nvarchar(max),>
           ,GETUTCDATE() --<CreatedOnUtc, datetime,>
           --,<LastLoginDateUtc, datetime,>
           ,GETUTCDATE() --<LastActivityDateUtc, datetime,>
           --,<BillingAddress_Id, int,>
           --,<ShippingAddress_Id, int,>
    FROM PasswordUsers

I've also got a complete version of a customer import stored procedure which includes generating random salts and using them to correctly hash and encode existing user passwords if it would be useful?

Thanks for your advice!

Hi,

I have successfully loaded customer basic info into database by using the SQL. But, how can I add customer name/ phone/ etc to database by SQL?

Thanks a lot,

Daniel

You can add them to the GenericAttribute table.  Just have a look at what's already in there and follow the format.  If you have multiple addresses per customer you might also want to look at the Address and CustomerAddresses tables.