Closed KayTaneJupiter closed 2 years ago
I see issues with Paypal CSV files, they have price conversion and other useless data... I managed to do some mappings, but had to preprocess the file before :(
(this is ruby for paypal configured in English, but with everything paid in euros)
require 'csv'
csv_src = CSV.read("paypal.csv", :quote_char => '"', liberal_parsing: true, headers: true, header_converters: :symbol)
currency = "EUR"
disallowed_types = ["General Authorization", "Void of Authorization", "General Credit Card Deposit"]
conversion = { 'USD' => 0.85, 'CAD' => 0.69}
named_other_currency_row_buffer = []
out = []
csv_src.each do |row|
if disallowed_types.include?(row[4])
next
end
row[:amount].sub!(/,/,".")
row[:balance].sub!(/,/,".")
if(!named_other_currency_row_buffer.empty?)
if row[:name] == '' and row[:currency] == currency
named_other_currency_row_buffer[:currency] = row[:currency]
named_other_currency_row_buffer[:amount] = row[:amount]
named_other_currency_row_buffer[:balance] = row[:balance]
out << named_other_currency_row_buffer
named_other_currency_row_buffer = []
next
end
end
if row[:currency] != currency and row[:name] != ''
if(!named_other_currency_row_buffer.empty?)
puts "did not find conversion for : " + named_other_currency_row_buffer[:name] + " for " + named_other_currency_row_buffer[:amount] + named_other_currency_row_buffer[:currency]
if(conversion[row[:currency]] != '')
named_other_currency_row_buffer[:amount] = '%.2f' % (named_other_currency_row_buffer[:amount].to_f * conversion[row[:currency]])
named_other_currency_row_buffer[:balance] = '%.2f' % (named_other_currency_row_buffer[:balance].to_f * conversion[row[:currency]])
named_other_currency_row_buffer[:currency] = currency
puts "use arbitrary conversion of " + conversion[row[:currency]].to_s + " to " + named_other_currency_row_buffer[:amount].to_s
end
out << named_other_currency_row_buffer
end
named_other_currency_row_buffer = row
next
end
if row[:name] != ''
out << row
next
end
end
CSV.open("pp_cleaned.csv", "w") do |csv|
csv << csv_src.headers
out.each do |to_w|
csv << to_w
end
end
Mapping can be :
from __future__ import (
absolute_import, division, print_function, unicode_literals)
from operator import itemgetter
import json
import hashlib
def find_type(transaction):
amount = float(transaction.get('amount'))
return 'credit' if amount > 0 else 'debit'
def gen_transaction_id(transaction):
hasher = hashlib.sha256()
stringified = json.dumps(transaction).encode('utf-8')
hasher.update(stringified)
return hasher.hexdigest()
mapping = {
'has_header': True,
'is_split': False,
'bank': 'Paypal',
'currency': 'EUR',
'delimiter': ',',
'account': 'Paypal checkings',
'type': find_type,
'date': itemgetter('date'),
'amount': itemgetter('amount'),
'payee': itemgetter('name'),
'notes': itemgetter('receipt_id'),
# 'desc': itemgetter('Payment reference'),
'id': gen_transaction_id,
# 'check_num': itemgetter('Field'),
'balance' : itemgetter('balance')
}
Thanks @Tsht, feel free to submit a PR with this new mapping.
I don't see an obvious "mapping" for Paypal. Am I missing something? Surely that's the #1 conversion problem most people want. If it has to be done manually, how does the custom mapping file work? Docs say "New mappings must be placed in the csv2ofx/mappings folder" but it's not clear where this file path is supposed to be. From the same working directory as the .CSV file to be converted?