gnucash-user Digest, Vol 138, Issue 7

[Andy Lavarre]

CSV Import/Export: Problem Solved

I'll post this to my blog and let Google digest it, but in the meantime 
delighted to report problem solved. (I think.)

Easy when you know how:

It turns out that (ITOT) 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.

My need is due to the fact that my main (Linux) machine is out of 
commission, so I need to use a different (Windows 7) machine for a while 
until I recover the main one.

During that time I need to be entering transactions to balance the check 
book etc. When I get the main machine back I want simply to export the 
transactions that have been entered into GnuCash on the Win7 during the 
outage and import them back into the main machine, which has years of 
data already there.

Now, having done all this work, ITOT all I really need to do is import 
the *.log *files from the Win7 machine to the GnuCash file on the Linux 
machine when I get it back:

http://www.gnucash.org/docs/v2.4/C/gnucash-guide/basics-backup1.html

But since I've done all this work, and the documentation on CSV import / 
export is sorely lacking, here are my discoveries:

=====

Regarding the date format: 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.

It also turns out that (IATOT) 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 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.

So we have experimented scientifically and carefully and can confirm and 
replicate the steps below.

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

Easy when you know how.


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com