I am needing PHP code that will parse specific values from a QuickBooks Online (QBO) file, also known as the OFX/QFX file format (http://en.wikipedia.org/wiki/QFX_%28file_format%29).
A section of my sample QBO file that can be used for testing is the following:
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX><SIGNONMSGSRSV1><SONRS><STATUS><CODE>0<SEVERITY>INFO</STATUS><DTSERVER>20150518082838<LANGUAGE>ENG<FI><ORG>Bank Name<FID>1234</FI><INTU.BID>1234<INTU.USERID>123456789012</SONRS></SIGNONMSGSRSV1>
<BANKMSGSRSV1><STMTTRNRS><TRNUID>0<STATUS><CODE>0<SEVERITY>INFO</STATUS><STMTRS><CURDEF>USD<BANKACCTFROM><BANKID>123456789<ACCTID>12345678901<ACCTTYPE>CHECKING</BANKACCTFROM><BANKTRANLIST><DTSTART>20140204235959<DTEND>20150512235959
<STMTTRN><TRNTYPE>DIRECTDEBIT<DTPOSTED>20140204235959<TRNAMT>-000000056.32<FITID>2014000000000000000000000000000000000000000000000000000<NAME>ELECT PWR<MEMO>WEB</STMTTRN>
</BANKTRANLIST><LEDGERBAL><BALAMT>123.45<DTASOF>20150515235959</LEDGERBAL><AVAILBAL><BALAMT>123.45<DTASOF>20150515235959</AVAILBAL></STMTRS>
</STMTTRNRS></BANKMSGSRSV1></OFX>
I am having trouble getting values from an QBO to an array in php. I've looked into various utilities such as QBO2CSV (http://www.propersoft.net/qbo2csv/home) and FixOFX (https://github.com/wesabe/fixofx) and would like to use just PHP code to do this if at all possible. QBO2CSV seemed to almost work if I use a command line to convert the QBO to a CSV and then parse the CSV, but if I could just do this in PHP then I could cut out a few steps.
I also have issue cleaning up the QBO and then using SimpleXMLElement as the QBO files are very non-standard XML and I have been unable to clean it up enough to have SimpleXMLElement accept it as standard XML. The best example I have found for this is at: http://www.ibm.com/developerworks/library/x-ofxv2/ ...and it almost works. It is the closest code solution I have found, but it still isn't producing the results. This solution also tries to use SimpleXMLElement after cleaning up the QBO, but it as well has difficulty cleaning up the QBO to become accepted by SimpleXMLElement.
Parts of my attempted solution are below, but I am having difficulty with the XML brackets.
My code:
// READ CONTENTS OF FILE TO STRING
$cont = file_get_contents('C:\xampp\htdocs\testparse\test.qbo');
// STRIP OUT HEADER
$bline = strpos($cont,"<OFX>");
$head = substr($cont,0,$bline-2);
$ofx = substr($cont,$bline-1);
// 3. Examine tags that might be improperly terminated
$ofxx = $ofx;
// NUMBER OF TAGS
$numtags = substr_count($ofxx, '<');
// INIT LOOP
$tagloop = 1;
// PARSE THROUGH TAGS
while ($tagloop <= $numtags){
$tagloop++;
$pos = strpos($ofxx,'<');
$pos2 = strpos($ofxx,'>');
$ele = substr($ofxx,$pos+1,$pos2-$pos-1);
// FIND TAGS AND MAKE SURE THEY ARE IN HTML FORMAT WITH BRACKETS:
$tagstart = "<";
$tagend = ">";
$omittag = $tagstart . $ele . $tagend;
//FIND END OF TAG
$pos3 = strpos($omittag,'>');
$pos4 = $pos3+1;
//STRIP TAG OF ANY REMAINING CHARS AFTER THE ">" CHAR
//FOR SOME REASON OCCASIONALLY THE STRING WOULD BE LONGER THAN INTENDED, SO THIS MAKES SURE IT IS CUT OFF AFTER ">"
$omittag2 = substr($omittag, 0, $pos4);
//REMOVE TAG FROM MAIN STRING
$ofxx = preg_replace($omittag2, '', $ofxx, 1);
// TROUBLES OCCUR HERE...I CAN'T SEEM TO BE ABLE TO GET RID OF EMPTY <> and > CHARS...NOT SURE WHY THEY ARE HERE SINCE THE ABOVE SHOULD HAVE REMOVED ALL OF THE TAG BUT SOMETIMES "<>" OR ">" REMAIN
// WHAT I AM THEN TRYING TO DO IS TO GRAB THIS TAG'S NAME AND THEN EVENTUALLY STORE IT IN AN ARRAY ALONG WITH ITS DATA. SINCE QBOs DO NOT HAVE TERMINATING TAGS THEY EITHER NEED CONVERTING TO SELF TERMINATING TAGS OR JUST TRY AND GRAB A TAG VALUE, AND THE DATA THAT FOLLOWS IT AS THE DATA FOR THAT TAG
//FIND START OF NEXT TAG
$pos5 = strpos($ofxx,'<');
//USE THE START OF POS5 OF THAT TAG TO GRAB DATA FOR THE CURRENT TAG IF POS5 GREATER THAN ZERO
if ($pos5 > 0) {
$tagdata = substr($ofxx, 0, $pos5);
}
// 5. Deal with special characters
$ofxx = str_replace('&','&',$ofxy);
} // END LOOP
I think my biggest issue is dealing with the "<" and ">" characters. I am having trouble removing them as I am going through the string and parsing out the values.
Once I am seeing the correct values echoed, I will then start to add them to an array to then add to a MySQL database.
