Skip to content

Instantly share code, notes, and snippets.

@jubstuff
Created December 27, 2017 14:20
Show Gist options
  • Save jubstuff/372876394a659bb86a64b595c8b15d37 to your computer and use it in GitHub Desktop.
Save jubstuff/372876394a659bb86a64b595c8b15d37 to your computer and use it in GitHub Desktop.
Format ING direct exported XLS to YNAB compliant CSV
#!/bin/bash
if [ $# -eq 0 ]
then
echo "Usage: $0 filename.xls (where the xls is exported from ING direct website)"
exit 1;
fi
result=$(echo $1 | cut -d . -f1);
result+=".csv";
export HOME=/tmp && libreoffice --headless --convert-to csv $1 --outdir . # convert to csv
sed -i '/,,,,/q' $result # remove everything after ,,,,
sed -i '1d' $result # remove first line
sed -i '$d' $result # remove last line
sed -i 's/\x80//g' $result # remove € symbol
awk 'BEGIN { FS="\""; } { gsub(",", ".", $2); gsub("-", "", $2); print }' $result > temp.csv # replace , with . in amounts
mv temp.csv $result
# format final csv as needed by YNAB
awk 'BEGIN { FS=","; print "Date,Payee,Memo,Outflow,Inflow" } { if( $5 ~ "-") {print $2, ",", $4, ",", $3, ",", $5} else {print $2, ",", $4, ",", $3, ",", "," $5} }' $result > temp.csv
mv temp.csv $result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment