Q: How can I import a list of subscribers from Microsoft Excel into LISTSERV?

By Alexander Hyder
Software Support Engineer, L-Soft

The LISTSERV web interface makes it very simple to add new subscribers to a list using the subscriber management interface. However, if you wanted to add a large number of subscribers from an external Excel sheet, copying and pasting this information can be tedious and impractical. Fortunately, LISTSERV has a feature to allow the bulk import of as many subscribers as you want from a text file.

Note, however, that LISTSERV will only accept files of a certain format. It is not able to read data directly from a spreadsheet, HTML document, Microsoft Word document or any format other than plain text. It also expects specific data in a specific order. This tech tip will describe how to export information from Microsoft Excel to a text file, which you can then upload into LISTSERV.

These steps were written for Microsoft Office 2016, although other versions should be very similar. Also, while these steps outline how to add users to a list, the text file you generate can also be used to specify users to delete from a list.

Generating a Text File from Excel

Let's say that you store your membership information in an Excel workbook. Such a file might look like this:

The first step is to create a new, blank Excel workbook. Next, in the workbook containing your subscriber data, select only the columns containing the email address (mandatory) and names of the subscribers (optional). LISTSERV will not accept any other information for bulk uploads. Then copy the email addresses (mandatory) into column "A", the first names (optional) into column "B" and the last names (optional) into column "C". The data must be in that order. Finally, you should delete any header rows. The resulting new workbook should look like the following:

Note that if you choose to include names, LISTSERV will ignore the names unless both a first and last name are specified. Otherwise, the subscription will appear as "(No Name Specified)" in the web interface. You may, of course, use a role-based name such as "Sales Manager" if you prefer.

Next, click File > Save As, and type your file name, then select Text (Tab delimited) (*.txt) as your file type and click Save. This will produce a file that LISTSERV's bulk upload feature can accept.

Uploading the Text File You Created

In LISTSERV 17.0, open the LISTSERV web interface in your browser and select Subscriber Reports under List Management in the navigation area on the left. (In LISTSERV 16.x, the equivalent option is under List Management on the Subscriber Management page.) Select your mailing list from the drop-down menu at the top if necessary, and then click on Bulk Operations next to the Add Subscriber option. Finally, Choose File, and select the file you have just created in the file explorer window that pops up.

Make sure that you have selected the Add the imported addresses option, as the others involve the deletion of subscribers.

Of course, if you indeed intend to remove some or all addresses, the other options work as described. You may use email addresses only (with or without names) when performing a delete operation, but the file must still be a plain-text list with one address per line. Just be sure to double-check which option you have selected.

Once you have uploaded the file and selected the upload function you would like, click Import. A message will appear with information on the operations performed.

The bulk importer will automatically eliminate duplicate email addresses. Users will not be informed that they have been added to the list. The importer will not validate the addresses (either syntactically or by probing), but if your list has the Auto-Delete keyword configured properly, bad addresses will be automatically deleted as normal as your list processes messages.

If the bulk upload fails with an "Error 2" or "Error 13" message, contact your site manager and pass along the information in the following section. If you are a hosting customer, please email L-Soft support.

Enabling Bulk Uploads (Site Managers Only)

Behind the scenes, the web interface will upload the user's file to a directory called "upload". This directory must be a subdirectory of the location specified by the WWW_ARCHIVE_DIR site configuration variable.

If a user encounters "Error 2", this means that the upload subdirectory does not exist. If a user encounters "Error 13", this means that the web interface CGI program user does not have write permission in that directory. Under Windows and IIS, for instance, this would be the IUSR_xxx (or equivalent) user created when IIS was installed.

You can find the value of WWW_ARCHIVE_DIR in your site configuration file (SITE.EXE in Windows or go.user in Unix), or via the web interface under Server Administration > Site Configuration. Scroll down until you see the value of WWW_ARCHIVE_DIR:

Navigate to the location specified in WWW_ARCHIVE_DIR and verify that the "upload" subdirectory exists (and create the directory if it does not):

Finally, verify that the CGI user has permission to access this location, and add the proper permissions if it does not already have them.

Got Feedback?

Let us know about topics and tasks you'd like to see covered in future tech tips.

Next Steps