CSV Import function #3

Closed
opened 2025-12-28 23:23:33 +01:00 by adam · 32 comments
Owner

Originally created by @lo97 on GitHub (Jan 15, 2025).

Originally assigned to: @eitchtee on GitHub.

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

Originally created by @lo97 on GitHub (Jan 15, 2025). Originally assigned to: @eitchtee on GitHub. 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
adam added the enhancement label 2025-12-28 23:23:33 +01:00
adam closed this issue 2025-12-28 23:23:33 +01:00
Author
Owner

@eitchtee commented on GitHub (Jan 16, 2025):

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.

@eitchtee commented on GitHub (Jan 16, 2025): 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.
Author
Owner

@crazybob1215 commented on GitHub (Jan 16, 2025):

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.

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head> </head>
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  
</html>
@crazybob1215 commented on GitHub (Jan 16, 2025): 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. <html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head> <meta name=ProgId content=Excel.Sheet> <meta name=Generator content="Microsoft Excel 15"> <link id=Main-File rel=Main-File href="file:///C:/Users/Aaron/AppData/Local/Temp/msohtmlclip1/01/clip.htm"> <link rel=File-List href="file:///C:/Users/Aaron/AppData/Local/Temp/msohtmlclip1/01/clip_filelist.xml"> </head> <body link="#0563C1" vlink="#954F72"> 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 | Fees 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 |   |   </body> </html>
Author
Owner

@andremohrmann commented on GitHub (Jan 16, 2025):

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
@andremohrmann commented on GitHub (Jan 16, 2025): 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
Author
Owner

@eitchtee commented on GitHub (Jan 16, 2025):

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.

@eitchtee commented on GitHub (Jan 16, 2025): 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.
Author
Owner

@Inrego commented on GitHub (Jan 17, 2025):

Have you given thought to automatically import bank transactions through api's?

@Inrego commented on GitHub (Jan 17, 2025): Have you given thought to automatically import bank transactions through api's?
Author
Owner

@IZIme07 commented on GitHub (Jan 17, 2025):

Have you given thought to automatically import bank transactions through api's?

You can make it yourself, try to use N8N or similar

@IZIme07 commented on GitHub (Jan 17, 2025): > Have you given thought to automatically import bank transactions through api's? You can make it yourself, try to use N8N or similar
Author
Owner

@madiele commented on GitHub (Jan 20, 2025):

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

