Skip to content

Instantly share code, notes, and snippets.

@AlexAegis
Last active July 30, 2022 11:39
Show Gist options
  • Save AlexAegis/68cf84117e89c83214c6dd866eb3ca2a to your computer and use it in GitHub Desktop.
Save AlexAegis/68cf84117e89c83214c6dd866eb3ca2a to your computer and use it in GitHub Desktop.
A script to convert a 1Money CSV export into Wallet imports.
#!/bin/sh
# Converts a 1Money export to be used at Wallet
# Converts one Account at a time, places all found accounts into a folder
# called output
# Usage:
# ./convert_1money_to_wallet.sh 1moneyexport.csv
# Import settings:
# Go to https://web.budgetbakers.com/imports
# 1. page
# - Has Header row: true
# - Header row: 1
# - Last row: (Well, last row)
# 2. page
# - Incomes and Expenses are in two columns: true
# - Amount: "AMOUNT 2"
# - I have a separate fees/charges column: false
# - Fee: "TYPE"
# 3. page
# - Date: "DATE"
# 4. page
# - Note: "NOTES"
# - Payee: "FROM ACCOUNT"
# - Currency: "CURRENCY 2"
# - Category: "TO ACCOUNT / TO CATEGORY"
file="$1"
filename="${1%.*}"
if [ ! -e "$file" ]; then
echo "Add a valid file to convert";
exit 1
fi
collect_accounts() {
awk '
BEGIN {
FS="(\"?,\"?)|(^\")|(\"$)"
OFS="\",\""
}
NR > 1 && $2 != "NAME" && $4 && !$5 {
# NAME
printf "%s|", $2
# CURRENCY
printf "%s\n", $4
}' "$1"
}
convert_account() {
# Column mapping
# $1 Ends up being blank because of this FS
# $2 "DATE" (Has to be converted)
# $3 "TYPE" (Expense/Income/Transfer)
# $4 "FROM ACCOUNT"
# $5 "TO ACCOUNT / TO CATEGORY"
# $6 "AMOUNT"
# $7 "CURRENCY"
# $8 "AMOUNT 2"
# $9 "CURRENCY 2"
# $10 "TAGS" Not supported by Wallet (can put into note)
# $11 "NOTES"
awk -v account="$1" '
BEGIN {
FS="(\"?,\"?)|(^\")|(\"$)"
OFS="\",\""
}
NR == 1 {
printf "\"DATE\",\"CATEGORY\",\"EXPENSE AMOUNT\",\"INCOME AMOUNT\",\"CURRENCY\",\"NOTE\"\n"
}
NR > 1 && $4 == account && $9 {
split($2,date,"/")
# DATE
printf "\"20%02d-%02d-%02d\",", date[3], date[1], date[2]
# CATEGORY
printf "\"%s\",", $5
if ($3 == "Expense") {
# EXPENSE AMOUNT
printf "\"%s\",", $8
# INCOME AMOUNT
printf "\"%s\",", ""
} else if ($3 == "Income") {
# EXPENSE AMOUNT
printf "\"%s\",", ""
# INCOME AMOUNT
printf "\"%s\",", $8
} else {
# Transfers (! TRANSFERS CANT BE IMPORTED! MANUAL EDIT IS NEEDED!)
if ($5 == account) {
# If to account is this account, then its an income
# EXPENSE AMOUNT
printf "\"%s\",", ""
# INCOME AMOUNT
printf "\"%s\",", $8
} else {
# If to account is something else, then its an expense
# EXPENSE AMOUNT
printf "\"%s\",", $8
# INCOME AMOUNT
printf "\"%s\",", ""
}
}
# CURRENCY
printf "\"%s\",", $9
# NOTE
printf "\"%s", $11, $10
if ($10) {
printf " [%s]", $10
}
if ($3 == "Transfer") {
printf " [TRANSFER TO (%s)]", $5
}
printf "\"\n"
}' "$2"
}
accounts=$(collect_accounts "$file")
mkdir -p output
IFS='
'
for account_currency in $accounts; do
account="${account_currency%|*}"
currency="${account_currency#*|}"
account_with_underscore=$(echo "$account" | tr ' ' '_')
echo "Converting account \"$account\" currency \"$currency\""
convert_account "$account" "$file" > "output/$filename-$account_with_underscore.csv"
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment