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