close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Web development tutorial

Importing addresses from Excel into PowerMessenger

Tutorial created by Willi Schneider, rw2 Robert & Willi Schneider GbR

Categories: PowerMessenger, MySQL, PHP

rating

...so you've finally bought Webassist's PowerMessenger for your community, in order to start sending newsletters to the community members. All you have is an excel file containing the users' details...
This is a step-by-step tutorial to show one possible method to import addresses from that excel-file into powermessenger.

arrow downLet's get started...

What we need to get started:

  1. Powermessenger (*PM) properly installed on your server
  2. Excel file with address data of users to be imported into PM
  3. phpMyAdmin or any other tool to edit your database tables
  4. Make backups of all files and database tables!

Step 1: Screenshots

This is my excel-file, containing my community users' data, to import into my Powermessenger users table "pm1_users"


Here's the scheme of the "pm1_users" table, we want to add the addresses:


As you can see, my excel column names don't match those from the "pm1_users" table in PM. Additionally there are empty lines and a big headline that I really don't need. Well I just got this table from my client...

Step 2: Sanitize the excel file and save as *.csv

  1. Open the excel file
  2. Delete all unnecessary columns.
  3. For this tutorial we will only use the users first name, last name and email address.
  4. Change your excel's column names to the ones matching your "pm1_users" table
  5. Delete all empty lines between the column headers and the first address line, if there are, and below the last address to import.
  6. Save your sanitized excel file as "addresses_sanitized.xls" or whatever name you like ;-)


Having done all these steps you will have a sanitized excel file:


Now save that excel file as *.csv:
Click "File --> Save as" --> chose *.csv as file extension, and save that new file.
Open this *.csv file with a text editor like notepad and delete the first line containing your column names. The result should look like this: (Please notice, for legal reasons, all of the names are blurred, except the last line "John Smith", serving as a dummy, to show how such an address line should look like.

Step 3: Get ready for importing the addresses

Now we are almost good to go. We need one last step, before performing the import:

Open your text editor and type the following:

UserFirstName,UserLastName,UserEmail

Copy this line.

Now open PHPMyAdmin in your favorite browser and click to view the "pm1_users" table.


Click "Import"
On the import page click on "file" --> "browse" and select your sanitized *.csv file
See this screenshot for the right settings (Please note, that I'm working on a German computer, thus the expressions in your browser might be slightly different, but I'm sure you'll find the correct places):


Paste the line that you have just copied (UserFirstName,UserLastName,UserEmail) into the form field "column names" and click o.k.

If everything was correct, you should now have imported your addresses from your excel file into PM.

Step 4: Additional steps to consider

We have only used three columns for the import, which was o.k..
But what about the other columns?

o.k. the "UserID" column will get an auto_increment value, so we don't have to care about this one. But what about the rest?
Should we add a default password?
Should we set the Email verified column to "1"?
Should we add a default "UserGroupID"?

Well, what ever you like...
However all of these things can already be done in the sanitized excel file.
Just add the needed columns with the correct column names and add the default values in excel.

Things to remember:
Be careful for legal issues, since it's you who adds the users. Dont' forget to inform your users.
There's no "double opt-in" when you add the addresses!
Your users dont' know their passwords. Remember to inform your users about that, too.
Add a page on your website, where your users can modify their data,
or get their password (re-)sent,
add a page where your users have the ability to unsubscribe.
...

arrow downReviews and comments

Comments will be sent to the author of this tutorial and may not be answered immediately. For general help from WebAssist, please visit technical support.

Sign in to add comments
rating

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.