Discussions

Expand all | Collapse all

Need an OFX parser to import Bank Transactions to quick base

  • 1.  Need an OFX parser to import Bank Transactions to quick base

    Posted 02-17-2016 00:06

    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



  • 2.  RE: Need an OFX parser to import Bank Transactions to quick base

    Posted 02-17-2016 16:56
    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


  • 3.  RE: Need an OFX parser to import Bank Transactions to quick base

    Posted 02-17-2016 19:38
    >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: (.*?)


  • 4.  RE: Need an OFX parser to import Bank Transactions to quick base

    Posted 02-17-2016 19:47
    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....


  • 5.  RE: Need an OFX parser to import Bank Transactions to quick base

    Posted 02-17-2016 19:52
    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.


  • 6.  RE: Need an OFX parser to import Bank Transactions to quick base

    Posted 02-17-2016 20:15
    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


  • 7.  RE: Need an OFX parser to import Bank Transactions to quick base

    Posted 01-29-2018 14:38
    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


  • 8.  RE: Need an OFX parser to import Bank Transactions to quick base

    Posted 02-17-2016 20:39
    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);