Exporting data from FileMaker Pro for Sage 50

Sage 50 is a very popular accounting software for businesses and accountants in the UK mainly. Builting a FileMaker solution for business use, sometimes requires the need to interact at some point with the business’ accounting software. Here I present some key points to make this integration yourself. First of all Sage has an API on their website but this is beyond the usual use for FileMaker unless of course you want a full integration. The option that I would descibe, you would basically create an export file from FileMaker and import it into Sage.

Being a proporietory leading software for many years, Sage could not help itself from creating a proprietory file format for import into its software! This file format has an extension .imp and the specification can be found here.

Although it is an old document, it has not changed since. You could still use the version number that is stated in the file (12001). The rest is self explanatory in the document but here are some FileMaker caveats. Firstly, note that if you are using a Mac for developing a solution for Windows users, then take into account that the carriage returns should be followed by a line feed character. In other words just do a Substitute ( text ; Char( 13 ) ; Char( 13 ) & Char( 10 ) ).

The next important aspect is encoding. Apple and Microsoft have fought so hard to their worlds seperated and another way of doing that is through encoding. FileMaker Inc. as an Apple subsidiary could not help itself from confusing users and developers alike. FileMaker Pro has two  export script steps that you could use, one being the Export Field Contents and the other Export Records. The problem as previouly iterated is encoding, the latter step allows choice of encoding but the former only exports into UTF-16. This is not the encoding that we want as Sage 50 requires UTF-8.

Simple solution, use the Export Records function! That’s sufficient but what if you keep the text that you want to export in a single field? Which format are you going to export it to, tab separated, csv, etc.? FileMaker doesn’t ban you from exporting to another file type but which ever file type you choose it would keep adding some stuff. Another solution is at hand, exporting to XML format. But this gives rise to the other problem of using an XSLT file along with it. For an experienced XML programmer, this should be a very simple task but for the average Joe, its a hassle. Fear not, I have the solution at hand or more specifically, Beverly Volt  shows a way around this and explains all the nuts and bolts. At the bottom of the post you can download the sample which includes the XSLT file necessary to be used in the export step.

You are almost there. Take care with the decimals as Sage uses period. If there is any doubt in your solution, just use the Substitute function to replace comma with period. One final step is to make sure that at the bottom end tag line you put an extra carriage return for Sage to recognise the end of the file. And that’s it, you are now ready to export stuff from your FileMaker solution such as invoices, quotes, customers, vendors and timeslips that you can import into Sage.