NYCPHP Meetup

[Mitch Pirtle]

I'm in a similar situation with one of my clients right now.  It is a 
complex financial site that gets daily feeds from a third party data 
provider.  Normally I'd enjoy this kind of work, but the politics of the 
situation mean that instead of getting direct flatfile exports from the 
provider, I have to wait on someone at the client to manually pull the 
same data down into (gasp!) excel spreadsheets (gasp! gasp!)

This data is complete bollocks.  Mismatched fields, constantly-changing 
formats, bogus values, you name it.  (CENSORED)

So I have written a PHP script (fired off by cron every night) that does 
all the work for me.  It was quite some effort building in all of the 
error checking and format conversions, but it was an excellent 
opportunity for me to work with PHP on the command line and also get 
some badly-needed experimentation with classes and objects.

So instead of being unhappy, I'm happy and just a little more edumucated ;)

Plus my database is in PostgreSQL (a manly-man's database), and I can 
have whatever schema I want as a result of the load script running 
'interference' between the two disparate datasources.  That is, if you 
could qualify Excel a 'datasource'!

Keep this in mind - although importing with a direct loading utility 
(like mysqlloader or whatever) is the best bet - and easily the best 
performing of the bunch - you may need more that just direct loading.  
It may be more important to have error checking and controls in place to 
ensure that your application does not fall prey to the "garbage in, 
garbage out" disease.  This would also give you the insurance that 
having a separate schema for your app will not cause discomfort with the 
original application, as you now have the chance to do some data 
transformation.  Besides, you certainly want to have some sort of error 
checking so you aren't left with an empty database if the automated load 
fails, right?

Also, a plug on ADOdb's XML data transformation services is in order, as 
you may have yourself the perfect opportunity to use this fascinating 
library...

    http://phplens.com/lens/adodb/docs-datadict.htm

I'm hoping to use it in Mambo Open Source 5.0, which will sport ADOdb 
under the hood and needs to support cross-platform database creation at 
install time...

-- Mitch