Kurt Seifried

Why I love OpenSource – adding CSV export capability to Mailman

So mailman is by far one of the best mailing list management packages available. One thing I love is the command line access, Because of this I can write simple scripts like:

for list in `/usr/lib/mailman/bin/list_lists -b`
/usr/lib/mailman/bin/list_admins $list > /root/mailman-export/$i-admins.txt
/usr/lib/mailman/bin/list_owners $list > /root/mailman-export/$i-owners.txt
/usr/lib/mailman/bin/list_members $list > /root/mailman-export/$i-members.txt

And get a nice list of all the admins, owners and members for each list hosted on that server. I can then do things like grep for a specific user or domain across all the lists and then put that data into further scripts (e.g. to remove every account from a specific domain from every list). But not everyone wants a big list of text files. I had one administrative user that wants to run some analytics, they wanted a CSV file for every list in the form:

"list name","email address","user name","access level"

so for example:

"announcements","kurt@seifried.org","Kurt Seifried","member"

and so on. Now I could take the above bash script and modify it sufficiently to take the output and turn it into a CSV file, but there is probably a more elegant way. If you look at the “/usr/lib/mailman/bin/list_members” script you’ll see it’s pretty simple. In order to add CSV export capability I first copied it to “/usr/lib/mailman/bin/list_members-csv” and then opened it up in an editor.

First we’ll need support for CSV and datetime (so we can timestamp the output), just go to the import statements and add:

import csv
import datetime

Then you’ll want to create a CSV file to write to, I want my files in “/root/list-exports/” in a file name  that has the list name, membership level and date (which is redundant but makes dumping all the outputs into the same dir easy and safe). Simply go into “__main_-” and find the line:

listname = args[0].lower().strip()

Then add something like:

datestring = str(datetime.date.today())
cvsoutputdir = "/root/list-output/"
cvsoutputfilename = cvsoutputdir + listname + "-members-" + datestring + ".csv"
csvoutputfile = open(cvsoutputfilename, "wb")
csvwriter = csv.writer(csvoutputfile, dialect='excel', quoting=csv.QUOTE_ALL)

you now have a file name in the form “listname-members-date.csv”, all the data will always be quoted and the output will be in the format preferred by Excel (so for escaping/etc. it’ll use the characters Excel is expecting). I could have integrated this with the “–output file” command line option,  but then I need logic to handle the datetime and membership level and list name in the wrapper using my modified version of list_members, so it’s easier (for me) to just stick that logic into my modified list_members.

Now you simply look for the lines where the output is actually handled and replace:

print >> fp, formataddr((safe(name), addr))


csvwriter.writerow([listname, addr, safe(name), "member"])

And you’re done.  Now you could get fancy and make it an actual option (–csv?) in the existing program, and add some switch logic for output, but honestly, I couldn’t be bothered, this is simple enough and it works reliably.