[GNC] Data load from CSV files - duplicates
Kalpesh Patel
kalpesh.patel at usa.net
Tue Nov 23 13:36:30 EST 2021
Work flow using two pass for the CSV importer (for that fact any importer)
yields better result for me as well, generally speaking. This is probably
best way to handle double-entry accounting. In the first pass I import new
or update existing transactions into register for one side of the transfers
and then run it again for the other side of the transaction. During second
run, in the 'Match Transactions' windows I adjust the import of the
transaction from either 'A' or 'C' column in the window to 'U+C' column -
this can be toggled in that 'Match Transactions' window freely for any
transaction - and you won't have to do anything with the first pass
transactions as they all will all be matched correctly in the second pass so
only ones that will be in play are the transfers transactions only needing
review.
By clicking the 'Show Account column' and 'Show matched information' at the
bottom of the 'Match Transactions' window before importing any transaction,
you will get very good idea how the import will be done for that
transaction. Not sure if that settings to Show Account column and Show
matched information can be made sticky settings or not, though.
Only drawback with this two pass workflow is that 'Description' of
transaction in required to import CSV and it will be set to the one that was
last imported one when you select 'U+C'. I would love to see a setting to
use single column for multiple fields during import or better yet make
Description requirements optional which would give me method to cross link
transactions using my generic description and then allowing financial
institution specific details in the memo field for both side of the
transaction. But I imagine that this will complicated matcher mechanism so
it is reasonable that this is not implemented.
------------------------------
Message: 2
Date: Tue, 23 Nov 2021 15:46:27 +1000
From: davidcousens49 at gmail.com <mailto:davidcousens49 at gmail.com>
To: gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
Subject: Re: [GNC] Data load from CSV files - duplicates
Message-ID: <d2e2be17cadceb8c57924ce17d5ffdece1bd7e3a.camel at gmail.com
<mailto:d2e2be17cadceb8c57924ce17d5ffdece1bd7e3a.camel at gmail.com> >
Content-Type: text/plain; charset="UTF-8"
Leo,
You can always manually override GnuCash's matching in the Import Matcher
window by checking the "C" ("R" in older GnuCash versions) column if Gnucash
has selected the "A" column to import the transaction and you do know it
matches a transaction you have imported to another account. This will avoid
having to delete the duplicate transactions. I usually import my bank
account data first so when I import the credit card data I know any credit
card payments should already have been entered from my bank statement.
David Cousens
On Tue, 2021-11-23 at 01:46 +0000, Leo Vaccaneo wrote:
> Thanks to David T and David Carlson -
>
> to David T (and David Carlson) -
> yes - I believe you have hit what I am looking for - so, trying to say
> it again in a hopefully helpful way -
>
> being able to assign / match (the term in the CSV importer process)
> the second split of the transaction being loaded -
>
> to an existing transaction in the 'destination' 'other side / end of
> the transaction' account -
>
> instead of creating a replica of the trans being loaded - as the
> second split -in the 'destination' 'other side / end of the
> transaction' account
> ---
>
> to David Carlson -
>
> I will work my way through each of your suggestions, and give an
> update -
>
> The GNU installer was downloaded yesterday.
>
> The rest - I will need to work through.
>
> Sounds like it will be well worth my while to try the QIF import if I
> can get that format export from my bank (have to check).
>
> Thanks again to all
>
> Leo Vaccaneo
>
>
> ________________________________
> From: D. <sunfish62 at yahoo.com <mailto:sunfish62 at yahoo.com> >
> Sent: Monday, 22 November 2021 9:47 PM
> To: David Carlson <david.carlson.417 at gmail.com
<mailto:david.carlson.417 at gmail.com> >
> Cc: Leo Vaccaneo <leovplex at outlook.com <mailto:leovplex at outlook.com> >;
Gnucash Users <
> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> >
> Subject: Re: [GNC] Data load from CSV files - duplicates
>
> ...Fifth, I think Leo is using the term "match" to refer to the
> process of
> *assigning* the second split to an account. ISTR that there is a step
> in the process where the user can actually match imported transactions
> to existing ones, along with the option to use the existing
> description or replace it, which would eliminate that occurrence of a
> second transaction. Or perhaps I'm just getting senile.
>
> David T.
> ________________________________
> From: David Carlson
> Sent: Mon Nov 22 05:21:16 EST 2021
> To: Leo Vaccaneo
> Cc: "gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> "
> Subject: Re: [GNC] Data load from CSV files - duplicates
>
>
> There are a number of things that may be impacting your results.
>
> First, check Edit > Preferences > Online Banking to be sure that
> options under Generic Importer are still checked and the values listed
> are reasonable.
>
> Second, the entire chapter 16 of the Help manual has details that come
> into play. Reread <
> https://www.gnucash.org/docs/v4/C/gnucash-help/trans-import.html#trans
> -import-matcher
>
>
> Third, there have been some issues with some parts of the CSV and
> Generic importers that have been discussed here in the last several
> weeks. The exact release of your GnuCAsh program could make a
> difference. Please tell us which version and which OS you are using.
>
> Fourth, while the OFX/QFX and QIF importers have some issues as well,
> they have the advantage of being more mature and their limitations are
> better understood by users. If your bank offers either of these types
> of exports, try them. The QIF seems to work well for initial imports,
> taken in fairly small chunks, say a month's data at a time.
>
> When you have distilled your results to more specific issues, users
> here can give better advice to address your problems.
>
> On Mon, Nov 22, 2021 at 3:20 AM Leo Vaccaneo <leovplex at outlook.com
<mailto:leovplex at outlook.com> > wrote:
>
> Greetings from Cairns Qld Australia,
>
> I have my new accounts hierarchy data saved in a new file in the
> SQLite format.
>
> I have read the concepts guide & help docs.
>
> I have searched with our favourite search engine around the issue of
> imports from CSV etc files, and matching, and how to sort out
> duplicates
>
> I am somewhat aghast at the outcomes mentioned - deleting duplicates
> and/or doing reversals.
>
> I am therefore going to be more verbose than the mail list items
> questioners I found, to set out my frustration.
> ---
>
> I created a new file to do this test. I completely deleted all
> transactions from a previous file, the account balances all read zero,
> and saved this as my new test file.
>
> In my accounts, I have an assets, savings acct. I have a csv file of
> savings account transactions export from my bank.
>
> In my accounts, I have a liabilities, credit card acct. I have a csv
> file of credit card transactions export from my bank.
>
> These are my first 2 imports.
>
> I imported the credit card transactions first, from CSV.
>
> In the matching stage, for payments into the credit card from the
> savings, GnuCash did not offer any matches I set the match to be the
> savings account.
> Loaded, no errors, -
>
> And it created transactions in the savings account (presumably the
> second split of the credit card account transactions)
> ---
>
> I then imported the savings account transactions from CSV.
>
> In the matching stage, for payments from the savings into the credit
> card, GnuCash did not offer any matches I set the match to be the
> credit card account.
> Loaded, no errors, -
>
> And it created transactions in the credit card account (presumably
> the second split of the savings account transactions)
>
> NOW, - I have 2 versions of each of these transactions, - which are
> in fact the same transaction, - in each of the savings, and, the
> credit card accounts!! completely fouling up balances.
> ---
>
> Have I done something wrong?
>
> I don't want to be manually trying to delete some transaction splits,
> or trying to delete or reverse some transactions -
> . it would be just another manual tedious process
> . I would likely do it wrong and break the transaction(s) completely.
> ---
>
> If I don't provide manual matching, the transactions all end up
> listed in the Imbalance acct.
> ---
>
> If I were to import the credit card transactions - without providing
> manual matching -
>
> When I then import the savings account transactions -
>
> How to I tell it that -
> This transaction - x - being loaded into this savings account should
> be matched to that existing transaction - y - in that credit card account
> AND - remove that transaction - y - from the Imbalance acct
>
> I find it quite frustrating that it appears that I have to match
> during both imports, and then do more manual jiggery pokery to sort
> out the duplicates!
>
> There has to be a better way.
> ---
>
> I have not tried it - BUT -
> Could I import the credit card transactions, with no matching -
> second splits will be in Imbalance - THEN, when I do savings import,
> - is it possible to do the matching to the credit card second split
> in the imbalance account - and end up without duplicates, and the
> transaction cleared from Imbalance??
>
> Hoping for enlightenment
>
> Leo Vaccaneo
>
> ________________________________
>
> gnucash-user mailing list
> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
More information about the gnucash-user
mailing list