CSV Import function

[lo97]

Hi, I love the project but it would be very useful it there would be an import (and, in the future export) function, either from csv or excel. This could be useful both for accounts, currencies etc. and for transactions

[eitchtee]

Yes! That's definitely something I want to do.

Do you have any examples of what you want to import? No need for the actual data of course, just the headers and if possible some anonymized data.

I've been thinking about how to implement this, would be nice to know how this data is usually presented.

[crazybob1215]

I'd really like to see this feature too! Here's a (sanitized) example of the export my bank provides when I ask for a csv.

Account Name : Checking
Account Number : 11111111111
Date Range : 01/01/2025-01/15/2025
Transaction Number	Date	Description	Memo	Amount Debit	Amount Credit	Balance	Check Number
123456	1/10/2025	Withdrawal ATM	Arbitrary note about what this money is doing	-50		850
121212	1/2/2025	Withdrawal Electric Bill	I use too much electricity for self-hosting	-100		900
96024	1/1/2025	Deposit Paycheck	Paycheck for work I definitely did		1000	1000

[andremohrmann]

This would be a great feature and is a must have, for me personally, since I'm not going to add all the transactions manually each month.
My .csv export looks like this:

Timestamp (UTC)	Transaction Description	Amount	To Currency	To Amount	Native Currency	Native Amount	Native Amount (in USD)	Transaction Kind	Transaction Hash
30/12/2024 15:17	Big purchase	-3,38			EUR	-3,38	-3,531508855

[eitchtee]

Work has officially started on this. More contributions, via code or your csv file are always welcome.

Due to my job, it will be slow on weekdays, but hopefully I can pick it up on weekends.

Currently the plan is:

A one-size-fits-all solution seems impossible, so I'm taking a page out of of FireflyIII and developing customizable profiles you can configure according to your own CSV; a way to share these configurations, and maybe add them as presets for everyone to use, is also planned.

For an idea of how this might work, take a look at the Rules section on the app, but instead of actions you will add headers and how they should map to WYGIWYH.

[madiele]

