Jump to content

[SOLVED] Importing bank records...


forumnz

Recommended Posts

Hey all.

 

I'm quite advanced with PHP, but have never come across this problem - I need to import a bank statement in QXF format into a MySQL database using PHP.

 

This is the format of the document:

 

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>20090430132144

<LANGUAGE>ENG

</SONRS>

</SIGNONMSGSRSV1>

<BANKMSGSRSV1>

<STMTTRNRS>

<TRNUID>1001

<STATUS>

<CODE>0

<SEVERITY>INFO

</STATUS>

<STMTRS>

<CURDEF>NZD

 

<BANKACCTFROM>

<BANKID>12

<BRANCHID>3111

<ACCTID>0000000-50

<ACCTTYPE>CHECKING

</BANKACCTFROM>

 

<BANKTRANLIST>

<DTSTART>20081130

<DTEND>20090430

 

<STMTTRN>

<TRNTYPE>POS

<DTPOSTED>20081130

<TRNAMT>-1000

<FITID>2008113001

<NAME>A SHOP SOMEWHERE

<MEMO>EFTPOS

</STMTTRN>

 

<STMTTRN>

<TRNTYPE>POS

<DTPOSTED>20081130

<TRNAMT>-1000

<FITID>2008113001

<NAME>A SHOP SOMEWHERE

<MEMO>EFTPOS

</STMTTRN>

 

<STMTTRN>

<TRNTYPE>POS

<DTPOSTED>20081130

<TRNAMT>-1000

<FITID>2008113001

<NAME>A SHOP SOMEWHERE

<MEMO>EFTPOS

</STMTTRN>

 

</BANKTRANLIST>

<LEDGERBAL>

<BALAMT>1000

<DTASOF>20090427

</LEDGERBAL>

<AVAILBAL>

<BALAMT>1000

<DTASOF>20090430

</AVAILBAL>

</STMTRS>

</STMTTRNRS>

</BANKMSGSRSV1>

</OFX>

 

End. I don't really need to worry about the bank account information - it's really just the individual transactions I need to insert (from <STMTTRN> to </STMTTRN>).

 

How would I go about doing this?

 

Thanks!

Sam

Link to comment
Share on other sites

I don't think that will parse in an XML parser. It is not normal XML syntax. It is similar but parts of it are different, such as this:

 

<STMTTRN>

<TRNTYPE>POS

<DTPOSTED>20081130

<TRNAMT>-1000

<FITID>2008113001

<NAME>A SHOP SOMEWHERE

<MEMO>EFTPOS

</STMTTRN>

 

There are no close tags for each node inside STMTTRN.

Link to comment
Share on other sites

There might be a ready made parser for this format out there. If not try this below. What it does it takes the raw data as a string and parses the transactions into an associative array of key => value, you can then loop through the returned jagged array and do anything you want with it, such as insert to database:

 

$raw = "

<STMTTRN>
<TRNTYPE>POS
<DTPOSTED>20081130
<TRNAMT>-1000
<FITID>2008113001
<NAME>A SHOP SOMEWHERE
<MEMO>EFTPOS
</STMTTRN>

<STMTTRN>
<TRNTYPE>POS
<DTPOSTED>20081130
<TRNAMT>-1000
<FITID>2008113001
<NAME>A SHOP SOMEWHERE
<MEMO>EFTPOS
</STMTTRN>

<STMTTRN>
<TRNTYPE>POS
<DTPOSTED>20081130
<TRNAMT>-1000
<FITID>2008113001
<NAME>A SHOP SOMEWHERE
<MEMO>EFTPOS
</STMTTRN>


";

function parseData($data)
{
$out = array();

$arr = explode('<STMTTRN>', $data);

for($x=1; $x<count($arr); $x++)
{
	$single = array();

	$arr2 = explode('<', $arr[$x]);

	for($i=1; $i<count($arr2)-1; $i++)
	{
		$arr3 = explode('>', $arr2[$i]);

		$single[$arr3[0]] = $arr3[1];	
	}

	$out[] = $single;
}

return $out;
}

//pass in the raw data string, a jagged array is returned
$parsedData = parseData($raw);

print_r($parsedData);

 

If you're parsing a lot of data this might be slow and will need optimising, but it shows how it can be done.

Link to comment
Share on other sites

Here is an example of how to insert the parsed data into a MySQL database. You'll need to set your database credentials. You also obviously need a table called 'transactions' which includes the transaction fields.

 

//insert the data and function here

$parsedData = parseData($raw);

//setup a mysql connection using the MySQLi class
$db = new mysqli('host', 'user', 'pass', 'database_name');

//check connection
if (mysqli_connect_errno())
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} 

//use a prepared statement so we don't have to worry about escaping the data for SQL Injection attacks
$stmt = $db->prepare("INSERT INTO transactions (TRNTYPE, DTPOSTED, TRNAMT, FITID, NAME, MEMO) VALUES(?, ?, ?, ?, ?, ?)");
foreach($parsedData as $trans)
{
$stmt->bind_param('ssssss', $trans['TRNTYPE'], $trans['DTPOSTED'], $trans['TRNAMT'], $trans['FITID'], $trans['NAME'], $trans[' MEMO']);
$stmt->execute();
}

$stmt->close();
$db->close();

 

Again, this can be optimised to make it more efficient and faster. For example if you will only ever parse the data then insert into database, there's no point returning it as an array, you might aswell just insert at the point of parsing.

Link to comment
Share on other sites

Thanks! I can see how that works (sort of) :)

 

I have tried to get it to work, but i get this error:

 

Fatal error: Call to a member function on a non-object in /domains/html/import.php on line 72

 

Line 72 is:

$stmt = $db->prepare("INSERT INTO transactions (TRNTYPE, DTPOSTED, TRNAMT, FITID, NAME, MEMO) VALUES(?, ?, ?, ?, ?, ?)");

 

What could the problem be?

 

Thanks

Sam :)

Link to comment
Share on other sites

Sort of.. I got this message (which didn't seem right) so I commented out the DB error code.

 

Fatal error: Call to undefined function: mysqli_connect_errno() in /domains/html/import.php on line 57

 

Line 57: if (mysqli_connect_errno())

 

 

Link to comment
Share on other sites

That means your PHP installation does not have access to the MySQLi class unfortunately.

 

What you'll need to do is use the native PHP MySQL functions instead. The same principle applies, only you won't use a prepared statement. You will also need to use mysql_real_escape_string() on each of the six values to prevent SQL Injection.

Link to comment
Share on other sites

Fiexed that error (just changed my version of PHP to 5 :)

 

Now I get this error:

 

Fatal error: Call to a member function prepare() on a non-object in /domains/html/Bank/import.php on line 72

 

Which is:

$stmt = $db->prepare("INSERT INTO transactions (TRNTYPE, DTPOSTED, TRNAMT, FITID, NAME, MEMO) VALUES(?, ?, ?, ?, ?, ?)");

 

What could it be? Thanks so much!

 

Link to comment
Share on other sites

Looks like your database connection failed... although you should have got the connection failed error.

 

Try a test query to see if the $db object is connected to the database, like

 

$db->query("INSERT INTO transactions (TRNTYPE) VALUES('test value')");

 

See if it inserts. Also try

 

echo $db->error;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.