Friday 24 July 2009

Ok - now this works

After about 12 hours, finally a set of instructions which work...

Kudos to TSGal on this forum - see his instructions second to last post.

Instead of downloading the Actual Technologies driver, you need iODBC framework and administrator which you can use instead of the ODBC Mac Administrator and this integrates nicely with Excel and Query.

I'll copy his instructions here for posterity and completeness:

1. I downloaded the iODBC Administrator at:

http://download.cnet.com/iODBC-Driver-Manager-Administrator-SDK/3000-2070_4-51225.html

2. I installed it, and it was placed inside my Hard Drive -> Applications -> iODBC (folder)

3. I put in my FileMaker Pro 10 disc, opened the xDBC directory and copied the SequeLink.bundle into the Hard Drive -> Library -> ODBC (folder)

4. I launched FileMaker Pro 10, created a new database file "Test.fp7" with one field (Name) and one record (TSGal)

5. I pulled down the File menu and selected "Sharing -> ODBC/JDBC..."

6. I turned on ODBC Sharing, selected "Test.fp7" in the lower left, and set ODBC/JDBC access to "All Users", and clicked "OK".

7. I launched iODBC Administrator and selected the tab "ODBC Drivers".

8. I clicked "Add a driver", and entered the following information:

Description of the driver: myODBCdriver

Driver file name: /Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk20.dylib

(Note: Be sure to manually type this in. Do not paste! )

[note from blogger: leave this as a user not a system driver i.e. leave the radio button as it is]


Click "OK".

9. Click on the System DSN tab, and click the "Add" button.

10. I entered the following information.

Data Source Name (DSN): myTESTDB1

a. Keyword: Host

Value: 127.0.0.1

(Click Add)

b. Keyword: Port

Value: 2399

(Click Add)

c. Keyword: ServerDataSource

Value: Test

(Click Add)

Click OK.

11. Now, you should be set up. Click the "Test" button. This should return everything was successful. If not, then let me know what is the error message, and we'll proceed from there.

Friday 17 July 2009

Real time Filemaker Pro querying from Excel for Mac 2008 via ODBC

(Note: This blog covers how to use Filemaker Pro as a data source for querying and analysing data real time from Excel)

You'd think it would be easy. Or at least that someone, ideally Filemaker, would write a comprehensive guide. Or even better than that, you'd hope that when you went to the Excel menu item: Data > Get External Data > Import from Filemaker Pro that the built in query feature would actually work returning reliable results.

The problem is that Filemaker haven't created the guide. And Excel fails to deliver. Its built in query feature simply doesn't return reliable results from a Filemaker Pro database.

Here's my scenario: We are working on a medical research project and Filemaker is used to capture, store, produce reports and do some basic summary analysis of the data. However, we really need the power of Excel and a statistical package like SPSS or R to do the real data crunching analysis that either Filemaker can't do or isn't the ideal interface for.

Now, we could choose to use Filemaker to export data out to Excel each time we want to do the analysis. But come on, we're in the the year 2009. Surely we can connect Excel directly to Filemaker and query the database real time, with a table layout in Excel which updates automatically as the data changes in the Filemaker Pro database? Then I only need one Excel spreadsheet set up which I can press 'refesh data' on and everything updates automatically. Nice.

Well, hurray (kind of) for the Mac team at Microsoft, who have provided us with an enticing option to query data directly from Filemaker, without the need to set up an ODBC connection.

The problem is, it doesn't work. Or at least not in the databases we're connecting to. There are two underlying problems:
1) 50% of the time Excel's built in querying function for Filemaker Pro on a .fp7 database (built in Filemaker 9 or 10) returns undescriptive errors e.g. object not found with no explanation or information to tell us why it hasn't worked. Perhaps it's where we've selected a container field in the dataset - but who knows? There isn't any documentation or error code.
2) Most importantly, in over a third of the dataset extracted, we get random data pulled into the columns from all across the database, which is completely inconsistent with the data actually stored in the related fields. Sometimes we even get some form or east asian script such as Chinese or Japanese characters displayed in cells which definitely don't exist in cell data within Filemaker.


After hours of playing around with both the database and Excel, we could not deduce any logical relationship between the data returned by Excel and the real data stored in the Filemaker Pro database.

If you can't trust the data returned by the method, you need to try something else.

Not ones to give up so easily, we decided to try the more traditional ODBC Microsoft Query route to gain realtime access to the Filemaker Pro database via the free Filemaker ODBC driver that comes on the Filemaker Pro CD.

So for those who need to (hopefully the following will save hours of time and frustration), here's how to create an ODBC connection between Excel for Mac 2008 and Filemaker Pro using Apple's ODBC Database Administrator.

[edit: this technique worked for me the first few times, now I can't connect and am trying to work out what changed between last week and now!]

Note: This should work for Leopard and Tiger.

Things you'll need:
- Filemaker Pro (installed on your Mac)
- Microsoft Excel 2008 (installed on the same Mac)
- ODBC Administrator (yes, it's an Apple App pre-installed in Applications > Utilities)

Note: You only need the trial version of the Actual ODBC Pack with Access and MySql ODBC drivers - It's not the actual drivers that are useful to us in this case, but thanks to Actual Technologies, you've got an easy packaged distribution and installation of the underlying iODBC framework. This framework effectively enables Excel to recognise the DSN you're about to set up in the ODBC Administrator.app and creates the link between Excel and Microsoft Query using the Filemaker Pro ODBC driver to connect Excel to your database.

The first couple of instructions here come straight from the Filemaker Pro documentation but I'll include them for sake of having everything in one place:

1) Install the ODBC Client Driver
From the folder /xDBC/ODBC Client Driver Installer (this is either on your Filemaker Pro CD or in your Filemaker Pro Application Folder), copy the file SequeLink.bundle to your Mac's main System library. If you don’t have an /ODBC folder, you'll need to create one manually.

So in summary: copy the SequeLink.bundle to /Library/ODBC

2) Install Actual Technologies ODBC Pack

