GNOME Bugzilla – Bug 738462
CSV Transaction Import should be able to import CSV files generated by GnuCash export
Last modified: 2018-06-29 23:34:54 UTC
Created attachment 288386 [details] Details and discussion of CSV import algorithm GnuCash 2.6.4. currently does not directly import a CSV file that it has exported. The following are the steps to reimport an exported transaction report in GC 2.6.4. Details are at the attachment: ===== 1. Open the CSV file (e.g., exported from GnuCash) in an editor (e.g., LibreOffice Calc): a. Edit the file to change date column format from MM/DD/YYYY to m-d-yy b. If there are split transactions i. Add the parent transaction date to each of the splits ii. Add the parent transaction account to each of the splits iii. Delete the parent summary line iv. Delete the parent split line v. Select the number columns and delete all minus signs vi. save and close. 2. In GnuCash: a. Import Transactions from CSV b. Keep the Data Type as Separated i. If it does appear as columns then examine the Separators to find the right one (Comma, Tab, Semicolon, etc.) c. Change the Date Format to m-d-y d. Change the Currency Format to Period e. In the None |None |None |None |... row: i. Rename the Date, Num, Account, Description, To Num, From Num columns to Date, Num, Account, Description, Deposit, Withdrawal f. If you see headers in the presented view then change i. Start import on row to whatever causes the headers to be highlighted in pink, e.g., 2 g. Change i. stop row on to whatever clears any data from being highlighted in pink, e.g., 4 h. Click Forward i. The Match Transactions screen appears: i. It has correctly read the Category column and creates an Info column entry that states New, transfer $(xxx) to (auto)"*Category Name*" j. Click Apply. It reports success. Click Close. k. If it is a new transaction it appears. If it is a duplicate it simply overwrites the original
I have not yet discovered how to deal with splits. It would be highly convenient to be able to just import the full transaction exactly as it was exported. Thank you for an excellent application.
When I wrote this I did not think people would be using it to export some transactions and then try and import them back in, I did not see a use for this. I wrote it so I could export some transactions and import them into a spreadsheet to get an over view of the data and find similar entries with the quick filters that were input differently and then find and correct in Gnucash. What you have done is great and would work for a normal transactions with two splits but with more I think it would be impossible, the only format that can do this I believe would be QIF. May be I should submit a patch to change the text on the opening page ?
*** Bug 737193 has been marked as a duplicate of this bug. ***
Created attachment 290974 [details] [review] Patch to clarify CSV transaction export Andy sent an email to me containing... ### My need arose from having lost my main computer for a long while. So I loaded GnuCash on a separate machine and proceeded to record transactions for the duration until the main machine (and database) were recovered, at which point I wanted to export just the interim transactions from the new machine and import them back into the old machine and database. The matter is now resolved with the reported farkling about. For future however, if there were a way to export GnuCash to QIF that would be just fine as well. I see a lot of chatter about that, but no direct way to do so. Best regards, Andy ### I have added a patch to try and clarify that the CSV transaction export results in each transaction having a minimum of 3 rows and may require further manipulation to get them in a format you can use. Bob
Thanks! Cheers, Andy
Comment on attachment 290974 [details] [review] Patch to clarify CSV transaction export Thanks for the patch. I have applied it to master. More generally I think that improving gnucash such that it can import its own csv export format without further tweaking would be a good thing. So I'll keep this bug open as an enhancement request for that.
Created attachment 294078 [details] [review] Add ability to search equal strings For the transactions import, I require to search for strings being exactly the same but the existing query option is one that contains the search string. This patch adds the option to search for equal strings. There is one issue for this to work, for blank strings to be found I needed to comment out a line in procedure qof_query_string_predicate in file qofquerycore.cpp, the line in question is... g_return_val_if_fail (*str != '\0', NULL);
Created attachment 294079 [details] [review] Change the Transaction export to add some new fields This patch adds some new fields to the CSV export that make it easier to do the import. As part of this, I have removed duplicate transactions from the export with all transactions will be listed under the first occurrence in the list of accounts processed.
Created attachment 294080 [details] [review] Add Fixed Format Transaction Import This patch adds the ability to import the transactions, this is a fixed format import with the fields matching those in the transaction export. A check is made for matching transactions based on the date, account, description, notes, number and value. If exactly the same transactions appears multiple times on the same day, subsequent transactions are deemed to be duplicates. Errors and duplicate entries are listed at the end of import.
Created attachment 294855 [details] [review] Add ability to search equal strings Missed a couple of case options.
Created attachment 294856 [details] [review] Change the Transaction export to add some new fields An erroneous line was added by mistake, removed in this one.
Created attachment 294857 [details] [review] Add Fixed Format Transaction Import Modified patch for recent PO file changes.
Thanks for your work Bob. The first two patches look good, and I have committed them as they are. The last patch however (the import part) fails on my system. Most likely because I have a different decimal separator as you. My system has a ','. I chose to export using quotes and ',' as separator. This should work even if the decimal separator is a ','. Please have a look at your regexes.
Created attachment 295686 [details] Screenshot of failing import This screenshot illustrates what goes wrong. Because the amounts get split on the decimal separator as if it were separate fields, there are superfluous fields. As a result the columns shift.
Created attachment 295688 [details] Sample csv file using quotes and , with , as decimal separator as well. This csv file is what I tried to import in the screenshot. It was exported from gnucash using quotes and , as field separator. , is also the decimal separator.
Created attachment 295897 [details] [review] Add Fixed Format Transaction Import version 2 You were right, I had made an error in the regular expression for the last field when I copied it from the Account export. The other thing I found was the existing function xaccParseAmountExtended had a comment in it that stated it did not track negative signs at the end of numbers. I have compensated for this in the code and your file imported OK, hopefully correct but if you could try that would be great. I will have a look at xaccParseAmountExtended on a separate bug, need to understand what is going on.
Created attachment 295958 [details] [review] Consolidate some fields in transaction export This patch consolidates the three to/from field combinations to three single fields. Hopefully it makes it easier to understand especially with negative numbers.
Created attachment 295959 [details] [review] Consolidate some fields in transaction import This patch consolidates the three to/from field combinations to three single fields to match the transaction export option.
Nice work Bob. With your new set of patches I can successfully import a file I had just exported. It properly detects duplicates as well.
For clarity this new functionality was applied to master and will first appear in gnucash 2.8.0.
@Bob: Unfortunately your patch that resulted in e12c7f75 broke some reports in gnucash. Running i.e. the "income statement" results in the Scheme error "Wrong number of arguments to xaccQueryAddDescriptionMatch", see Scheme trace below. Which is immediately obvious from your change in src/engine/Query.h: Some function argument lists have been extended, but the places in the Scheme code that are also calling this function have not been adapted. Maybe you should make a text search for the function names that you changed on the C side through all the Scheme code to ensure you will now find all of those places, and can you propose a fix? Thanks! 927: 37 [xaccQueryAddDescriptionMatch # "Abschlussbuchungen" #f ...] /home/cs/usr/share/gnucash/scm/report-utilities.scm:927:25: In procedure xaccQueryAddDescriptionMatch in expression (xaccQueryAddDescriptionMatch query2 matchstr ...): /home/cs/usr/share/gnucash/scm/report-utilities.scm:927:25: Wrong number of arguments to xaccQueryAddDescriptionMatch
Created attachment 297490 [details] [review] Fix missing scheme changes Totaly missed the changes I made affecting the scheme code. I have searched for scheme files containing the functions I changed and only found the one related to this report. Patch adds missing argument.
Comment on attachment 297490 [details] [review] Fix missing scheme changes Thanks for the update Robert. I have committed them to master.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=738462. Please update any external references or bookmarks.