OFX date:time conversion

[jsquarepants]

I am writing an app that takes ofx banking files files on convert them to csv for excel or importation into sql. I am on the last task. Converting the nonstandard date:time stamp. I am using preg_replace(). I am not the best at regular expressions. I am getting this error: "Warning: preg_replace() [function.preg-replace]: Unknown modifier '\' in C:\xampp\htdocs\banking\ofx-date.php on line 13"

 

CODE:

        // Date Time 20110228000600 (2011/02/28/, 00:06:00)

        // Expected Return '<DTPOSTED>2011/02/28,00:06:00'

       

        $ofxDate = '<DTPOSTED>20110228000600';

        $ofxNewDate =  substr($ofxDate,0,10) . substr($ofxDate,10,4) . '/' . substr($ofxDate,14,2) . '/' .

            substr($ofxDate,16,2) . ',' . substr($ofxDate,18,2) . ':' . substr($ofxDate,20,2) . ':' . substr($ofxDate,22,2);

        echo htmlentities($ofxNewDate) . PHP_EOL;;

       

        $patternA = '/<DTPOSTED>/\b[0-9]+\b';

        //$replacement = '/<DTPOSTED>/';

       

        $stringB = preg_replace($patternA, $ofxNewDate, ofxDate);

        echo htmlentities($StringB) . PHP_EOL;

 

[Pikachu2000]

Why not simply use the date/time functions that are available in php?

 

echo '<DTPOSTED>' . date('Y/m/j,H:i:s', strtotime($str));

 

Returns: <DTPOSTED>2011/02/28,00:06:00

Obviously, you'd need to change the html entities to the actual < and > characters in your code to make it an actual tag.

[jsquarepants]

Picachu2000,

 

Thanks for the help. What am I doing wrong?

 

<?PHP

    echo '<DTPOSTED>' . date('Y/m/j,H:i:s', strtotime($str));

    $ofxStr = '<STMTTRN><TRNTYPE>FEE<DTPOSTED>20110228000600<TRNAMT>-6.00<FITID>106608220110228WF<NAME>ATM FEE<MEMO>Withdrawal Fee</STMTTRN>';

 

    $patternA = '<DTPOSTED>\b[0-9]+\b';

    //$replaceA = "date('Y/m/j,H:i:s', strtotime($patternA))";

    $stringB = preg_replace($patternA, '<DTPOSTED>' . date('Y/m/j,H:i:s', strtotime($str)), $ofxStr);

    echo htmlentities($stringB) . PHP_EOL;

   

Return:

 

<DTPOSTED>1970/01/1,00:00:00

Warning: preg_replace() [function.preg-replace]: Unknown modifier '\' in C:\xampp\htdocs\banking\snipet.php on line 7

?>

[jsquarepants]

I am attempting to write code to import an ofx file obtained from my bank to a MySQL database. I have all the documentation describing the xml like tags in the ofx file.

 

I have the code working that replaces the xml like tags working but before I call that function I need to convert the date posted string into date and time fields. The regular expression, '<DTPOSTED>[0-9]{14,}', work in PSPad and regextester.com.

 

I basically have two issues.

 

1. How do I properly escape the search string '<DTPOSTED>[0-9]{14,}' for PHP?

 

2. How do I get the found string into $str of date('Y/m/j,H:i:s', strtotime($str)) for the replacemant?

 

My code is below. I know I am close but I am missing some thing.

 

Thanks in advance.

 

<?PHP

    $ofxStr = '<STMTTRN><TRNTYPE>FEE<DTPOSTED>20110228000600<TRNAMT>-6.00<FITID>106608220110228WF<NAME>ATM FEE<MEMO>Withdrawal Fee</STMTTRN>';

    $patternA = '<DTPOSTED>[0-9]{14,}';   

    $replaceA = "date('Y/m/j,H:i:s', strtotime($str))";

    $stringB = preg_replace($patternA, $replaceA, $ofxStr);

    echo htmlentities($stringB) . PHP_EOL;

?>

[Pikachu2000]

I'm sorry, the $str variable held your 20110201 etc.  date string. I must have cut it off when I copied and pasted it.

[jsquarepants]

Sorry Pikachi2000,

 

I don't think I understood your last post.

 

<?PHP

    $ofxStr = '<STMTTRN><TRNTYPE>FEE<DTPOSTED>20110228000600<TRNAMT>-6.00<FITID>106608220110228WF<NAME>ATM FEE<MEMO>Withdrawal Fee</STMTTRN>';

 

    $patternA = '/<DTPOSTED>+[0-9]{14,}/';   

    $replaceA = "(date('Y/m/j,H:i:s', strtotime($ofxStr)))";

    $stringB = preg_replace($patternA, $replaceA, $ofxStr);

 

    echo htmlentities($stringB) . PHP_EOL;

?>

 

Return:

 

<STMTTRN><TRNTYPE>FEE(date('Y/m/j,H:i:s', strtotime(<STMTTRN><TRNTYPE>FEE<DTPOSTED>20110228000600<TRNAMT>-6.00<FITID>106608220110228WF<NAME>ATM FEE<MEMO>Withdrawal Fee</STMTTRN>)))<TRNAMT>-6.00<FITID>106608220110228WF<NAME>ATM FEE<MEMO>Withdrawal Fee</STMTTRN>

[jsquarepants]

Pikachu,

 

I seem to be making some progress but the backreference is not working the way I understand it.

 

http://php.net/manual/en/function.preg-replace.php

 

Return:

<STMTTRN><TRNTYPE>FEE<DTPOSTED>2011/02/28,00:06:00<TRNAMT>-6.00<FITID>106608220110228WF<NAME>ATM FEE<MEMO>Withdrawal Fee</STMTTRN>

Warning: strtotime() expects at least 1 parameter, 0 given in C:\xampp\htdocs\banking\snipet.php(7) : regexp code on line 1

<STMTTRN><TRNTYPE>FEE<DTPOSTED>1970/01/1,00:00:00<TRNAMT>-6.00<FITID>106608220110228WF<NAME>ATM FEE<MEMO>Withdrawal Fee</STMTTRN>

 

[attachment deleted by admin]

[Pikachu2000]

The post I made originally should have read like follows:

$str = '20110228000600'; // This was the date string in your OP.
echo '<DTPOSTED>' . date('Y/m/j,H:i:s', strtotime($str));

 

Which returns: <DTPOSTED>2011/02/28,00:06:00

Obviously, you'd need to change the html entities to the actual < and > characters in your code to make it a proper tag.