filepro to sql

[Raymond Scheel]

I've done something that sounds similar to what Tyler Style described so we can access our fP data directly from Oracle.  

I have a filePro process that will walk the maps of a given (imported) list of directory and key files to generate the "SQL" commands for Oracle to see those keys as read-only, non-indexed external tables.  Those external references can then be used to load a native Oracle table that you could modify or index using standard SQL operations. 

I built in validation to adjust any filePro field names than might be Oracle reserved words (I just added an underscore to any column names that matched exactly an exhaustive list of reserved words.  I start each of the reference tables with a 20 byte binary field to cover the fP record header.  When I catch duplicate column names when compared to a list I build as I go for each table, I append the field number, and for multiple word field names I replace spaces with underscores.  Once I had that worked out, I was able to build the link instructions for several thousand keys in seconds   Any other SQL based products that allow external table references (like pointing to a flat file) should allow similar access.  

Aside from the binary header column, everything else comes in as a character and we fix it during the process to load the native Oracle tables. With the idiosyncrasies of fP edit validation, data that spanned decades, and key files merged from over 100 separate installations, there was too much funky data to fix by myself and still get the bulk of the data available in a timely manner for others to program around the problems. (At my work there are several programmers who primarily do SQL and pretty much only me keeping the fP side taped together). Now that we have access to all of it without having to jump through an import-export process each time we want to refresh our source data, we use it to populate tables in a separate Oracle instance so we are not tied to the existing (often very flawed) fP data structure. 

Ray Scheel

On Wed, 22 Apr 2009 Richard Hane wrote:
> OK it's time for wierd question of the day.
>  Has anyone populated a SQL (either Microsoft or mysql) database directly
from filepro?  If not have about MS-Access?
>  I am pretty sure this could be done with Excel either dif or csv but I am
trying to do this seemlessly.