import from excel

[Robert Eldon Taylor]

---------
> 
> Message: 13
> Date: Tue, 15 Nov 2011 04:28:37 -0500
> From: "Derek Atkins" <derek at ihtfp.com>
> To: "George OLson" <grglsn765 at gmail.com>
> Cc: gnucash-user at gnucash.org
> Subject: Re: import from excel
> Message-ID:
>     <fb9e4b03cd2e2d93404f663dd0e97401.squirrel at mail2.ihtfp.org>
> Content-Type: text/plain;charset=iso-8859-1
> 
> 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
> 

This script won't work with windows, but is not actually necessary. 
Some years ago I worked out a procedure to create a qif in excel.  I 
am not familiar with libreoffice but suspect it will work with that 
too.  I am certain it works with Open Office Calc.  I never made much 
use of it because it doesn't work with quickbooks, where it would be 
more useful.  Be glad to pass it on to anyone who wants it.

Regards, Bob Taylor
philologos at mindspring dot com