Data Wrangling

I decided to roll our own form and review management for the Network Music Festival despite how much work this was the last time I did it. I also wanted to stop using Google and to have more flexibility.

So we deployed Ninja Forms, which has high ratings and worked great on a test website.

But then we started getting some emails. Some forms would not submit. There were no error messages in the logs. I tried a lorem ipsum generator and it wasn’t the amount of text. It wasn’t the special symbols. It worked great on the test website!

So we switched to google docs.

So then, when the call ended, I had to merge a bunch of spreadhseets together and then break them up again by what sort of performance people submitted. This is tedious and there’s always a risk of clobbering somebody’s data.

Then everything must get an ID. Rather than write a macro, I did it by hand. Which, again, is risky.

Finally, that done, I need to generate documents or whatever for reviewers to look at. In this case, I did write a macro. But then I wanted to convert all the URLs into clickable links. So I learned a little bit about how the python API works with LibreOffice.

I generally think of myself as fairly intelligent, but I found the documentation extremely hard to navigate. It definitely would have been faster and easier to also do this by hand, but by then the time reading the documents was a sunk cost.

So here’s how I’ve done it. I did a merge with the spreadsheets and a template, which is also a convoluted process that tends to crash things, because why wouldn’t it? I generated a large number of .odf files, one for each submission.

In open office, I went to Tools → AutoCorrect Options... → Options tab →
☑ ☑ URL Recognition

I turned off everything else in autocorrect. I don’t want to mangle anybody’s spelling capitalisation, etc.

Then I wrote a Python script:


# import socket  # only needed on win32-OOo3.0.0
import uno

# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()

# create the UnoUrlResolver
resolver = localContext.ServiceManager.createInstanceWithContext(
				"com.sun.star.bridge.UnoUrlResolver", localContext )

# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager

# get the central desktop object
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)

# access the current writer document
model = desktop.getCurrentComponent()

# get the dispatcher
dispatcher = smgr.createInstanceWithContext( "com.sun.star.frame.DispatchHelper", ctx)
doc = model.getCurrentController()

# run the commands
dispatcher.executeDispatch(doc, ".uno:AutoFormatApply", "", 0, ())
dispatcher.executeDispatch(doc, ".uno:Save",  "", 0, ())
dispatcher.executeDispatch(doc, ".uno:Quit", "", 0, ())

I found a lot of useful documentation for this at: https://christopher5106.github.io/office/2015/12/06/openoffice-libreoffice-automate-your-office-tasks-with-python-macros.html

Now, obviously, that connects to a running version of libreoffice which has the document you want already open. Obviously. There’s a command line for libreoffice that does a few things, including opening a port to listen for python commands. It also does file conversions.

Before I go, I /know/ I could have done a file conversion in the python script, but sometimes, when evaluating time save shortcuts, it’s important to make a note of how long it would take to figure out the shortcut. None of the commands called in my python script take arguments. I’m sure passing arguments is straightforward and also not documented anywhere on the official site. (If this is wrong, I’m sorry, but yikes, you guys.)

So I wrote a bash script that traverses a directory and also calls the python script

#!/bin/bash

for file in *.odt
do
    soffice $file --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" --headless &
    pid=$!
    sleep 3
    python3 ../urlify.py
    wait $pid
    soffice --headless --convert-to htm:HTML $file
    rm $file
done

That sleep is in there because you have to wait for writer to get going and allocate a port or else the python script hangs and everything goes to hell.

The next step is to allocate 2 reviewers (one from column A and one from column B) to every item, making sure that every reviewer gets no more than 5 items, mark this in a spreadsheet and email them off.

I expect this will also take about 5 times as long as doing it by hand.

Personalised Jpegs for emailed wedding invitations

Let’s say you want to invite a bunch of people to your wedding and your soon-to-be-spouse wants some nice graphic you can mail to folks that has their name in it. You can do this! This script works with Linux and should work with OS X. It will require a few changes to work with windows.

Install some nifty software

I’m using both imagemagick and OptiPNG, both of which I got through apt-get. Probably, the png step is overkill and you could go straight to jpeg.
I’m also using Inkscape, Python and LibreOffice, but you can use any spreadsheet you want.

Make the graphic template

  1. Find some nice border of some kind. I drew one and scanned it, but there are other options.
  2. Open your nice border in inkscape (or other svg editor).
  3. Put all the text you want into your graphic, with the font you want.
  4. In the part where you want their name, put GUEST_NAME
  5. Save your lovely creation as template.svg

Compile names and email addresses

In LibreOffice, open a new spreadsheet and make a bunch of entries for your guests.
The first column should be email addresses.
If you are NOT saving the images, then the next column can all say ‘invite’ for every single entry. If you want to save the jpegs (say to post them to facebook walls as well as emailing them), then give them a short name associated with the person. This will be a file name, so it should be all one word that starts with a letter and contains only letters, numbers and underscores.
The last column should be the name you want to appear in the invite. You know from making your template how much space you have for names, so keep that in mind, if you’re deciding to put in Reverend Doctor Julius Milliband Cameron III’s full name or not. Or you may need to go back and tweak your template.
Your speadsheet should look something like this:

foo@example.com jen Jennifer
bar@example.com ralph Ralph & Morris
dr_rev@example.com ju Dr Cameron

Save this spreadsheet as guests.csv

The script

Get ready

