import from excel

[Derek Atkins]

Hi,

On Tue, November 15, 2011 3:57 am, George OLson wrote:
> 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.

Glad you got it working, and thank you for this script.  It's a fairly
minimal QIF file, but it will certainly work.  Note that if you need to
have transfers to asset/liability accounts then you need to add L[Acct]
lines, and if you want to be more clever you could add Income/Expense
Categories using LCategory lines.

> -G

-derek

-- 
       Derek Atkins                 617-623-3745
       derek at ihtfp.com             www.ihtfp.com
       Computer and Internet Security Consultant