import from excel

George OLson grglsn765 at gmail.com
Tue Nov 15 03:57:08 EST 2011


On 11/08/2011 10:19 PM, Derek Atkins wrote:
> George OLson<grglsn765 at gmail.com>  writes:
>
>> On 11/08/2011 04:32 PM, Geert Janssens wrote:
>>>>
>>> You could try to save your excel sheet as a CSV file (comma separated values)
>>> and then use the CSV importer in GnuCash.
>>>
>>> Geert
>>
>> Ok, that is a good idea. I did that and it works. However, the
>> transfer field does not import, and there seems to be no mechanism for
>> including the transfer field in the import. Does anyone know of a way
>> to set the transfer field?
>
> I dont think there is a way to do that in the CSV importer.  You could
> try the Calc2QIF macro to convert your excel to QIF, which does have a
> way to import transfer account info via QIF Categories.
>
>> George
>
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>
> -derek
>

Thanks for all that. I found a way to do it that works for me now. Here 
is as follows:

procedure for changing a CSV to a QIF for import (simple)

arrange the CSV to have 3 columns: Date, description, and transaction amount
the transaction amount must be positive for deposits, negative for 
withdrawals
date format I used was m-d-y

in libreoffice spreadsheet:
   - use search and replace to eliminate any extra commas in the 
description field
   - save file as a csv

on the command line, type the following command from the correct 
directory (changing the appropriate file names):

( echo '!Type:Bank'; cat statement.csv | awk -F, '{ print "D" $1; print 
"P" $2; print "T" $3; print "^"; }' ) > statement.qif

Then open the newly created file in kedit or something like that to 
check the formatting
   - use search and replace to eliminate all quotation marks (assuming 
libreoffice saved your csv with quotation marks)

Then the file is ready for importing into gnucash.

-G


More information about the gnucash-user mailing list