Parse planet XLS into CSV

.gitignore

var
cpos.csv

Makefile

CSV := $(patsubst %.xls,%.csv,$(wildcard var/*.xls))

all: $(CSV)

var/%.csv: var/%.xls
	in2csv --sheet CaseWorkDetail $< 2> $@.stderr | python3 parse.py > $@

init:
	pip3 install -r requirements.txt

parse.py

#!/usr/bin/env python3

import sys
import re
import csv


def field(s, sep=' '):
    return sep.join(s).strip()


cols = ( 'ID', 'File Ref', 'LA', 'Applicant', 'Team', 'Received Date', 'Decision Date', 'Address', 'Decision')
o = {}
count = 100

r = csv.reader(sys.stdin)
w = csv.DictWriter(sys.stdout, cols)
w.writeheader()

for row in r:

    count = count + 1

    if 'ID:' in row:
        count = 0
        o = {}
        o['ID'] = re.sub('\.0*$', '', field(row[16:20]))
        o['Team'] = row[26]
        o['LA'] = field(row[35:40])
    elif 'File Ref:' in row:
        o['File Ref'] = o['ID'] = re.sub('\.$', '', field(row[3:5]))
    elif 'Decision:' in row:
        o['Decision'] = field(row[25:35])
    elif 'Decision Date:' in row:
        o['Decision Date'] = field(row[20:25])
    elif 'Received Date:' in row:
        o['Received Date'] = field(row[20:25])
        o['Address'] = row[1]
    elif count == 3:
        o['Applicant'] = field(row[1:10])
    elif 'Next action:' in row:
        w.writerow(o)

requirements.txt

csvkit==1.0.4