FileMaker -to-> SQL Server Ruby Scripts

Ok, I've been playing with FM Pro 9 for the first time after about 3 yrs away from FM Pro... I had really wanted to use SQL Server as the backend, but ultimately FM Pro isn't there, that said there are a lot of analytics I want to do that aren't easy with FileMaker, but are with SQL Server.

Further, the ODBC driver for FM is somewhat poor.

Anyhow, I have started on some open source Ruby scripts that help you pull data from FM via ODBC and put it into SQL server (or for that matter any ODBC data source) for further analysis.

The gist of the solution is that I used a simple text file (YAML format) to describe the FM Pro database and my desired SQL CREATE format:

tables.yaml:

lmattertype:

    - [mattertype_id, int NOT NULL PRIMARY KEY]

    - [TypeText, NVARCHAR(255)]

    - [sortkey, int]

    - [abbrev, NVARCHAR(255)]



tDockets:

    - [docket_id, int NOT NULL PRIMARY KEY]

    - [matterfull, NVARCHAR(MAX)]

    - [family, integer]

    - [mattertype_id, int]

    # continues for ~50 more fields

This YAML file has enough for the Ruby script to SELECT against the FM Pro, then selectively DROP/CREATE the table on the SQL Server, and finally INSERT all of the data:

IF object_id('dbo.lmattertype') IS NOT NULL

                DROP TABLE [dbo].[lmattertype]



CREATE TABLE [dbo].[lmattertype] (



mattertype_id int NOT NULL PRIMARY KEY

, TypeText NVARCHAR(255)

, sortkey int

, abbrev NVARCHAR(255))



INSERT INTO [lmattertype] (mattertype_id, typetext, sortkey, abbrev) VALUES (1, 'Type1', 1, 'Abbrev1')

-- continues for many lines

Now, this does NOT handle synchronization or anything like that, but I think it is somewhat nifty and others might enjoy.

BSD license.

FM2SQLServer.zip