In an earlier article, I described a mailing the Service Center sent out to treatment providers in the city of San Francisco. The data source for that mailing was the online Substance Abuse Facility Locator created in January 2000 by SAMHSA (the Substance Abuse and Mental Health Services Administration of the U.S. Department of Health and Human Services.
In the earlier article I complained that the SAMHSA database was posted in an elaborate screen format that made it laborious to download the data. Since then, SAMHSA has posted its database in raw table format, which makes it almost a breeze to download the data and use it in a mailmerge project, such as this one to Rochester MN.
To access this database in computer-readable format, point your browser to
This is the start page for the Drug and Alcohol Services Information System. From there, click on
which gets you to http://wwwdasis.samhsa.gov/ows-bin/owa/NFRFTP$.Startup , a listing of databases in .zip file format, by state. Click on the state you want. Up comes a screen that invites you to click to download a .zip file. Click and save the file to your disk in a location where you can find it again. Then use PKZIP or any functional equivalent, such as WinZip, to open the .zip file and extract its contents to your disk. The contents of the .zip file (for Minnesota) consist of three separate files:
- mn_rpt.txt
- mn_dat.txt
- readme.txt
The file you want is mn_dat.txt. This contains the facilities data in plain text (ASCII) format with fixed-length records separated by a vertical line | as delimiter. Either the fixed-length format or the delimiter would have been enough, but I guess SAMHSA wanted to make sure, so they gave us both.
Next I imported the dat.txt file into Microsoft Access. Probably any other database will handle the information equally well, and you might even be able to use the dat.txt file directly as a Mailmerge data file in your word processor, if you massage it a bit. I used Access. If you are using Access, the procedure is this:
Create a new blank Access database. On the File menu, click on "Get External Data" and then on "Import." In the file browse box that opens, set the file type to "Text files." Find the directory where you stored the mn_dat.txt file. Click on it. Then click "Import." When the "Import Text Wizard" opens, click on "Delimited." Then click "Next." Where it asks you to "Choose the delimiter," click on "Other" and enter the vertical bar | in the field next to "other." Also click "First Row Contains Field Names." Click "Finish." You should in moments have a new Access table named "mn_dat." Open it and you will see a listing of 312 facility names and addresses.
Next, create a new Access query on the table, so as to pull out the Rochester records. If you know the target zip codes you could query by that field. I simply entered "Rochester or Winona" in the "address" field. (A quick spot check showed that the smaller towns in the vicinity of Rochester had no listings.) A short list of 20 facilities resulted. Inspecting the data showed that the street address field in one record was blank. I manually copied the "mailing address" data into this field so as to create a usable record. (If you run into a lot of data problems of this kind, more complicated methods of massaging the data are available.) I saved the query as "RochesterMN" and closed Access.
It was then a straightforward process of creating the form letter and a mailing label in MS Word, linking to the new "RochesterMN" query as data source, and running the job on the printer. After that it's collate-stuff-and-lick, and it's done.
A word about the economics of this project. This was an expensive mailing. The "Treatment booklet" in photocopy format, tape bound, costs $4.40 each. Add 40 cents for the 4 brochures, plus about 30 cents for the envelope, cover sheet, and mailing label. Add $1.65 for postage. Total $6.75 each. Total cost for this small mailing: $135.00, labor donated. I thought it was worth doing because of the long-term importance of gaining a toe-hold in Minnesota, the bastion of the "Minnesota Model" -- and to back up the excellent work that the local convenor, Roger L., has done in obtaining a room, getting local press coverage, and distributing flyers.
If the treatment providers send their clients to the meeting, and if the clients put money in the meeting basket, and if the meeting treasurer sends the basket surplus over local needs to the Service Center, then the Service Center will be in a financial position to continue this kind of service indefinitely. I am more than happy to donate my labor to the extent practicable, but I hope people understand that the Service Center cannot at this early point in our development afford to do this kind of mailing for each and every locality where a new meeting gets underway. I am posting the how-to instructions here with the thought that computer-savvy local convenors can use local initiative for similar efforts.
One measure that will cut the cost of this kind of mailing substantially is to get the "Treatment booklet" printed commercially. The LifeRing Board of Directors have approved a proposal to print this item as a paperbound book, half-sheet format, with glossy cover and spot color. This will reduce the cost per copy from $4.40 to $2.04 each, and make for a more attractive and professional presentation besides. The postage should also be slightly cheaper, and we can use a more attractive half-size envelope. Look for the announcement that the print edition is ready around mid-September.
-- Marty N. 8/5/00
