mirror of
https://github.com/eitchtee/WYGIWYH.git
synced 2026-01-14 21:23:29 +01:00
CSV Import function #3
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
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
@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.
@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>@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:
@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.
@Inrego commented on GitHub (Jan 17, 2025):
Have you given thought to automatically import bank transactions through api's?
@IZIme07 commented on GitHub (Jan 17, 2025):
You can make it yourself, try to use N8N or similar
@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)
@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
@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
@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
@eitchtee commented on GitHub (Jan 20, 2025):
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 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.
@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:
The problem is that when I try to create the import profile, it throws this error:
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 commented on GitHub (Jan 28, 2025):
@crazybob1215 you have to use a list:
or
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 it should look something like this in the end:
@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:
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:
@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
typemapping to:Currently you can't source a mapped value, just CSV headers.
@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
amountand nottype? If I set it totype, I get what appears to be a format handling error. When set toamountit runs successfully.Now that it's successfully imported, I'm seeing two new errors.
Order bytoNewest firstI can't see any of the imported transactions. I can see them with it set toDefaultorOldest first.detection method: signto correctly identify the transactions but apparently something isn't correct there.@eitchtee commented on GitHub (Jan 28, 2025):
Glad it worked.
You need to have two different mappings:
and
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,
typeis 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
amountandtypewith 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.
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):
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 commented on GitHub (Jan 28, 2025):
Adding both the
amountandtypemappings and using theClear Cachebutton seems to have fixed both of the issues. Thanks for all the help with getting my head wrapped around this!@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):
@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 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 bysetting as I change pages now too, which is pretty handy.@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.
@eitchtee commented on GitHub (Jan 31, 2025):
@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 commented on GitHub (Jan 31, 2025):
Yes, I like it pretty much, just still figuring out the data for importing
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
@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:
My yaml configuration:
My transactions csv file:
What am I missing here?
@eitchtee commented on GitHub (Feb 6, 2025):
@andremohrmann If the first line of your CSV is the header as in your example, you can set
skip_linesto 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:
This will hash the fields and add them to internal_id so you don't re-import then in the future. If
Transaction Hashis present on your file but omitted here, you could use that instead, then you won't need the transformations.This will add a income or expense based on the amount sign
This should add all transactions with currency EUR to the account you set.
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.
@andremohrmann commented on GitHub (Feb 6, 2025):
This is my full import yaml now:
I still get this error:
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.
@eitchtee commented on GitHub (Feb 6, 2025):
@andremohrmann I'm tracking this in #136
@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.