split-pro
split-pro copied to clipboard
oss-apps
How can I import my full spending history from Splitwise?
Hi and thanks for your work! I’m in the process of onboarding to SplitPro and just exported a dataset from Splitwise to convert my Tricount data to that JSON format. However, I just realized that only balances were exported, not the individual transactions.
Is there a way to import the full transaction history? My girlfriend and I would love to keep all the history—and all the little jokes we wrote while spending our money! 😊
Update: I opened the database, but my SQL skills are way too rusty to write a tool for importing an expense dataset into PostgreSQL. Can someone help me with this?
Well, I coded a python script to import data from a splitwise dataset, the example works with two users:
Date,Description,Catégorie,Cost,Devise,user1,user2
2025-03-11,Company,General,"7,2",EUR,"3,60","-3,60"
,,,,,"3,60","--3,60"
It looks like this, if you wanna use it to inspire yourself:
import csv # Module to handle CSV file reading and writing
import random # Module for generating random numbers and selections
import string # Module providing string constants (e.g., lowercase letters)
import sys # Module for accessing command-line arguments
from decimal import Decimal, InvalidOperation # For precise decimal arithmetic and error handling during conversions
from datetime import datetime # To obtain the current date and time
def generate_id():
"""
Generates an identifier that matches the regex pattern:
^cm84[a-z]{5}000[a-z0-9]zmcd[a-z0-9]{8}$
"""
part1 = "cm84" # Fixed prefix
part2 = ''.join(random.choices(string.ascii_lowercase, k=5)) # 5 random lowercase letters
part3 = "000" # Fixed sequence "000"
part4 = random.choice(string.ascii_lowercase + string.digits) # A single random alphanumeric character
part5 = "zmcd" # Fixed string "zmcd"
part6 = ''.join(random.choices(string.ascii_lowercase + string.digits, k=8)) # 8 random alphanumeric characters
return part1 + part2 + part3 + part4 + part5 + part6 # Concatenate all parts to form the final identifier
def escape_string(s):
"""
Escapes single quotes in SQL strings by doubling them.
This is used to prevent SQL injection or errors in SQL queries.
"""
return s.replace("'", "''")
def main(csv_file, sql_file):
# Open the input CSV file for reading and the output SQL file for writing (both with UTF-8 encoding)
with open(csv_file, newline='', encoding='utf-8') as f_in, \
open(sql_file, 'w', encoding='utf-8') as f_out:
reader = csv.DictReader(f_in) # Create a CSV reader that returns each row as a dictionary keyed by column names
rows = list(reader) # Convert the reader to a list of rows
if not rows:
return # Exit if the CSV file is empty
# Process all rows except the last one normally
for row in rows[:-1]:
# Generate a unique identifier for the Expense record
expense_id = generate_id()
# Convert the "Cost" field to a number (multiplied by 100 to represent cents)
cost_str = row.get('Cost', '0').replace(',', '.') # Replace commas with dots for decimal conversion
try:
cost_decimal = Decimal(cost_str) * Decimal('100') # Multiply by 100 to convert to cents
cost = int(cost_decimal)
except InvalidOperation:
cost = 0 # Default to 0 if conversion fails
# Convert the "user1" field (for user1) to an integer (in cents)
user1_str = row.get('user1', '0').replace(',', '.')
try:
user1_value = Decimal(user1_str)
except InvalidOperation:
user1_value = Decimal(0)
user1_value = int(user1_value * Decimal('100'))
# Convert the field for user2 to an integer (in cents)
user2_str = row.get('user2', '0').replace(',', '.')
try:
user2_value = Decimal(user2_str)
except InvalidOperation:
user2_value = Decimal(0)
user2_value = int(user2_value * Decimal('100'))
# Determine the payer and adder:
# If user1 has a positive amount, then he is considered the payer (user 1), otherwise user 2 is the payer.
if user1_value > 0:
paidBy = 1
else:
paidBy = 2
addedBy = paidBy # The 'addedBy' field is set to the same value as 'paidBy'
# Escape any single quotes in the "Description" field to avoid SQL issues
description = escape_string(row.get('Description', ''))
expense_date = row.get('Date', '') # Retrieve the expense date from the CSV row
# Construct the SQL INSERT statement for the "Expense" table
sql_expense = (
'INSERT INTO "Expense" ("id", "paidBy", "addedBy", "name", "amount", "category", "splitType", "expenseDate", "createdAt", "updatedAt", "currency", "fileKey", "groupId", "deletedAt", "deletedBy", "updatedBy") '
f'VALUES (\'{expense_id}\', {paidBy}, {addedBy}, \'{description}\', {cost}, \'general\', \'EQUAL\', \'{expense_date}\', \'{expense_date}\', \'{expense_date}\', \'EUR\', NULL, 1, NULL, NULL, NULL);\n'
)
f_out.write(sql_expense) # Write the expense SQL command to the output file
# Construct the SQL INSERT statement for the "ExpenseParticipant" table for user1
sql_participant_1 = (
'INSERT INTO "ExpenseParticipant" ("expenseId", "userId", "amount") '
f'VALUES (\'{expense_id}\', 1, {user1_value});\n'
)
f_out.write(sql_participant_1) # Write the participant SQL command for user1
# Construct the SQL INSERT statement for the "ExpenseParticipant" table for user2
sql_participant_2 = (
'INSERT INTO "ExpenseParticipant" ("expenseId", "userId", "amount") '
f'VALUES (\'{expense_id}\', 2, {user2_value});\n'
)
f_out.write(sql_participant_2) # Write the participant SQL command for user2
# Process the last row in the CSV file, which is used to update the "Balance" table
last_row = rows[-1]
balance_str = last_row.get('user1', '0').replace(',', '.')
try:
balance_decimal = Decimal(balance_str) * Decimal('100') # Convert the balance value to cents
balance_value = int(balance_decimal)
except InvalidOperation:
balance_value = 0
# Obtain the current date and time in the desired format ("YYYY-MM-DD HH:MM:SS")
current_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
# Construct the SQL INSERT statement for the "Balance" table for the first entry (user1 owes user2)
sql_balance_1 = (
'INSERT INTO "Balance" ("userId", "currency", "friendId", "amount", "createdAt", "updatedAt", "importedFromSplitwise") '
f'VALUES (1, \'EUR\', 2, {balance_value}, \'{current_date}\', \'{current_date}\', true);\n'
)
f_out.write(sql_balance_1) # Write the first balance SQL command
# Construct the SQL INSERT statement for the "Balance" table for the second entry (user2 owes user1)
sql_balance_2 = (
'INSERT INTO "Balance" ("userId", "currency", "friendId", "amount", "createdAt", "updatedAt", "importedFromSplitwise") '
f'VALUES (2, \'EUR\', 1, {balance_value}, \'{current_date}\', \'{current_date}\', true);\n'
)
f_out.write(sql_balance_2) # Write the second balance SQL command
if __name__ == '__main__':
# Check that the correct number of command-line arguments are provided
if len(sys.argv) < 3:
print("Usage: python script.py input.csv import.sql")
else:
# Run the main function with the provided CSV input and SQL output file paths
main(sys.argv[1], sys.argv[2])
and it works like this: python import_splitwise.py splitwise.csv import.sql
I can download and import the JSON, but it doesn't import the transaction history. I can manually download the spreadsheet from splitwise, but how do I import it to this app?
