Writing a script to import bank's csv file
Art
pinaart at yahoo.com
Tue Feb 11 00:08:56 EST 2014
Hi Mike, Brendan,
I noticed there was a significant deviation from the subject line in this thread, so if the "script to import... csv file" is no longer relevant, I apologize.
I was importing data to GC a couple of years ago from Microsoft Money, then my various financial institutions, as I migrated from Money to GC. It's all a blur now, but I found a couple of scripts on the web that addressed my needs (I mostly did a QIF export to dump the data from Money, but still needed CSV to QIF for my other financial data, some of which would be ongoing, except most of my data is available in QIF which I just import).
I have some links from that time which you may have already seen,
Excel file to Quicken (QIF) Format - dmcritchie.mvps.org/excel/xl2qif.htm
Convert CSV to OFX - social.microsoft.com/Forums/en-US/1e01e5a8-3c06-4265-931d-9601af50db56/convert-csv-to-ofx?forum=money
csv2ofx - https://github.com/djmuhlestein/csv2ofx
Csv2OfxCmd - code.google.com/p/hle-ofx-quotes/wiki/Csv2OfxCmd
XL2QIF - xl2qif.chez-alice.fr/xl2qif_en.php
The code I used was,
"Excel to Quicken" - https://groups.google.com/forum/?fromgroups=#!msg/microsoft.public.excel.misc/o8g0kRYXIvA/9iUDjbSHQ3IJ
It worked as advertised, but I had to tweak it to import my specific data into GC. I.e., I got this from GC,
Failed import,
Line 1: File does not appear to be in QIF format: ^
Read aborted.
I think I posted this in a GC mailing list, but I don't recall which one. As much as I hate redundancy, this is my Excel macro,
Sub XL2QIF()
Dim Rw As Long, Clm As Long, filename As String
' Columns("B:F").NumberFormat = "General"
filename = "c:\temp\my.qif"
Close #1
Open filename For Output As 1
For Rw = 2 To 10
If Cells(Rw, 1) = "" Then GoTo done
Print #1, "^"
For Clm = 1 To 10
If Trim(Cells(Rw, Clm)) <> ""
Then
Print #1,
Cells(1, Clm) & Cells(Rw, Clm).Text
End If
Next Clm
Next Rw
done:
Print #1, "^" 'unless concatenating more files
Close #1
'The following can be included to view output,
Shell "notepad.exe " & """" & filename & """",
1
End Sub
Credit is due to the original author, which I didn't include in the macro comments.
I am not including my sample input and output because it's been posted before and I don't want to post what can be Googled.
- Art
On Monday, February 10, 2014 6:17 PM, Michael Iles <michael.iles at gmail.com> wrote:
> No problem. Just means I need to work out how to write a split in a qif
> file.
A split in a QIF file looks like this:
!Type:Bank
D2013-10-01
T5000
MTransfer to somewhere
SAssets:Acct1
$2500
SAssets:Acct2
$2500
^
There's a thriving cottage industry in scripts that generate QIF files
for import into GC. (I would say QIF import is the most effective API
to GC.)
Mike.
_______________________________________________
gnucash-user mailing list
gnucash-user at gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
More information about the gnucash-user
mailing list