Planet Gnanet

2012 április 26, 13:29 · only

bzr branch lp:ubuntu-online-tour/12.04

cd 12.04
cd translate-html/bin
./translate-html -t

#!/usr/bin/python

# Formstack email scraper, by Dylan McCall 

import argparse

import codecs
import sys
import csv

# Fields that have arbitrary, user-entered text as values, so the parser knows
# to ignore reserved characters like "="
FREE_INPUT_FIELD_NAMES = ["Other", "Please specify", "Do you have any other comments about toxic ingredients in home cleaning products?"]

def main():
	parser = argparse.ArgumentParser(description='Convert a CSV file full of Formstack overflow emails back to a useful format.')

	parser.add_argument('input', metavar='mail',
		help="The CSV file with Formstack email text in its \"Body\" column")
	parser.add_argument('output', metavar='output',
		help="A file to store the results in")

	parser.add_argument('--input_delimiter', default=',',
		help="Value separator used for input file. Escape sequences like \\t are allowed.")
	parser.add_argument('--headers', default='headers.csv',
		help="A CSV file from Formstack's Export Submissions feature. Only the top part (column names) will be used.")
	parser.add_argument('--unique_id_start', metavar="N", default=99900000, type=int,
		help="Starting point for unique submission IDs")

	args = parser.parse_args()
	args.input_delimiter = args.input_delimiter.decode('string_escape')

	try:
		headerFile = open(args.headers, 'r')
	except IOError as error:
		print "I couldn't open %s." % args.headers
		print "Please use Formstack's Export Submissions feature to download form"
		print "submissions as a CSV file. Save the file to the same place as this"
		print "script, and name it headers.csv."
		print "The file doesn't need any real submissions. I only need it for the"
		print "row of column names at the top."
		sys.exit()

	inFile = open(args.input, 'r')
	outFile = open(args.output, 'wb')

	submissionData = []

	# This bit of code really hinges on how your mail format works.
	# Right now, it's written for Microsoft Outlook's "Export messages as CSV"
	# feature, which has a Body column for message text
	mailReader = csv.DictReader(inFile, delimiter=args.input_delimiter, quotechar='"')
	for row in mailReader:
		mailBody = row['Body']
		data = getDataFromMail(mailBody)
		# Some messages may not have interesting data, so we ignore those
		if len(data) > 0:
			submissionData.append(data)

	# Field names can repeat, so we need to figure out the order they appear
	columns = []
	headerReader = csv.reader(headerFile, delimiter=',', quotechar='"')
	columns.extend(headerReader.next())

	dataWriter = csv.writer(outFile)
	dataWriter.writerow(columns) # Print column names at top of CSV file

	for index, data in enumerate(submissionData):
		# Populate the Unique ID field, which isn't in the message body
		data['Unique ID'] = args.unique_id_start + index

		outputRow = list(['']) * len(columns)
		for fieldID, fieldValue in data.iteritems():
			fieldParts = fieldID.split('###', 2)
			fieldName = fieldParts[0]
			fieldNumber = int(fieldParts[1]) if len(fieldParts) > 1 else 0

			# Find the [fieldNumber]th repetition of the column called fieldName
			columnRepeats = 0
			colNumber = findNth(columns, fieldName, fieldNumber)
			if colNumber >= 0:
				outputRow[colNumber] = fieldValue

		dataWriter.writerow(outputRow)

def findInList(lst, needle, start=0):
	try:
		position = lst.index(needle, start)
	except:
		position = -1
	return position

def findNth(lst, needle, n):
	needleRepeats = 0
	foundIndex = findInList(lst, needle, 0)
	while needleRepeats < n and foundIndex >= 0:
		needleRepeats += 1
		foundIndex = findInList(lst, needle, foundIndex+1)
	return foundIndex

'''
Extract all of the fields listed in the given message body.
@return dictionary of raw field data, mapping a field name+++number to its value
'''
def getDataFromMail(text):
	fields = {}

	IGNORE_COLUMNS = frozenset([
		"WARNING" #Formstack's warning message preceding information dump
	])

	lines = text.split('\n')

	currentKey = None
	currentValue = None

	for line in lines:
		if currentKey == None:
			# Looking for the start of a new field

			if line.find(': ') > -1:
				# Start of a new field.
				# This field can span many lines. Store currentKey so the next lines will be appended.
				# Record field name and first value (after ": ") if applicable
				key, val = line.split(': ', 1)
				key = key.strip()
				val = val.strip()

				if not key in IGNORE_COLUMNS:
					currentKey = key
					currentValue = list()
					if val:
						currentValue.append(val)

		elif currentKey and line.strip() == '':
			# Blank line always means the end of the current field.

			if len(currentValue) == 0:
				currentValue.append('')

			processedKeyValues = processField( (currentKey, currentValue) )

			for finalKey, finalValue in processedKeyValues:
				duplicates = 0
				uniqueFinalKey = "%s###%d" % (finalKey, duplicates)
				while uniqueFinalKey in fields:
					duplicates += 1
					uniqueFinalKey = "%s###%d" % (finalKey, duplicates)

				fields[uniqueFinalKey] = finalValue

			currentKey = None
			currentValue = None

		elif currentKey:
			currentValue.append(line.strip())

	return fields

def processField(field):
	fieldKey = field[0]
	fieldValue = field[1]

	orderedSubFieldKeys = list()
	subFields = dict()

	data = list()

	for value in fieldValue:
		# This behaves poorly for values that, themselves, contain " = "

		# Ugly hack for cases with user input
		if fieldKey in FREE_INPUT_FIELD_NAMES or value.startswith("Other:"):
			splitValue = [value]
		else:
			splitValue = value.split(' = ', 1)

		subFieldKey = ''
		subFieldValue = ''

		if len(splitValue) == 2:
			subFieldKey = splitValue[0]
			subFieldValue = splitValue[1]
		elif len(splitValue) == 1:
			subFieldValue = splitValue[0]

		if not subFieldKey in orderedSubFieldKeys:
			orderedSubFieldKeys.append(subFieldKey)
			subFields[subFieldKey] = list()
		subFields[subFieldKey].append(subFieldValue)

	for subFieldKey in orderedSubFieldKeys:
		if subFieldKey == '':
			completeHeader = fieldKey
		else:
			completeHeader = "%s (%s)" % (fieldKey, subFieldKey)

		subFieldValueList = subFields[subFieldKey]
		completeData = '|'.join(subFieldValueList)

		#Final output!
		data.append( (completeHeader, completeData) )

	if len(data) == 0:
		print "Aaah!"

	return data

if __name__ == "__main__":
	main()
python extract-data.py mail.csv output.csv --headers=headers.csv

Planet Gnanet

április 26, 2012

Related posts

április 25, 2012

Where is it?

How can I use it for my LoCo website?

Try for yourself!

A few screenshots

Related posts

New in 0.3: Sound Menu

New in 0.4: Quicklists

Feature Roundup

Elöfizetések