@madiele commented on GitHub (Jan 20, 2025): 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 ```
Author
Owner

@madiele commented on GitHub (Jan 20, 2025):

I would also add that while writing an import script the biggest hurdle I had is that a reference Id is missing from the API: basically an ID that I can give that uniquely identity an imported transaction, if I run the import and give the same import id the import shoud update the old record or ignore it and give an error (for the API I prefer error, if I can get a transaction by reference Id via API I could write the update manually then)

This way it's possible to retry the import many times without accidentally duplicating fields, so for a firefly import I could use the journal ID for this scope

@madiele commented on GitHub (Jan 20, 2025): I would also add that while writing an import script the biggest hurdle I had is that a reference Id is missing from the API: basically an ID that I can give that uniquely identity an imported transaction, if I run the import and give the same import id the import shoud update the old record or ignore it and give an error (for the API I prefer error, if I can get a transaction by reference Id via API I could write the update manually then) This way it's possible to retry the import many times without accidentally duplicating fields, so for a firefly import I could use the journal ID for this scope
Author
Owner

@Inrego commented on GitHub (Jan 20, 2025):

I don't know which API you're referencing. But I think most api's would have a transaction ID. I know that GoCardless does

@Inrego commented on GitHub (Jan 20, 2025): I don't know which API you're referencing. But I think most api's would have a transaction ID. I know that GoCardless does
Author
Owner

@madiele commented on GitHub (Jan 20, 2025):

I was mentioning WYGIWYH current API, since I developed a firefly import script already and played with it already

https://github.com/eitchtee/WYGIWYH/discussions/49

@madiele commented on GitHub (Jan 20, 2025): I was mentioning WYGIWYH current API, since I developed a firefly import script already and played with it already https://github.com/eitchtee/WYGIWYH/discussions/49
Author
Owner

@eitchtee commented on GitHub (Jan 20, 2025):

I would also add that while writing an import script the biggest hurdle I had is that a reference Id is missing from the API: basically an ID that I can give that uniquely identity an imported transaction, if I run the import and give the same import id the import shoud update the old record or ignore it and give an error (for the API I prefer error, if I can get a transaction by reference Id via API I could write the update manually then)

This way it's possible to retry the import many times without accidentally duplicating fields, so for a firefly import I could use the journal ID for this scope

That's planned. I'm adding a external_note and external_id fields, that are only accessible via imports and the API, the last is unique meaning it would error out if you try to set two transactions with the same one.

@eitchtee commented on GitHub (Jan 20, 2025): > I would also add that while writing an import script the biggest hurdle I had is that a reference Id is missing from the API: basically an ID that I can give that uniquely identity an imported transaction, if I run the import and give the same import id the import shoud update the old record or ignore it and give an error (for the API I prefer error, if I can get a transaction by reference Id via API I could write the update manually then) > > This way it's possible to retry the import many times without accidentally duplicating fields, so for a firefly import I could use the journal ID for this scope That's planned. I'm adding a external_note and external_id fields, that are only accessible via imports and the API, the last is unique meaning it would error out if you try to set two transactions with the same one.
Author
Owner

@eitchtee commented on GitHub (Jan 24, 2025):

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.

@eitchtee commented on GitHub (Jan 24, 2025): 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.
Author
Owner

@crazybob1215 commented on GitHub (Jan 28, 2025):

@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?

@crazybob1215 commented on GitHub (Jan 28, 2025): @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 "<unicode string>", line 37, column 9: - type: merge ^ expected <block end>, but found '<scalar>' in "<unicode string>", 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?
Author
Owner

@eitchtee commented on GitHub (Jan 28, 2025):

@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 commented on GitHub (Jan 28, 2025): @crazybob1215 you have to use a list: ```yaml - type: merge fields: - "Amount Debit" - "Amount Credit" separator: "" ``` or ```yaml - 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.
Author
Owner

@eitchtee commented on GitHub (Jan 28, 2025):

@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
@eitchtee commented on GitHub (Jan 28, 2025): @crazybob1215 it should look something like this in the end: ```yaml 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 ```
Author
Owner

@crazybob1215 commented on GitHub (Jan 28, 2025):

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",,
@crazybob1215 commented on GitHub (Jan 28, 2025): 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",, ```
Author
Owner

@eitchtee commented on GitHub (Jan 28, 2025):

@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.

@eitchtee commented on GitHub (Jan 28, 2025): @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: ```yaml 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.
Author
Owner

@crazybob1215 commented on GitHub (Jan 28, 2025):

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.
@crazybob1215 commented on GitHub (Jan 28, 2025): 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.
Author
Owner

@eitchtee commented on GitHub (Jan 28, 2025):

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 commented on GitHub (Jan 28, 2025): 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: ```yaml amount: target: amount transformations: - type: merge fields: ["Amount Debit", "Amount Credit"] separator: "" ``` and ```yaml 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.
Author
Owner

@eitchtee commented on GitHub (Jan 28, 2025):

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:

Image

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

