Skip to content

Instantly share code, notes, and snippets.

@sherbang
Last active July 2, 2021 13:28
Show Gist options
  • Save sherbang/8e7fa988c5f00643f93323661721f832 to your computer and use it in GitHub Desktop.
Save sherbang/8e7fa988c5f00643f93323661721f832 to your computer and use it in GitHub Desktop.
Convert 401k data from Insperity report to Guideline's import format
#!/usr/bin/env python
import csv
import pathlib
import sys
GUIDELINE_HEADERS = [
"Last Name",
"First Name",
"Traditional (Pre-tax)",
"Employer Contribution",
"Roth (Post-tax)",
"Pay Date",
"Pay Period Gross Pay",
"Pay Period Hours Worked",
"Loan Repayment"
]
HEADER_MAPPING = {
"Last Name": "Last Name",
"First Name": "First Name",
"Traditional (Pre-tax)": "Retirement,Pretax",
"Employer Contribution": "Company Match",
"Roth (Post-tax)": "Retirement,Roth",
"Pay Date": "Pay Date",
"Pay Period Gross Pay": "Gross Pay",
"Pay Period Hours Worked": "Total Hours",
}
filename = pathlib.Path(sys.argv[1])
out_filename = filename.with_name(filename.stem + '.stripped' + filename.suffix)
class DictReaderStrip(csv.DictReader):
@property
def fieldnames(self):
if self._fieldnames is None:
# Initialize self._fieldnames
# Note: DictReader is an old-style class, so can't use super()
csv.DictReader.fieldnames.fget(self)
if self._fieldnames is not None:
self._fieldnames = [name.strip() for name in self._fieldnames]
return self._fieldnames
with open(filename, 'r') as infile:
reader = DictReaderStrip(infile)
with open(out_filename, 'w') as outfile:
writer = csv.writer(outfile)
writer.writerow(GUIDELINE_HEADERS)
for row in reader:
new_row = []
for h in GUIDELINE_HEADERS:
if h == 'Loan Repayment':
# Insperity doesn't send Loan Repayment column
new_row.append(0)
elif h == 'Pay Date':
insp_date = row[HEADER_MAPPING[h]]
'mmddyyyy'
g_date = f'{insp_date[4:]}-{insp_date[0:2]}-{insp_date[2:4]}'
new_row.append(g_date)
else:
new_row.append(row[HEADER_MAPPING[h]])
writer.writerow(new_row)
print(f'Wrote stripped file to {out_filename}')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment