[SOLVED] Importing bank records...

[forumnz]

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

[Psycho]

Except for the first few header lines it looks to be in a basic XML format. Just use an XML parser. Many tutorials out there.

[forumnz]

Thanks I'll give that a go

[the182guy]

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.

[forumnz]

What would you suggest please the182guy?

 

Thanks

Sam

[forumnz]

Oops it's QFX format.

 

Anyone?

[the182guy]

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.

[forumnz]

Thanks the182guy. That helps a lot.

 

How would I got about inserting the info into a database? Would I use a foreach statement or something? Sorry I'm not very familiar with this

 

Thanks heaps!

[the182guy]

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.

[forumnz]

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

[the182guy]

Looks like the connection to the database, failed. Did you get ther "connection failed" message also?

[forumnz]

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())

 

 

[the182guy]

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.

[forumnz]

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!

 

[the182guy]

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;

[forumnz]

Yes!!

 

It was a connection issue - thanks heaps for that the182guy!

It works!

 

Much appreciated

Sam

[the182guy]

No problem Sam