Forum Discussion
- eduardoeduardoQrew TraineeSeems 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 - _anomDiebolt_Qrew Elite>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: (.*?) - eduardoeduardoQrew TraineeThanks 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.... - _anomDiebolt_Qrew EliteThat 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. - eduardoeduardoQrew TraineeTHANKS 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
- DiretoriaMirantQrew TraineeBom 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
- _anomDiebolt_Qrew EliteFor 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);