My notes on importing users manually

AbleMods · Post by **AbleMods** » Sun Dec 02, 2007 8:22 pm

Well my user import is done. Took me 6 hours to write and test the code as I had no documentation on the data structure or exposed methods.

Everything worked pretty well. The biggest gotcha was country codes. My previous system only stored the country name. You can't do that in AC7, you have to store the country code. The country name is a read-only element in the address collection.

So I had to do some heavy digging to find out how Able was doing it. Once I figured that out, it was easy.

But now I have yet another wall to climb. I didn't want to sign every user up to a mailing list without their permission. But I need to notify them of this migration. So, I basically imported 1,600 live user accounts and have no AC7 method for contacting them all at once. Ugh. I tried doing a mail merge email in Word 2007 as a last resort. Word repeatedly locks up. Ugh x2.

My mail server software supports opt-in, opt-out and bounce cleanup etc. I may try working with that instead of AC7 email lists. Might be better for me in the long run anyways <shrug>.

nborelli · Post by **nborelli** » Mon Dec 03, 2007 9:35 pm

Hi Joe,

If you are comfortable with SQL Server Transact SQL, you could do the following. In fact this is sort of what I did for our customer migration:

Add a new e-mail list that is specifically for existing customers that you are porting. This will add a new record to the ac_EmailLists table. Query this table to get the primary key of the new list.

SELECT * FROM dbo.ac_EmailLists

Lets say for example that the EmailListId is 2. You can do the following to add all of your users to this new list:

INSERT dbo.ac_EmailListUsers(EmailListId, FailureCount, EMail, SignupDate)
SELECT 2, 0, EMail, GETDATE()
FROM dbo.ac_Users
-- You could add a WHERE clause to only add certain users.

You could then create a new e-mail template to send out an e-mail to everyone in this list. In fact, I have gone one step further and actually have gamed the DB and included a link that sends them directly to the PasswordHelp.aspx page. If you want more info about how to do this, send me a private e-mail and I will fill you in.

-Neal

compunerdy · Post by **compunerdy** » Tue Dec 04, 2007 1:17 am

I will need to import my users soon. My current store does not have the password encrypted and is a ms sql database as well. Is there a "importing for dummies" tutorial? Or could someone help me write a script for doing this?

nborelli · Post by **nborelli** » Tue Dec 04, 2007 8:14 am

Eventually AbleCommerce will have their Data Import/Export utility finished, but in my experience, there are a lot of cases where you want to massage the data before importing it. Things I ran into were changing Country and Providence codes, removing bogus records and reformatting certain data like phone numbers and postal codes. I even run SQL statements to change product prices, add product keywords, etc.

The cart I am coming from had encrypted passwords, but they wouldn't tell me how they were encrypted and couldn't export them to plain text. I wound up exporting the user data to a CSV file and then importing it using the BULK INSERT Transact SQL Statement. I then wrote a stored procedure to add each user to the database. The dbo.ac_Users table has a column named "comment" that is used in a special way when user's forget their password. I generate a unique 14 character value for this column that will be used in the e-mail I send out. I also created a E-mail list that is just for the imported customers and add then to it during the import. The e-mail tells them about the new storefront and gives them a discount coupon to use on their next visit. It also contains a link to let them set their new password.

I then created a new e-mail template that looks like this:

Code: Select all

<html>
<head>
<style>
p {  margin: 10px; }
.coupon { font-size: 14px; font-weight:bold }

TABLE.Email {
width: 640px;
padding: 5px;
margin: 0px;
border: 1px solid #7C8A4D;
}
TABLE.Email TH {
font-weight: bold;
font-size: 12px;
color: #ffffff;
font-family: Arial, Verdana, Sans-Serif;
font-style: strong;
background-color: #304FBA;
text-align: center;
text-decoration: none;
padding: 5px;
}
TABLE.Email TD {
font-weight: normal;
font-size: 12px;
color: #000000;
font-family: Arial, Verdana, Sans-Serif;
background-color: #ffffff;
text-align: left;
text-decoration: none;
padding: 3px;
}
</style>
</head>
<body>
<table>
<tr>
<td>
<p>Hello,</p>
<p>
$store.Name has a new storefront that is greatly improved!
</p>
<p>
As a gift for being a loyal $store.Name customer, please use coupon code <span>"NEWNUBIUS"</span> the next time you order for a 10% discount!
</p>
<p>Most of your account information has been transferred to the new store, but for security reasons, we have to ask you to reset your password. You can do this by simply clicking the link below and specifying a new password.  Alternatively, you can click the "Forgot Password?" link in the returning customers login screen the next time you visit $store.Name.
</p>
<p><strong>Click the link below to reset your password:</strong></p>
<p>                    <a>${store.StoreUrl}PasswordHelp.aspx?Key=${customer.UserId}&Check=${customer.Comment}</a></p>
<p>If clicking the link doesn't  work, you can copy and paste the link
into your browser's address window, or retype it there. Once you have
returned to $store.Name,
we will give you instructions for resetting your password.</p>
<p><strong>Thank you for visiting $store.Name!</strong><br>
<a><strong>$store.Name</strong></a></p>	
</td>
</tr>
</table>
</body>
</html>

Notice the part that reads:

Code: Select all

<a>${store.StoreUrl}PasswordHelp.aspx?Key=${customer.UserId}&Check=${customer.Comment}</a>

This will create a URL for them to reset their password.

I know this isn't easy, in fact, it took me about 6 hours to figure all of this out and I have been using SQL Server for 12 years. If you are interested in the stored procedure I wrote to add users, I can post that also.

Good luck,
-Neal

Hostmaster · Post by **Hostmaster** » Tue Feb 19, 2008 2:05 pm

Joe, Did you import user passwords ?

AbleMods · Post by **AbleMods** » Tue Feb 19, 2008 2:57 pm

Yes, I was storing them clear-text in the MSAccess table. Ones that were encrypted from the "early days" of my site were reset to a default password and an email notification was sent.

Since I wrote my own import routine, I was able to add additional automation.