Need an OFX parser to import Bank Transactions to quick base

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered

Looking for an OFX parser.

 

Want to import banking transactions, wanting to use dans technique to parse OFX file transform to json, transform to CSV and import.

 

Found Banking.js The Missing API for your bank, but I need a hint of where do I start from

Photo of eduardo

eduardo

  • 40 Points

Posted 3 years ago

  • 1
  • 1
Photo of eduardo

eduardo

  • 40 Points
Seems that OFX files are something similar to xml and readable with file reader.

The structure has some headers

OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX>
..........
/<OFX>

Which I splice.

I only need the Basic information of a transaction:

Example:

<STMTTRN>
  <TRNTYPE>CHECK
  <DTPOSTED>20150930
  <TRNAMT>-15840.00
  <FITID>2015093011584000
  <CHECKNUM>850093
  <MEMO>CHEQUE PAGO OUTRA AG NCIA
</STMTTRN>

My problem is that there are no delimiters in between. Expected to be

<STMTTRN>
  <TRNTYPE>CHECK</TRNTYPE>
  <DTPOSTED>20150930 </DTPOSTED>
  <TRNAMT>-15840.00 </TRNAMT>-
  <FITID>2015093011584000 </FITID>
  <CHECKNUM>850093 </CHECKNUM>
  <MEMO>CHEQUE PAGO OUTRA AG NCIA </MEMO>
</STMTTRN>

I Cut and Paste Dan’s Piece of code, after using file reader:

$xml.find("STMTTRN").each(function(row) {

    var csv_line = [];
       var tipo = $(this).find('TRNTYPE').text();
       csv_line.push(tipo);

       var data = $(this).find('DTPOSTED').text();
       csv_line.push(data);

       var valor = $(this).find('TRNAMT').text();
       csv_line.push(valor);

       var numDoc = $(this).find('FITID').text();
       csv_line.push(numDoc);

       var numChec = $(this).find('CHECKNUM').text();
       csv_line.push(numChec);

        var memo = $(this).find('MEMO').text();
       csv_line.push(memo);


       csv_array.push('"' + csv_line.join('","') + '"');


   });
etc etc
It Works but each var picks up all the information from his value and forward.

Output:
"CHECK
    20150930
    -15840.00
    2015093011584000
    850093
    CHEQUE PAGO OUTRA AG�NCIA
   ","20150930
    -15840.00
    2015093011584000
    850093
    CHEQUE PAGO OUTRA AG�NCIA
   ","-15840.00
    2015093011584000
    850093
    CHEQUE PAGO OUTRA AG�NCIA
   ","2015093011584000
    850093
    CHEQUE PAGO OUTRA AG�NCIA
   ","850093
    CHEQUE PAGO OUTRA AG�NCIA
   ","CHEQUE PAGO OUTRA AG�NCIA
   "

Any hint of how can I limit to pick only the var value?
Sure is some javascript magic that I don't Know

After that is the same as all ImportCsv implementations
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
>Seems that OFX files are something similar to xml and readable with file reader.

OFX files are not XML. There is a header with attribute value pairs followed by something that looks like XML but isn't (it is SGML without the requirement of closing tags). You will probably have to parse the OFX file using regular expressions. If you isolate the text after the header you can attempt to parse the thing that looks like XML using code similar to this:

var doc = "";
doc += "<STMTTRN>";
doc += "  <TRNTYPE>CHECK ";
doc += "  <DTPOSTED>20150930 ";
doc += "  <TRNAMT>-15840.00 ";
doc += "  <FITID>2015093011584000 ";
doc += "  <CHECKNUM>850093 ";
doc += "  <MEMO>CHEQUE PAGO OUTRA AG NCIA ";
doc += "</STMTTRN>";

var TRNTYPE = doc.match(/<TRNTYPE>(.*?)</)[1].trim();
console.log("TRNTYPE=" + TRNTYPE); // outputs: TRNTYPE=CHECK

The key is to use a catapulting group with the non-greedy regexp: (.*?)
Photo of eduardo

eduardo

  • 40 Points
Thanks Dan!!
Actually I have been able to parse it with ;

var tipo = $(this).find('TRNTYPE').text();
       var tipoS = tipo.split(" ");
       csv_line.push(tipoS[0]);

It is working (of course I use your technique to import afterwards

Seems OFX is not very standard in Brazil, cause each bank has different formats, Some have closing tags some do not....
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
That code would fail if attempting to parse the MEMO data which has spaces in its value. The statement I used is simple

doc.match(/<TRNTYPE>(.*?)</)[1].trim();

Match and capture all text after "<TRNTYPE>" up to the next "<" delimiter (ie non-greedy match)- trim the result of extra whitespace.
Photo of eduardo

eduardo

  • 40 Points
THANKS WILL test your suggestion seems simple and will work in every field, memo is the last one I parse so I was not using split(" ") , so it was working
Photo of Martin

Martin

  • 86 Points 75 badge 2x thumb
Bom dia Eduardo,
Estou usando o quickbase já a 4 anos, mas estou desistindo dele, você conseguiu integrar com os extratos bancários ou com a emissão de boletos?
Estou pensando em migrar para Windev, o que acha?
Muito obrigado
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
For technical reasons I will not get into here I think you will probably have to use this regexp:

var MEMO = doc.match(/<MEMO>([\s\S]*?)</)[1].trim();
console.log("MEMO=" + MEMO);