Converting Sage Data to MySQL
Recently I came across a post by Rick Vause demonstrating a simple method for syncing your financial data from sage up onto your webserver. The advantages of this are plenty, most frequently this is required to show live stock and financial data on a web application.
I say simple, because the concept really is obvious once you read the code.
His post is here: sagetomysql
The process is easy to understand:
- Setup a DSN to the sage data, using the sage tool.
- Write a PHP script to connect to the DSN using PHP’s ODBC
- Add a MySQL connection to your PHP script.
- Tell ODBC which table you want to copy across
- Loop through the field names and field types with PHP’s odbc_field_name and odbc_field_type
- Allow the PHP script to create the duplicate table in your MySQL database.
- Copy the data across using php, updating records if this is not the first run
This script works fine for a single table, but it can be improved using odbc_tables().
We can eliminate the process of specifying the sage table name, and get all of the data online in one go. odbc_tables allows us to loop through every table in the Sage data source and pull them through into our initial database.
I’ve made the required modifications to Rick’s script and uploaded it here:
It’s currently untested, but I will be using it in some projects soon, so any required updates will be made then.
It’s worth noting that due to PHP’s large database support, it’s possible to convert it to any of the supported databases just by changing some of the function names used and confirming the CREATE TABLE syntax in those.
With some small modifications to use PEAR::SpreadSheet_Excel_Writer it could even just dump all of the data to an excel file – not very practical, but cool to know it can be done.