Monday, October 13, 2014

Importing CSV into GnuCash

So we have experimented scientifically and carefully and can confirm and replicate the steps below to import CSV files into GnuCash 2.6.4:

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

Discussion

I was on GnuCash 2.4.13 under Linux openSUSE 13.1. This is the default version offered by their repositories. However, in yast2 sw_software you can search for gnucash and open the Versions tab to select the current 2.6.4 offering:

2.6.4-63.1 x86_64

You have to do this also for gnucash-lang to avoid conflicts.

That works. So now we are on 2.6.4.

This offers File → Export → Transactions to CSV

That works just fine.

Importing, however, is a bit more daunting. 

1. First you clear out all the unwanted columns

It appears that the GnuCash CSV import routine requires exactly one line per transaction split, and NO HEADERS, NO TRANSACTION SUMMARY line, and a funky DATE FORMAT.

So you have to load the exported CSV file into an application, e.g., LibreOffice, and farkle with it a bit. ESPECIALLY if it was exported from GnuCash.

2. Regarding the date format: 

It appears that the routine requires m-d-y. But neither Windows 7 nor LibreOffice have that explicitly. You can enter m-d-y as a custom date format in LibreOffice, but 09/04/14  is rendered 9-4-y

So you must use m-d-yy in LibreOffice. 

3. Remove unwanted rows:

It appears that the GnuCash import routine expects one and only line for each transaction and really only requires 5 fields to work. 

But if the CSV was exported from GnuCash you will have at least three lines with fifteen fields:
    1. A summary line for the transaction with Account and fifteen other fields.
    2. Another line for each destination account but no date or account or description fields
    3. Another line for the parent account transaction split again without date or account or description fields.
    
    You must remove #1 and #3.

Otherwise, a piece of cake. 

Easy when you know how.

2 comments:

Unknown said...

If I delete #1 and #3 (assuming we're talking about lines, NOT rows), then the imported transaction would bear no date since the date is saved only on line #1.

GNUcash version 2.6.12 (Windows)

Andy L said...

Hi thanks for the input. Did you go through all the steps I did that showed that it worked at the time? This post was a couple of years ago, things may have changed. Good luck.