Jump to content


Photo

[SOLVED] Importing bank records...


  • Please log in to reply
16 replies to this topic

#1 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 04:47 PM

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
www.designervision.co.nz

#2 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,861 posts
  • LocationCanada

Posted 01 May 2009 - 04:55 PM

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.
The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#3 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 05:14 PM

Thanks I'll give that a go :)
www.designervision.co.nz

#4 the182guy

the182guy

    Advanced Member

  • Members
  • PipPipPip
  • 611 posts

Posted 01 May 2009 - 05:26 PM

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.

#5 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 05:38 PM

What would you suggest please the182guy?

Thanks
Sam
www.designervision.co.nz

#6 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 05:57 PM

Oops it's QFX format.

Anyone?
:)
www.designervision.co.nz

#7 the182guy

the182guy

    Advanced Member

  • Members
  • PipPipPip
  • 611 posts

Posted 01 May 2009 - 06:11 PM

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.

#8 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 06:35 PM

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!
www.designervision.co.nz

#9 the182guy

the182guy

    Advanced Member

  • Members
  • PipPipPip
  • 611 posts

Posted 01 May 2009 - 06:53 PM

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.

#10 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 07:10 PM

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 :)
www.designervision.co.nz

#11 the182guy

the182guy

    Advanced Member

  • Members
  • PipPipPip
  • 611 posts

Posted 01 May 2009 - 07:17 PM

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

#12 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 07:20 PM

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


www.designervision.co.nz

#13 the182guy

the182guy

    Advanced Member

  • Members
  • PipPipPip
  • 611 posts

Posted 01 May 2009 - 07:29 PM

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.

#14 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 07:36 PM

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!

www.designervision.co.nz

#15 the182guy

the182guy

    Advanced Member

  • Members
  • PipPipPip
  • 611 posts

Posted 01 May 2009 - 07:42 PM

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;

#16 forumnz

forumnz

    Advanced Member

  • Members
  • PipPipPip
  • 735 posts

Posted 01 May 2009 - 07:53 PM

Yes!!

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

Much appreciated
Sam :D
www.designervision.co.nz

#17 the182guy

the182guy

    Advanced Member

  • Members
  • PipPipPip
  • 611 posts

Posted 01 May 2009 - 07:58 PM

No problem Sam  :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com