This are the csv headers exported by the default export of firefly (though it's possible to export more stuff by using the command line)

 user_id,group_id,journal_id,created_at,updated_at,group_title,type,amount,foreign_amount,currency_code,foreign_currency_code,description,date,source_name,source_iban,source_type,destination_name,destination_iban,destination_type,reconciled,category,budget,bill,tags,notes,sepa_cc,sepa_ct_op,sepa_ct_id,sepa_db,sepa_country,sepa_ep,sepa_ci,sepa_batch_id,external_url,interest_date,book_date,process_date,due_date,payment_date,invoice_date,recurrence_id,internal_reference,bunq_payment_id,import_hash,import_hash_v2,external_id,original_source,recurrence_total,recurrence_count,recurrence_date

[eitchtee]

Version 0.7.0 brings the beta version of Import and a bunch of other changes.

Please check the release notes for what you need to do to upgrade and use this function: https://github.com/eitchtee/WYGIWYH/releases/tag/0.7.0 and https://github.com/eitchtee/WYGIWYH/wiki/Import for documentation (could be improved)

Unfortunately this was a more complex task than I predicted, which led to a less interactive interface.

Let me know what you think, folks. Feedback is always welcome.

[crazybob1215]

@eitchtee I'm testing the import function now, and I'm running into an issue. Instead of using one field for transaction amounts, my bank lists the amount as either "Amount Debit" or "Amount Credit". I figure I can use the current import scheme by doing this:

   amount:
    target: amount
    required: true
    transformations:
      - type: merge
        fields: "Amount Debit" "Amount Credit"
        separator: ""

  type:
    source: amount
    target: type
    detection_method: sign

The problem is that when I try to create the import profile, it throws this error:

Invalid YAML Configuration: while parsing a block mapping in "", line 37, column 9: - type: merge ^ expected , but found '' in "", line 38, column 32: fields: "Amount Debit" "Amount Credit" ^

I've tried adding a comma as a separator, removing the space, both at the same time... It doesn't matter, I still get the same error. What is the proper formatting for listing the fields to merge?

[eitchtee]

@crazybob1215 you have to use a list:

- type: merge
  fields:
    - "Amount Debit"
    - "Amount Credit"
  separator: ""

or

- type: merge
  fields: ["Amount Debit", "Amount Credit"]
  separator: ""

This is poorly documented on the wiki, I will update it.

Let me know how it goes.

[eitchtee]

@crazybob1215 it should look something like this in the end:

settings:
  file_type: csv
  delimiter: ","
  encoding: utf-8
  skip_lines: 2
  importing: transactions
  trigger_transaction_rules: true
  skip_errors: true

mapping:
  account:
    target: account
    default: <YOUR ACCOUNT NAME>
    type: name

  date:
    target: date
    source: Date
    format: "%m/%d/%Y"

  amount:
    target: amount
    transformations:
      - type: merge
        fields: ["Amount Debit", "Amount Credit"]
        separator: ""

  description:
    target: description
    source: Description

  type:
    target: type
    detection_method: sign
    transformations:
      - type: merge
        fields: ["Amount Debit", "Amount Credit"]
        separator: ""

  notes:
    target: notes
    source: Memo

  internal_id:
    target: internal_id
    source: Transaction Number

  is_paid:
    target: is_paid
    detection_method: always_paid
    
deduplication:
  - type: compare
    fields:
      - internal_id
    match_type: strict

[crazybob1215]

Perfect, that cleared the error. I briefly ran into a permissions issue for the temp folder, but I'm past that now. I'm now seeing an error:

wygiwyh_procrastinate | apps.transactions.models.Transaction.account.RelatedObjectDoesNotExist: Transaction has no account.

In the export from my bank, the account name is only listed once in the header, not on each line. I thought I had this covered by not setting a "source" for the account mapping. Here's what my full import profile looks like, and an example line from the csv:

settings:
  file_type: csv
  delimiter: ","
  encoding: utf-8
  skip_lines: 3
  importing: transactions
  trigger_transaction_rules: true
  skip_errors: true

mapping:
  account:
    target: account
    default: BANK - Checking
    type: name

  internal_id:
    target: internal_id
    source: "Transaction Number"

  date:
    target: date
    source: Date
    format: "%m/%d/%Y"

  description:
    target: description
    source: Description

  notes:
    target: notes
    source: Memo

  amount:
    target: amount
    required: true
    transformations:
      - type: merge
        fields: ["Amount Debit", "Amount Credit"]
        separator: ""

  type:
    source: amount
    target: type
    detection_method: sign

deduplication:
  - type: compare
    fields:
      - internal_id
    match_type: strict

Transaction Number,Date,Description,Memo,Amount Debit,Amount Credit,Balance,Check Number,Fees  
"12345",01/27/2025,"Withdrawal to Foo","Credit card payment",-500.00,,"1000.00",,

[eitchtee]

@crazybob1215 your YAML looks pretty clean to me. You're right on you assumption, if no source is provided, it will default to default, if there isn't a default, it will be ignored.

The error you mentioned happens when an account with the provided name or id can't be found, are you sure "BANK - Checking" (or the original value you're using) exists on your instance? Try quoting it just in case.

Also you might want to change you type mapping to:

type:
    target: type
    detection_method: sign
    transformations:
      - type: merge
        fields: ["Amount Debit", "Amount Credit"]
        separator: ""

Currently you can't source a mapped value, just CSV headers.

[crazybob1215]

Ah, I thought I was targeting the account name correctly, but it is actually just "Checking" and not "BANK - Checking".

As for the type mapping in your example, shouldn't the target be amount and not type? If I set it to type, I get what appears to be a format handling error. When set to amount it runs successfully.

Now that it's successfully imported, I'm seeing two new errors.

1. From the Overview (monthly) page, if I change the Order by to Newest first I can't see any of the imported transactions. I can see them with it set to Default or Oldest first.
2. It's importing all of the transactions as expenses. The values listed in the csv under "Amount Debit" are negative and "Amount Credit" are positive, so I would expect the detection method: sign to correctly identify the transactions but apparently something isn't correct there.

[eitchtee]

Glad it worked.

As for the type mapping in your example, shouldn't the target be amount and not type? If I set it to type, I get what appears to be a format handling error. When set to amount it runs successfully.

2. It's importing all of the transactions as expenses. The values listed in the csv under "Amount Debit" are negative and "Amount Credit" are positive, so I would expect the detection method: sign to correctly identify the transactions but apparently something isn't correct there.