@eitchtee commented on GitHub (Jan 28, 2025): 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: ![Image](https://github.com/user-attachments/assets/7ad4e40d-16b4-4ebc-bc53-342647a87498) If you didn't enable it, just deleting the wrong transactions should be enough to re-import.
Author
Owner

@crazybob1215 commented on GitHub (Jan 28, 2025):

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!

@crazybob1215 commented on GitHub (Jan 28, 2025): 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!
Author
Owner

@eitchtee commented on GitHub (Jan 29, 2025):

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

@eitchtee commented on GitHub (Jan 29, 2025): No problem at all @crazybob1215 , this actually gave me some ideas on how to improve the config, stay tuned.
Author
Owner

@eitchtee commented on GitHub (Jan 29, 2025):

@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.

@eitchtee commented on GitHub (Jan 29, 2025): @crazybob1215 you might want to upgrade to [0.8.0](https://github.com/eitchtee/WYGIWYH/releases/tag/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.
Author
Owner

@crazybob1215 commented on GitHub (Jan 29, 2025):

@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.

@crazybob1215 commented on GitHub (Jan 29, 2025): @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.
Author
Owner

@lucius100 commented on GitHub (Jan 31, 2025):

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 commented on GitHub (Jan 31, 2025): 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.
Author
Owner

@eitchtee commented on GitHub (Jan 31, 2025):

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.

@eitchtee commented on GitHub (Jan 31, 2025): > 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.
Author
Owner

@lucius100 commented on GitHub (Jan 31, 2025):

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 ?

Image

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
@lucius100 commented on GitHub (Jan 31, 2025): 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 ? <img width="503" alt="Image" src="https://github.com/user-attachments/assets/faaeb476-20fc-49b8-af76-fc8eabf42a32" /> 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 ```
Author
Owner

@andremohrmann commented on GitHub (Feb 5, 2025):

Not sure if I'm misunderstanding the yaml requirements or something else, but I get this error trying to import my csv:

[2025-02-05 21:52:41] INFO: Starting import process
[2025-02-05 21:52:41] INFO: Skipped 1 initial lines
[2025-02-05 21:52:41] INFO: Starting import with 57 rows
[2025-02-05 21:52:41] ERROR: Fatal error processing row 1: Required field date is missing
[2025-02-05 21:52:41] ERROR: Import failed: Required field date is missing

My yaml configuration:

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


mapping:
  date:
    target: date
    source: Timestamp
    format: "%d/%m/%Y"
  amount:
    target: amount
    source: Amount
    required: true
  description:
    target: description
    source: TransactionDescription
  

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

My transactions csv file:

Timestamp,TransactionDescription,Currency,Amount,To Currency,To Amount,Native Currency,Native Amount,Native Amount (in USD),Transaction Kind,Transaction Hash
30/01/2025,Purchase01,EUR,-20,,,EUR,-20,-20,9214385,,
30/01/2025,Purchase02,EUR,-544,,,EUR,-544,-569,0631272,,
30/01/2025,EUR Deposit,EUR,258,,,EUR,258,269,8865567,,
30/01/2025,Purchase03,EUR,-14,9,,,EUR,-14,9,-15,58647168,,

What am I missing here?

@andremohrmann commented on GitHub (Feb 5, 2025): Not sure if I'm misunderstanding the yaml requirements or something else, but I get this error trying to import my csv: > [2025-02-05 21:52:41] INFO: Starting import process > [2025-02-05 21:52:41] INFO: Skipped 1 initial lines > [2025-02-05 21:52:41] INFO: Starting import with 57 rows > [2025-02-05 21:52:41] ERROR: Fatal error processing row 1: Required field date is missing > [2025-02-05 21:52:41] ERROR: Import failed: Required field date is missing My yaml configuration: ``` settings: file_type: csv delimiter: "," encoding: utf-8 skip_lines: 1 importing: transactions trigger_transaction_rules: true skip_errors: false mapping: date: target: date source: Timestamp format: "%d/%m/%Y" amount: target: amount source: Amount required: true description: target: description source: TransactionDescription deduplication: - type: compare fields: - internal_id match_type: strict ``` My transactions csv file: ``` Timestamp,TransactionDescription,Currency,Amount,To Currency,To Amount,Native Currency,Native Amount,Native Amount (in USD),Transaction Kind,Transaction Hash 30/01/2025,Purchase01,EUR,-20,,,EUR,-20,-20,9214385,, 30/01/2025,Purchase02,EUR,-544,,,EUR,-544,-569,0631272,, 30/01/2025,EUR Deposit,EUR,258,,,EUR,258,269,8865567,, 30/01/2025,Purchase03,EUR,-14,9,,,EUR,-14,9,-15,58647168,, ``` What am I missing here?
Author
Owner

@eitchtee commented on GitHub (Feb 6, 2025):

What am I missing here?

@andremohrmann If the first line of your CSV is the header as in your example, you can set skip_lines to 0. Lines should be skipped only to reach the header in the file. This is probably why date is missing, because it couldn't find the given header.

Also, you're missing a few required fields and optional fields:

  internal_id:
    target: internal_id
    transformations:
        - type: hash
          fields: ["Timestamp", "TransactionDescription", "Amount"]

This will hash the fields and add them to internal_id so you don't re-import then in the future. If Transaction Hash is present on your file but omitted here, you could use that instead, then you won't need the transformations.

type:
    source: Amount
    target: "type"
    detection_method: sign

This will add a income or expense based on the amount sign

account:
    target: account
    source: "Currency"
    type: name
    transformations:
        - type: "replace"
          pattern: "EUR"
          replacement: "<YOUR EUR ACCOUNT NAME>"
          exclusive: true

This should add all transactions with currency EUR to the account you set.

  is_paid:
    target: is_paid
    detection_method: always_paid

This will make all imported transactions paid by default.

That should be it. Let me know how it goes.

Remembering the spec is still in beta, if you need something specific that can't be done currently or find some bug you can open an issue and I will be happy to check it out.

@eitchtee commented on GitHub (Feb 6, 2025): > What am I missing here? @andremohrmann If the first line of your CSV is the header as in your example, you can set `skip_lines` to 0. Lines should be skipped only to reach the header in the file. This is probably why date is missing, because it couldn't find the given header. Also, you're missing a few required fields and optional fields: ```yaml internal_id: target: internal_id transformations: - type: hash fields: ["Timestamp", "TransactionDescription", "Amount"] ``` This will hash the fields and add them to internal_id so you don't re-import then in the future. If `Transaction Hash` is present on your file but omitted here, you could use that instead, then you won't need the transformations. ```yaml type: source: Amount target: "type" detection_method: sign ``` This will add a income or expense based on the amount sign ```yaml account: target: account source: "Currency" type: name transformations: - type: "replace" pattern: "EUR" replacement: "<YOUR EUR ACCOUNT NAME>" exclusive: true ``` This should add all transactions with currency EUR to the account you set. ```yaml is_paid: target: is_paid detection_method: always_paid ``` This will make all imported transactions paid by default. That should be it. Let me know how it goes. Remembering the spec is still in beta, if you need something specific that can't be done currently or find some bug you can open an issue and I will be happy to check it out.
Author
Owner

@andremohrmann commented on GitHub (Feb 6, 2025):

This is my full import yaml now:

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

mapping:
  account:
    target: account
    source: "Currency"
    type: name
    transformations:
        - type: "replace"
          pattern: "EUR"
          replacement: "AM"
          exclusive: true
  amount:
    target: amount
    source: "Amount"
    required: true
  description:
    target: description
    source: "TransactionDescription"
  internal_id:
    target: internal_id
    transformations:
        - type: hash
          fields: ["Timestamp", "TransactionDescription", "Amount"]  
  date:
    target: date
    source: "Timestamp"
    format: "%d/%m/%Y"
  is_paid:
    target: is_paid
    detection_method: always_paid

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

I still get this error:

2025-02-06 21:20:20 2025-02-06 20:20:20,790 INFO    procrastinate.worker Starting job apps.import_app.tasks.process_import[7](file_path='/usr/src/app/temp/card_transactions_record_02052025_221347_TTLEnjQ.csv', import_run_id=4)
2025-02-06 21:20:20 2025-02-06 20:20:20,853 ERROR   procrastinate.worker Job apps.import_app.tasks.process_import[7](file_path='/usr/src/app/temp/card_transactions_record_02052025_221347_TTLEnjQ.csv', import_run_id=4) ended with status: Error, lasted 0.062 s
2025-02-06 21:20:20 Traceback (most recent call last):
2025-02-06 21:20:20   File "/usr/src/app/apps/import_app/services/v1.py", line 615, in process_file
2025-02-06 21:20:20     self._process_csv(file_path)
2025-02-06 21:20:20   File "/usr/src/app/apps/import_app/services/v1.py", line 590, in _process_csv
2025-02-06 21:20:20     self._process_row(row, row_number)
2025-02-06 21:20:20   File "/usr/src/app/apps/import_app/services/v1.py", line 529, in _process_row
2025-02-06 21:20:20     mapped_data = self._map_row(row)
2025-02-06 21:20:20                   ^^^^^^^^^^^^^^^^^^
2025-02-06 21:20:20   File "/usr/src/app/apps/import_app/services/v1.py", line 513, in _map_row
2025-02-06 21:20:20     raise ValueError(f"Required field {field} is missing")
2025-02-06 21:20:20 ValueError: Required field date is missing
2025-02-06 21:20:20 
2025-02-06 21:20:20 During handling of the above exception, another exception occurred:
2025-02-06 21:20:20 
2025-02-06 21:20:20 Traceback (most recent call last):
2025-02-06 21:20:20   File "/usr/local/lib/python3.11/site-packages/procrastinate/worker.py", line 281, in run_job
2025-02-06 21:20:20     task_result = await await_func(*job_args, **job.task_kwargs)
2025-02-06 21:20:20                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2025-02-06 21:20:20   File "/usr/local/lib/python3.11/site-packages/procrastinate/utils.py", line 108, in sync_to_async
2025-02-06 21:20:20     return await sync.sync_to_async(func, thread_sensitive=False)(*args, **kwargs)
2025-02-06 21:20:20            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2025-02-06 21:20:20   File "/usr/local/lib/python3.11/site-packages/asgiref/sync.py", line 468, in __call__
2025-02-06 21:20:20     ret = await asyncio.shield(exec_coro)
2025-02-06 21:20:20           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2025-02-06 21:20:20   File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run
2025-02-06 21:20:20     result = self.fn(*self.args, **self.kwargs)
2025-02-06 21:20:20              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2025-02-06 21:20:20   File "/usr/local/lib/python3.11/site-packages/asgiref/sync.py", line 522, in thread_handler
2025-02-06 21:20:20     return func(*args, **kwargs)
2025-02-06 21:20:20            ^^^^^^^^^^^^^^^^^^^^^
2025-02-06 21:20:20   File "/usr/src/app/apps/import_app/tasks.py", line 17, in process_import
2025-02-06 21:20:20     import_service.process_file(file_path)
2025-02-06 21:20:20   File "/usr/src/app/apps/import_app/services/v1.py", line 629, in process_file
2025-02-06 21:20:20     raise Exception("Import failed")
2025-02-06 21:20:20 Exception: Import failed

I even tried setting up a fresh instance with an empty database and just added a currency and an account. Then tried the import again. Same error.

@andremohrmann commented on GitHub (Feb 6, 2025): This is my full import yaml now: ```yaml settings: file_type: csv delimiter: "," encoding: utf-8 skip_lines: 0 importing: transactions trigger_transaction_rules: true skip_errors: false mapping: account: target: account source: "Currency" type: name transformations: - type: "replace" pattern: "EUR" replacement: "AM" exclusive: true amount: target: amount source: "Amount" required: true description: target: description source: "TransactionDescription" internal_id: target: internal_id transformations: - type: hash fields: ["Timestamp", "TransactionDescription", "Amount"] date: target: date source: "Timestamp" format: "%d/%m/%Y" is_paid: target: is_paid detection_method: always_paid deduplication: - type: compare fields: - internal_id match_type: strict ``` I still get this error: ``` 2025-02-06 21:20:20 2025-02-06 20:20:20,790 INFO procrastinate.worker Starting job apps.import_app.tasks.process_import[7](file_path='/usr/src/app/temp/card_transactions_record_02052025_221347_TTLEnjQ.csv', import_run_id=4) 2025-02-06 21:20:20 2025-02-06 20:20:20,853 ERROR procrastinate.worker Job apps.import_app.tasks.process_import[7](file_path='/usr/src/app/temp/card_transactions_record_02052025_221347_TTLEnjQ.csv', import_run_id=4) ended with status: Error, lasted 0.062 s 2025-02-06 21:20:20 Traceback (most recent call last): 2025-02-06 21:20:20 File "/usr/src/app/apps/import_app/services/v1.py", line 615, in process_file 2025-02-06 21:20:20 self._process_csv(file_path) 2025-02-06 21:20:20 File "/usr/src/app/apps/import_app/services/v1.py", line 590, in _process_csv 2025-02-06 21:20:20 self._process_row(row, row_number) 2025-02-06 21:20:20 File "/usr/src/app/apps/import_app/services/v1.py", line 529, in _process_row 2025-02-06 21:20:20 mapped_data = self._map_row(row) 2025-02-06 21:20:20 ^^^^^^^^^^^^^^^^^^ 2025-02-06 21:20:20 File "/usr/src/app/apps/import_app/services/v1.py", line 513, in _map_row 2025-02-06 21:20:20 raise ValueError(f"Required field {field} is missing") 2025-02-06 21:20:20 ValueError: Required field date is missing 2025-02-06 21:20:20 2025-02-06 21:20:20 During handling of the above exception, another exception occurred: 2025-02-06 21:20:20 2025-02-06 21:20:20 Traceback (most recent call last): 2025-02-06 21:20:20 File "/usr/local/lib/python3.11/site-packages/procrastinate/worker.py", line 281, in run_job 2025-02-06 21:20:20 task_result = await await_func(*job_args, **job.task_kwargs) 2025-02-06 21:20:20 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2025-02-06 21:20:20 File "/usr/local/lib/python3.11/site-packages/procrastinate/utils.py", line 108, in sync_to_async 2025-02-06 21:20:20 return await sync.sync_to_async(func, thread_sensitive=False)(*args, **kwargs) 2025-02-06 21:20:20 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2025-02-06 21:20:20 File "/usr/local/lib/python3.11/site-packages/asgiref/sync.py", line 468, in __call__ 2025-02-06 21:20:20 ret = await asyncio.shield(exec_coro) 2025-02-06 21:20:20 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2025-02-06 21:20:20 File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run 2025-02-06 21:20:20 result = self.fn(*self.args, **self.kwargs) 2025-02-06 21:20:20 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2025-02-06 21:20:20 File "/usr/local/lib/python3.11/site-packages/asgiref/sync.py", line 522, in thread_handler 2025-02-06 21:20:20 return func(*args, **kwargs) 2025-02-06 21:20:20 ^^^^^^^^^^^^^^^^^^^^^ 2025-02-06 21:20:20 File "/usr/src/app/apps/import_app/tasks.py", line 17, in process_import 2025-02-06 21:20:20 import_service.process_file(file_path) 2025-02-06 21:20:20 File "/usr/src/app/apps/import_app/services/v1.py", line 629, in process_file 2025-02-06 21:20:20 raise Exception("Import failed") 2025-02-06 21:20:20 Exception: Import failed ``` I even tried setting up a fresh instance with an empty database and just added a currency and an account. Then tried the import again. Same error.
Author
Owner

@eitchtee commented on GitHub (Feb 6, 2025):

@andremohrmann I'm tracking this in #136

@eitchtee commented on GitHub (Feb 6, 2025): @andremohrmann I'm tracking this in #136
Author
Owner

@eitchtee commented on GitHub (Feb 9, 2025):

I'm closing this issue. If you have suggestions, problems or questions about this system, I'd be happy to help in a separate issue.

@eitchtee commented on GitHub (Feb 9, 2025): I'm closing this issue. If you have suggestions, problems or questions about this system, I'd be happy to help in a separate issue.
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/WYGIWYH#3