Does anyone have experience exporting / migrating data from a Filemaker Pro DB?

On 2/3/2012 4:12 PM, Jim Garrity wrote:
> Does anyone have experience exporting / migrating data from a Filemaker Pro DB?

I did it once by exporting Filemaker tables to CSV (a built-in option),
opening the CSV in Excel, saving Excel as DBF, and using EDB's migrator
to import the DBF.  Not pretty, but it was good enough for small tables.

Jim,

<< Does anyone have experience exporting / migrating data from a Filemaker
Pro DB? >>

If they have an ODBC Driver, then you should be able to use the ADO migrator
to migrate the database over directly.

--
Tim Young
Elevate Software
www.elevatesoft.com

>>Charles Tyson wrote:

>>On 2/3/2012 4:12 PM, Jim Garrity wrote:
>>> Does anyone have experience exporting / migrating data from a Filemaker Pro DB?

>>I did it once by exporting Filemaker tables to CSV (a built-in option),
>>opening the CSV in Excel, saving Excel as DBF, and using EDB's migrator
>>to import the DBF.  Not pretty, but it was good enough for small tables.

Note that EDB has really good SQL Commands for "IMPORT" and "EXPORT"

IMPORT TABLE <ImportTable>
FROM <FileName>
IN STORE <StoreName>
[(<ColumnName>[,<ColumnName>])]
[FORMAT DELIMITED|XML]
[ENCODING AUTO|ANSI|UNICODE]
[DELIMITER CHAR <DelimiterChar>]
[QUOTE CHAR <QuoteChar>]
[DATE FORMAT <DateFormat>]
[TIME FORMAT <TimeFormat> [AM LITERAL <AMLiteral> PM LITERAL <PMLiteral>]]
[DECIMAL CHAR <DecimalChar>]
[BOOLEAN TRUE LITERAL <TrueLiteral> FALSE LITERAL <FalseLiteral>]
[USE HEADERS]
[MAX ROWS <MaxRowCount>]

These commands make a couple of the steps that Charles mentions above unnecessary.

If you can get the data into a well-delimited CSV format (which should be easy from any Db format) then you can import it using these commands provided that table field structures match.

I have done medium-sized jobs of this type, but not from Filemaker. The big gotchas:

* CSV doesn't carry over INDEXES or any other more complex data parameters.
  ... to capture this I use DeZign by Datanamic ... open your existing project there &
  then convert it to EDB, then save a SQL CREATE script for EDB, that gives you
  all constraints & indexes.

* With many DBs there are nasty mis-matches in datatypes (i.e. Interbase "Date" field
   type in older versions) ... sometimes data being ported across barfs or is nulled.
   Note in the above SQL the "TIME FORMAT" options ... you may have to get to
   know how these work

* Complex BLOB data (images and longer text) can be horrible. I have had to use Delphi
   to export image-files from a DB into a folder with filename = recordID, and then write
   code to copy these files back in to Image fields once the conversion process is
   complete (!)

... my main comments:

* Don't be afraid to use Delphi extensively to "work over" your source data to get it into
   shape prior to an export / import  process.

* Keep plenty of backups of the key stages of "cleaning" so if you screw up you can
  easily unwind.

* Ensure you have some good check sums on the original data so you can be totally sure
   that what arrives in the destination database actually matches the original.

After saying all that the process can be done & isn't too difficult