You need to have two different mappings:

  amount:
    target: amount
    transformations:
      - type: merge
        fields: ["Amount Debit", "Amount Credit"]
        separator: ""

and

  type:
    target: type
    detection_method: sign
    transformations:
      - type: merge
        fields: ["Amount Debit", "Amount Credit"]
        separator: ""

The first represents the transaction amount, internally WYGIWYH stores all amounts as positive values. The second, represents the type of the transaction, if it's Income or Expense.

In the YAML you shared, type is sourcing from "amount", which isn't a header in your csv, so it will be ignored silently and all your imported transactions will be added with the database default of Expense.

My suggestion is to have both amount and type with the same transformation, this way type can be inferred by sign, i.e. if it's a negative value, it will be an Expense, if it's a positive value, it will be treated as Income.

This definitely could be improved.

---

1. From the Overview (monthly) page, if I change the Order by to Newest first I can't see any of the imported transactions. I can see them with it set to Default or Oldest first.

What we use for caching database queries is not playing nice with our task scheduler, I'm aware and looking for a fix, you can click the Yellow Bust icon on the navbar and select Clear cache, everything should show up after this.

[eitchtee]

Also, if you enabled SOFT_DELETE, you might want to go to Management > Django Admin > Transactions and Hard Delete the transactions you imported so you can re-import them with the correct type.

Select them all and choose the action shown below:

If you didn't enable it, just deleting the wrong transactions should be enough to re-import.

[crazybob1215]

Adding both the amount and type mappings and using the Clear Cache button seems to have fixed both of the issues. Thanks for all the help with getting my head wrapped around this!

[eitchtee]

No problem at all @crazybob1215 , this actually gave me some ideas on how to improve the config, stay tuned.

[eitchtee]

@crazybob1215 you might want to upgrade to 0.8.0 and use a single container setup, make sure to read the release notes to know more about it. It should fix the caching problem.

[crazybob1215]

@eitchtee Upgraded to 0.8.0. Tried a couple imports and that seems to be working fine, also the caching seems to be working as well. I noticed that it is remembering my Order by setting as I change pages now too, which is pretty handy.

[lucius100]

Seems a lot of manual setup, I thought just drop csv and we are good to go.
Usually something like pre-made example template, and we download it , then just fill in our data, and import to it, but we need to setup yaml,etc.

This app seems like have all the feature I need, just need to play around to know it better, really appreciate all the things u do mate, thanks.

[eitchtee]

Seems a lot of manual setup, I thought just drop csv and we are good to go. Usually something like pre-made example template, and we download it , then just fill in our data, and import to it, but we need to setup yaml,etc.

This app seems like have all the feature I need, just need to play around to know it better, really appreciate all the things u do mate, thanks.

@lucius100 unfortunately CSVs are universal, but not standardized, so there's some complexity that comes with that. We do have a presets feature to try to get away with some of these complexities, but it's lacking on presets right now, hopefully the community can pick up on it and share the configuration for their banks and apps. If you need help with your YAML config you can open an issue, just share how your data is presented and I will be happy to help.

Hope you can try it out and like it.

[lucius100]

Yes, I like it pretty much, just still figuring out the data for importing

  type:
    source: amount
    target: type
    detection_method: sign

  type:
    source: "Valor"
    target: "type"
    detection_method: sign

what are these for ?

I am in the middle of setup for my data import, just a bit confused about the part for own transfer , it would be something like transfer from acc A to acc B, and I have separate credit with debit table, both positive balance, so merge format not seems work

settings:
  file_type: csv
  delimiter: ","
  encoding: utf-8
  skip_lines: 0
  importing: transactions
  trigger_transaction_rules: true
  skip_errors: true

mapping:
  account:
    target: account
    default: "MYR Wallet"
    type: name

  date:
    target: date
    source: Date
    format: "%d/%m/%Y"

  amount:
    target: amount
    source: Valor

  description:
    target: description
    source: Description

  type:
    source: "Valor"
    target: "type"
    detection_method: sign

  notes:
    target: notes
    source: Notes

  is_paid:
    target: is_paid
    detection_method: always_paid

deduplicate:
  - type: compare
    fields:
      - internal_id
    match_type: strict