After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 738462 - CSV Transaction Import should be able to import CSV files generated by GnuCash export
CSV Transaction Import should be able to import CSV files generated by GnuCas...
Status: RESOLVED FIXED
Product: GnuCash
Classification: Other
Component: Import - CSV
2.6.4
Other Linux
: Normal enhancement
: future
Assigned To: gnucash-import-maint
gnucash-import-maint
: 737193 (view as bug list)
Depends on:
Blocks:
 
 
Reported: 2014-10-13 13:51 UTC by Andy Lavarre
Modified: 2018-06-29 23:34 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
Details and discussion of CSV import algorithm (3.58 KB, text/plain)
2014-10-13 13:51 UTC, Andy Lavarre
  Details
Patch to clarify CSV transaction export (1.17 KB, patch)
2014-11-19 11:46 UTC, Bob
committed Details | Review
Add ability to search equal strings (14.37 KB, patch)
2015-01-08 11:52 UTC, Bob
none Details | Review
Change the Transaction export to add some new fields (19.22 KB, patch)
2015-01-08 11:58 UTC, Bob
none Details | Review
Add Fixed Format Transaction Import (95.61 KB, patch)
2015-01-08 12:07 UTC, Bob
none Details | Review
Add ability to search equal strings (14.93 KB, patch)
2015-01-19 11:04 UTC, Bob
committed Details | Review
Change the Transaction export to add some new fields (18.82 KB, patch)
2015-01-19 11:06 UTC, Bob
committed Details | Review
Add Fixed Format Transaction Import (95.62 KB, patch)
2015-01-19 11:07 UTC, Bob
needs-work Details | Review
Screenshot of failing import (92.37 KB, image/png)
2015-01-28 17:41 UTC, Geert Janssens
  Details
Sample csv file using quotes and , with , as decimal separator as well. (3.03 KB, text/plain)
2015-01-28 17:50 UTC, Geert Janssens
  Details
Add Fixed Format Transaction Import version 2 (95.90 KB, patch)
2015-02-01 19:26 UTC, Bob
committed Details | Review
Consolidate some fields in transaction export (6.33 KB, patch)
2015-02-02 16:26 UTC, Bob
committed Details | Review
Consolidate some fields in transaction import (15.33 KB, patch)
2015-02-02 16:27 UTC, Bob
committed Details | Review
Fix missing scheme changes (2.12 KB, patch)
2015-02-21 12:24 UTC, Bob
committed Details | Review

Description Andy Lavarre 2014-10-13 13:51:40 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
Comment 1 Andy Lavarre 2014-10-13 13:56:19 UTC
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.
Comment 2 Bob 2014-10-20 09:32:21 UTC
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 ?
Comment 3 Geert Janssens 2014-10-21 10:47:42 UTC
*** Bug 737193 has been marked as a duplicate of this bug. ***
Comment 4 Bob 2014-11-19 11:46:14 UTC
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
Comment 5 Andy Lavarre 2014-11-19 18:57:10 UTC
Thanks!
Cheers, Andy
Comment 6 Geert Janssens 2014-11-30 14:47:10 UTC
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.
Comment 7 Bob 2015-01-08 11:52:17 UTC
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);
Comment 8 Bob 2015-01-08 11:58:57 UTC
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.
Comment 9 Bob 2015-01-08 12:07:40 UTC
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.
Comment 10 Bob 2015-01-19 11:04:39 UTC
Created attachment 294855 [details] [review]
Add ability to search equal strings

Missed a couple of case options.
Comment 11 Bob 2015-01-19 11:06:08 UTC
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.
Comment 12 Bob 2015-01-19 11:07:17 UTC
Created attachment 294857 [details] [review]
Add Fixed Format Transaction Import

Modified patch for recent PO file changes.
Comment 13 Geert Janssens 2015-01-28 17:32:25 UTC
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.
Comment 14 Geert Janssens 2015-01-28 17:41:35 UTC
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.
Comment 15 Geert Janssens 2015-01-28 17:50:38 UTC
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.
Comment 16 Bob 2015-02-01 19:26:36 UTC
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.
Comment 17 Bob 2015-02-02 16:26:10 UTC
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.
Comment 18 Bob 2015-02-02 16:27:42 UTC
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.
Comment 19 Geert Janssens 2015-02-07 14:14:16 UTC
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.
Comment 20 Geert Janssens 2015-02-07 14:15:40 UTC
For clarity this new functionality was applied to master and will first appear in gnucash 2.8.0.
Comment 21 Christian Stimming 2015-02-18 19:55:27 UTC
@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
Comment 22 Bob 2015-02-21 12:24:50 UTC
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 23 Geert Janssens 2015-02-22 23:30:15 UTC
Comment on attachment 297490 [details] [review]
Fix missing scheme changes

Thanks for the update Robert. I have committed them to master.
Comment 24 John Ralls 2018-06-29 23:34:54 UTC
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.