Simply run the Actual Technologies ODBC Pack installer, accepting all the defaults.

3) Setting up your DSN via the ODBC Administrator

Open up Apple's ODBC Administrator.app in Applications > Utilities. Click on the Drivers tab.

Here you'll see the drivers that Actual Technologies ODBC Pack has installed, however as I mentioned before, we're not going to actually use their drivers, we're just piggybacking on the underlying iODBC framework that their installer has kindly installed for us.

First click on the padlock in the bottom left hand corner and authenticate. Then, go ahead and click on Add to set up a new driver.

Here we're going to be adding the Filemaker Pro SequeLink driver to the ODBC Administrator.

  • Type in a description. This can be anything, but note that you can set up a driver to be available to either a specific user or for the system. I haven't had any success when setting up a driver for the system, so I'm going to stick to the method that works here. As such, I've called my driver: FilemakerProUser
  • In the Driver file: box, enter in the following: /Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk20.dylib
  • Leave the Setup File: as blank
  • Define as: must be set as System not User
  • No keywords or values need setting for the driver so leave the rest blank and press OK.

Good, halfway there.

Now click on the User DSN tab at the top of ODBC Administrator and click on Add.

  • You'll get a drop down come up where you need to select the driver you just installed above. Select the driver, in my case FilemakerProUser and click OK.
  • Another screen will appear asking you to set a Data Source Name (DSN). Here's the relevant blurb from the Filemaker Documentation: You need to create a DSN for each FileMaker database file you want to access as a data source. The DSN identifies the FileMaker ODBC client driver, the location of the FileMaker host application, and the FileMaker database file you’re accessing as a data source. So in the DSN box, enter in a unique name for the database that you want to refer to it by. Two notes here, 1) it doesn't need to be the same as the name of the database itself 2) just to err on the side of caution, I'm entering in all the data lower case, no spaces or special characters.
  • Optional: fill in some text in the description field
  • Now the important bit, you need to create 5 Keyword-Value pairs as below, click on the Add button at the bottom left to add each set. (Note: If you're using Leopard, clicking on the ODBC keywords and values can be a little difficult to edit them. Just click once on the word itself, wait a second and it should change to be an editable field)
Keyword Value
Host localhost
Port 2399
ServerDataSource NAME OF YOUR DATABASE
Username
Password

  • Make sure the ServerDataSource is the exact name of your database and do not include any file path or .fp7 extension. (Filemaker Pro handles the actual location etc... via the File > Sharing > ODBC/JDBC... menu item. You'll set this up below.) Also if your filename contains spaces, read the below from the Filemaker Pro documentation: Type the filename of the FileMaker database file you’re using as a data source (don’t type the filename extension). If your database name contains spaces, replace them with the escape characters %20. For example, ServerDataDource=MY%20DATABASE. Double-byte characters are not supported.
  • Leave the username and password values blank as otherwise these are stored as clear text in an .ini file in your Mac's user account under ~/Library/ODBC.
  • Ok, all done, click OK and then Apply and Quit the ODBC Administrator.app
4) Enable ODBC Sharing in Filemaker Pro

Now open up Filemaker Pro and the database you want to share via ODBC.
  • Go to File > Sharing > ODBC/JDBC...
  • Turn on sharing
  • Select your database from the currently open files list and select either All Users or specify users in the radio button set.
  • Press OK, restart Filemaker and open your database
5) Connect Microsoft Excel
In Excel, open a new file and go to Data > Get External Data... > New Database Query...

If all goes according to plan, Microsoft Query should open up. Select User DSN from the tab at the top and select the DSN you created in the ODBC Administrator. Select your data source and press Test.

Enter in your username and password for your database, using an account which has ODBC Privileges enabled and hopefully you should get a connection.

If you do get a connection, congratulations, you can press OK and carry on through into writing a SQL query.

There are a bunch of anomalies such as underscore characters in file names which need to be escaped (I think by [_]) etc... which you need to be aware of but you'll come across them as time goes on.

My experience overall however is that the iODBC link between Excel, Query and Filemaker is pretty variable. The first time I set this up, it worked. The next time I tried, the iODBC set up box lost my details and I set up a new user DSN via MS Query. This worked once, but when I reopened my computer the next day I now can't connect.


Couple of pointers I've found so far:

  • If you get an error something along the lines of: SequeLink Client TCP/IP host is not specified - try going to terminal and entering: telnet localhost 2399 If you receive a connection refused there is a problem with Filemaker accepting ODBC connections, you're best off quitting FilemakerPro and restarting before you try anything else.
  • If your user connection disappears in Query's iODBC Data Source Chooser panel, even though it exists in ODBC Administrator just Add in the connection as explained above in the user section again by via MS Query instead of the ODBC Administrator. This seems to work on some occasions
  • If you're using Filemaker Pro 9 Advanced on Tiger, turning on ODBC sharing for me causes Filemaker to crash. Filemaker 10 on Tiger seems ok.