Skip to content

Instantly share code, notes, and snippets.

@hktonylee
Created March 7, 2018 07:28
Show Gist options
  • Save hktonylee/4bd096b6fcbf37591618d8014d8ff59b to your computer and use it in GitHub Desktop.
Save hktonylee/4bd096b6fcbf37591618d8014d8ff59b to your computer and use it in GitHub Desktop.
parse_standard_chartered_csv.py
#!/usr/bin/env python3
import os
import re
import argparse
ACCOUNT_FILE_NAME_EX = re.compile(r'AccountTransactions\d+.csv')
CARD_FILE_NAME_EX = re.compile(r'CardTransactions\d+.csv')
PAYMENT_LINE_EX = re.compile(r'^\d{2}/\d{2}/\d{4},PAYMENT - THANK YOU,')
TRANSACTION_LINE_EX = re.compile(r'^\t\t\t\t\t\t\t\t\t\t\t')
def replace_delimiter(line):
line = line.replace('","', '\t')
line = line.replace(',"', '\t')
line = line.replace('",', '\t')
return line
def parse_account_line(line):
date = line[:10]
rest = line[10:]
new_line = '%s/%s/%s%s' % (date[6:10], date[3:5], date[0:2], rest)
new_line = replace_delimiter(new_line)
new_line = new_line.rstrip('"')
return new_line
def parse_card_amount(line):
def split_currency(item):
return (item[:3], item[4:])
def parse_number(item):
if item.endswith(' CR'):
return (*split_currency(item.rstrip(' CR')), '')
else:
c = split_currency(item.rstrip(' DR'))
return (c[0], '', c[1])
amount = replace_delimiter(line.rstrip('"'))
return '\t'.join([
'\t'.join(parse_number(item))
for item in amount.split('\t')
])
def parse_card_line(line):
date = line[:10]
date_str = date[6:10], date[3:5], date[0:2]
if PAYMENT_LINE_EX.search(line) == None:
new_line = '%s/%s/%s\t%s\t%s' % (*date_str, line[11:51], parse_card_amount(line[52:]))
else: # payment line
new_line = '%s/%s/%s\t%s\t%s' % (*date_str, 'PAYMENT - THANK YOU', parse_card_amount(line[31:]))
return new_line
def parse_account_file(file_name, line_parser):
result = []
for line in open(file_name, 'r'):
if TRANSACTION_LINE_EX.search(line) != None:
result.append(line_parser(line.strip()))
return result
def parse_files(file_name_ex, line_parser):
result = []
for f in os.listdir(os.path.curdir):
if file_name_ex.match(f):
result.extend(parse_account_file(f, line_parser))
result.sort()
# print('Date,Description,Currency,Deposit,Withdrawal,Current Balance,HKD Equivalent')
for r in result:
print(r)
def main():
parser = argparse.ArgumentParser()
parser.add_argument('--type', '-t', required=True, choices=('account', 'accounts', 'card'))
result = parser.parse_args()
if result.type.startswith('account'):
parse_files(ACCOUNT_FILE_NAME_EX, parse_account_line)
elif result.type == 'card':
parse_files(CARD_FILE_NAME_EX, parse_card_line)
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment