Filemaker Novice, Exporting with ODBC

[nicholaspeterson]

Hello, I've recently been tasked with pulling information out of filemaker for a data migration to another system, and thought ODBC was a natural fit, after working around some initial problems, I'm able to query my filemaker database reasonably well, with one large problem.

Certain fields in filemaker have a datatype that doesn't seem to match the actual values present in the field, which in turn angers the ODBC driver when submitting a query. A good example is an invalid date format, which stops the export process (in SQL Server Integration Services) dead in its tracks.

It's not possible to cleanse the data in the source system since the fields are extremely unrestricted and will quickly go bad again, and recoding the custom filemaker UI is outside the scope of this project. This migration will take place over several months so the hope is to pull data from a backup nightly and insert it into a MS SQL database.

I've noticed it's easy enough to go into 'Manage Database', and simply change the columns to a type of 'text' and then programatically parse into a valid format in SSIS.

What I'd want to know is

1) Can I write a filemaker script that changes the datatype of a field and applies the change?

2) If no to number 1, can I in any other way intercept these bad values quickly without cleansing the source data? Please note that many of these filemaker tables have millions (yes, millions) of rows.

System Info: I'm working with a backup of a Live Filemaker 8 Database, hosted on a local copy of Filemaker 10 Advanced

[mr_vodka]

The answer to 1 is no and the answer to 2 is that you could put field level validation on the fields to make sure the data in entered correctly going forward. That being said, whether you clean up the existing invalid data within FM or via text field and then on the backside, the point is you still have to clean in it up with the proper data typing format.