Save your template, your spreadsheet and the script (cut and past from below), all to the same folder on your system. Call the script doemail.py
You will need to modify this script a bit.
Put in your own text where you see the part that says YOUR OWN TEXT. You’ll see it asks for your own text twice. One of those times is plain text. The other one is HTML. the plain text one is just text. Don’t include any html tags. If you put in links, you just have to put in the link as plain text. In the HTML part, you can use a lot of markup, including <a href=”blah.com”>blahblah</a> tags and whatnot. You can do inline CSS, if you like, but it’s email, so keep it relatively simple. In both sections, don’t forget to include a link to your website. And if you are using an online form for RSVPs, link to that as well.
You’ll also need to put in your own email address, and your own password. If you are using gmail, you can use an application-specific password.
Finally, you will also need to put in the smpt server for your mail server. Near the top of the file, you’ll see lines for hotmail and gmail. Delete any that don’t apply to you. If you are using a different server, you’ll have to find out what to put there.
If you want to save the nice jpegs, say to also post them in facebook messages, then look for the line:

jpegname = "/tmp/"+name+".jpeg"

and change the ‘/tmp/’ part to another directory on your system.
You need to make the script executable. Open the terminal application and cd to the directory with the script, template, and spreadsheet. Type:

 chmod +x doemail.py

This will make the script executable as a program you can run. then, double check your spreadsheet is ok. Type:

less guests.csv

It should look like:

foo@example.com,jen,Jennifer
bar@example.com,ralph,Ralph & Morris
dr_rev@example.com,ju,Dr Cameron

If you see semicolons instead of commas, then you need to change your script to tell it that. Change

guestreader = csv.reader(csvfile)

to

guestreader = csv.reader(csvfile, delimiter=';')

To run the script, still from your terminal, type:

 ./doemail.py

The Actual script to cut and paste

#! /usr/bin/python

import smtplib
import re
import subprocess
import os
import cgi
import uuid
import csv

from subprocess import call
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.text      import MIMEText
from email.mime.image     import MIMEImage
from email.header         import Header    



# me == my email address
me = "example@hotmail.co.uk"
password = "yourEmailPassword"
smpt_server = "smtp.live.com:587" #hotmail
smpt_server = "smtp.gmail.com:587" 

with open('guests.csv') as csvfile:
    guestreader = csv.reader(csvfile)
    for row in guestreader:
        you = row[0]
        name = row[1]
        salutation = row[2]

        name = ''.join(name.split())



        text = "Dear " + salutation + ",nn  YOUR TEXT GOES HERE http://YOURWEBSITE.COM"


        html = """
<html>
  <head></head>
  <body>
    <p>
<a href="YOUR WEDDING WEBSITE">
        """
        html = html + "<img src="cid:{}@example.com" alt="[More Information]" /></a></p>n<p>Dear {},</p>".format(img['cid'], salutation)
        html = html + """
<p>YOUR TEXT GOES HERE</p>
</body></html>
    </p>
  </body>
</html>
        """


        print (salutation)

        # Create message container - the correct MIME type is multipart/alternative.
        msg = MIMEMultipart('related')
        text_msg = MIMEMultipart('alternative')
        msg['Subject'] = Header(u'Wedding Invitation', 'utf-8')
        msg['From'] = me
        msg['To'] = you



        # make the image

        filename = "/tmp/"+name+".svg"
        pngname = "/tmp/"+name+".png"
        jpegname = "/tmp/"+name+".jpeg"

        svg = open("template.svg");
        svgn = open(filename, 'w');
        lines = svg.read().split('n')
        for line in lines:
            line = re.sub('GUEST_NAME',re.sub('&', '&amp;', salutation), line)
            svgn.write(line)
        #endfor

        svg.close()
        svgn.close()

        os.system("inkscape -f " + filename + " -e " + pngname)
        os.system("optipng " + pngname);
        os.system("convert -compress JPEG -quality 87 " + pngname + " " + jpegname);

        os.system("rm " + filename)

    
        # attach the image to the email

        img = dict(title=u'Invitation', path=jpegname, cid=str(uuid.uuid4()))
                                                        #cid=name)
                                                        #cid=str(uuid.uuid4()))
        with open(img['path'], 'rb') as file:
            msg_img = MIMEImage(file.read(), name=os.path.basename(img['path']))
            #msg.attach(msg_img)
            msg_img.add_header('Content-ID', '<{}@example.com>'.format(img['cid']))
            msg_img.add_header('Content-Name', img['cid'])
            msg_img.add_header('X-Attachment-ID', '{}@example.com'.format(img['cid']))
            #msg_img.add_header('Content-Disposition', 'attachment', filename=os.path.basename(img['path']))
            msg_img.add_header('Content-Disposition', 'inline', filename=os.path.basename(img['path']))

        print('<{}>'.format(img['cid']))



        # Create the body of the message (a plain-text and an HTML version).



        # Record the MIME types of both parts - text/plain and text/html.
        part1 = MIMEText(text, 'plain')
        part2 = MIMEText(html, 'html')

        # Attach parts into message container.
        # According to RFC 2046, the last part of a multipart message, in this case
        # the HTML message, is best and preferred.
        text_msg.attach(part1)
        text_msg.attach(part2)
        msg.attach(text_msg)
        msg.attach(msg_img)


        # Send the message via local SMTP server.
        server = smtplib.SMTP(smpt_server)
        server.ehlo()
        server.starttls()
        server.login(me,password)
        # sendmail function takes 3 arguments: sender's address, recipient's address
        # and message to send - here it is sent as one string.
        server.sendmail(me, you, msg.as_string())
        server.close()
        #server.quit()

        os.system("rm " + pngname)

    #end for (going through rows in the database)
    csvfile.close()
#end with