I thought I would give some information on the methods I used to prepare a user import and notify users.

In this case I was consolidating user data from a number of sources to launch a new customer service site. I need to notify customers of their profile creation - but I did not want to use CBJuice's built-in email generation for the reasons noted below. I also needed to work with date fields. Finally I wanted to work with excel because I am familiar with it and did not want to install open office for this one project.

A main trick here (not completely new) is to create your own password and import as the user password via the "password" field, but also into CB as an initial password field so it can be merged into a mass mail.

Finally if you haven't used CSVEDT, it has some nice features for splitting and merging columns, but also has some quirks.

Here goes:

1) I did NOT use the CBJuice notification messages because: a) I wanted to use HTML email, b) I wanted to use my mailing software (acymailing) so I could catch bounces and c) allow immediate unsubscribes from the notification list

2) I created a password field and ALSO a "cb_initpwd" password field in my CSV so that I could mail merge passwords myself. When I created this field in CB, I made sure the user cannot see it by putting it on a tab called "Admin Only" and setting "Who can view" to "Super Administrator"

3) I used a random password generator macro in excel to generate the passwords. It is here: www.neoegm.com/tech/software/excel-add-ins/excel-random-password-generator/. You DO NOT need to create cells to store the values like in the example. Instead do "=randomPassword(8,TRUE,TRUE,TRUE) so that you do not have extra cells in your file.

4) You cannot duplicate the column with the macro active or the passwords will not match. So save an XLS version with the macro, then export to CSV and reload in excel - now the passwords will be static text (because the csv export copied the literal result of the macro). Now duplicate the password column and give the second column the cb fieldname where you want to store the initial password.

5) If you are working with CB dates in excel, apply a custom format of "yyyy-dd-mm" to any CB date fields. Each time you open a CSV the date format will go back to the excel default (because there is no format data stored in CSV) so you have to reformat the column.

6) Used CSVEDT to add double quotes to an Excel generated CSV. It is here: csved.sjfrancke.nl/ This functionality is QUIRKY and challenging to find. The option is under "Tools > Options > Double Quotes" HOWEVER, the application remembers the setting - yet it does not apply it at save time. It applies it when you make the change in the setting. So the first time it works fine - but on subsequent tries you must FIRST turn the option off, exit CSVEDT, open CSVEDT, open your excel generated .CSV, turn the double quotes option on (right then I think it makes the file change) and save it. Next time you have to once again ensure the Double Quotes option is OFF when you start CSVEDT to open your excel generated CSV. I tested toggling the setting when I had opened a .csv with Double Quotes already ON and it did not work.

7) Open the file in a text editor (i use pspad) and remove the double quotes from the first line.

8) CSVEDT is also good for spliting and combining columns. It is NOT good at copying columns - I had it fail everytime, so I used excel for that.

I know people mention the use of open office here, but I would have to install it just for this project. Also CSVEDT has some cool features for the money (free).

D.

Post edited by: djsdjs, at: 2010/06/24 13:44

Post edited by: djsdjs, at: 2010/06/24 13:47