Jump to content
Sign in to follow this  
nicholaspeterson

Filemaker Novice, Exporting with ODBC

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.