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