parse-report.py

from datetime import datetime, date
from json import dumps
import fileinput
import re
import sys

# detect useless "page x of n" lines
page_regex = re.compile(r'^Page,[0-9]+,of,[0-9]+')

def parse_input():
    # skip the first line
    page_header = sys.stdin.readline()

    # lines are hard-wrapped. gather lines that belong together to join them
    # later
    lines_to_join = []

    for line in fileinput.input():
        # skip lines that only give a page number
        if page_regex.match(line):
            continue

        # the first line is repeated several times, skip that too
        if line == page_header:
            continue

        if len(lines_to_join) == 2:
            yield ','.join(lines_to_join)
            lines_to_join = []

        lines_to_join.append(line.rstrip())

for line in parse_input():
    if line == 'Site Name,Technician,Desciption,When Started,Duration (in minutes),Service Date,When Ended':
        # skip headers
        continue

    values = line.split(',')

    if len(values) == 7:
        site_name, technician, description, when_started, duration, service_date, when_ended = values
    else:
        print('bad line: "' + line + '"', file=sys.stderr)
        continue

    # parse the dates
    when_ended = datetime.strptime(when_ended, '%m/%d/%Y %I:%M:%S %p')
    when_started = datetime.strptime(when_started, '%m/%d/%Y %I:%M:%S %p')

    print(dumps({
        'service_date': datetime.strptime(service_date, '%m/%d/%Y').strftime("%Y-%m-%d"),
        'when_started': when_started.isoformat(),
        'when_ended': when_ended.isoformat(),
        'site_name': site_name,
        'technician': technician,
        'description': description,
        #'duration': int(duration),
        'duration': abs((when_ended - when_started).total_seconds()) / 60,
    }))