Hi Richard,

I've done export/import from FM to MySQL (which is used in many open source CMS apps) in the past but there are usually some hiccups due to the way data may be stored in FM. 

Try this if the CMS uses MySQL: 

1. Open your Filemaker database, export the fields you want to a tab-separated text file
2. transfer that file to the MySQL server (make sure you convert Mac line breaks to UNIX too, if applicable) 
3. import the text file using the LOAD DATA statement in the MySQL command-line client

If the CMS uses MySQL there may be some third party tools to do the conversion, as well. 

Bill 

-----Original Message-----
From: FileMaker Pro Discussions [mailto:[log in to unmask]] On Behalf Of Stephen Wonfor
Sent: Tuesday, April 19, 2011 1:17 PM
To: [log in to unmask]
Subject: Re: Advice I Don't Want To Give

Richard

I feel your pain.

You might be able to fake an export into flat file by doing something like this, where "this" is not = "flee".

#if you had a maximum related records value for each table you could write dummy data into variables to ensure that you would get the data aligned (Amount15 always after the 234th ∞ sign) for later use - $Table01Max, Table02Max, Table03Max, Table04Max... 

#locate a start table - eg, contacts - 
#go to first record
#Loop
	#define collector variables - perhaps one for each related table - $Table01, $Table02, $Table03 etc.
	#query each related table for data - get a related records count to use as a LoopStopCondition
	#write into appropriate variable and delimit it with some "never to occur character" - something from the high ASCII perhaps - I'll use ™ and ∞ and others in my example
	#You might even write the field name as well - eg.  ∞id#™08080∞Date™2/2/2010∞Amount™1125∞Notes™Never do this again∞id#™08080∞Date™3/28/2010∞Amount™555∞Notes™Send Cheese∞
	#repeat for each related table
	#join these collector variables into a large variable ($Table01&"∑"&$Table02&"œ"&$Table03)and then set that variable into a field in the "AaarghFlatFile" table (link by record id or something
	#reset all your variables = $$Null (which you never define)
	#GoToNextRecord (exit after last
#EndLoop

now you'll end up with a "flat table" of one field, many records filled with unusual delimiters.

Then perhaps some clever Substitute() to put in Char(9)s (append to your ∞ delimiters - not first and last) and then export the single field/many records as TAB separated text.
Then you convert this to an FMP database.  If your data has been suitably aligned you'll get the data in consistent fields - easier to validate.
Now you'll have clues as to how to rename F1, f2, F3...F∞ into useful Names -Amount1, Date1, Notes1, Amount2, Date2, Notes2...
Then you do another Substitute() frenzy to remove your ASCII markers, field names etc.
Then export as Excel (you will need to monitor total expected fields to the max in Excel - 16384 in '07 and '08) with first row field names, or as Merge CSV to get the field names.

I will have almost certainly made some errors in thinking this out but I may have done something approximately similar, or heard about someone doing it, or maybe just dreamed it.
It is clearly easier to visualize then to do, but often more helpful as well...
But the Char(9) export as Tab and convert back to FMPro I have done.

Present results to client and flee the country if need be...

Stephen

----------

"No sensible decision can be made any longer without taking into account not only the world as it is, but the world as it will be. " --- Isaac Asimov

On Apr 19, 2011, at 2:23 PM, Richard S. Russell wrote:

> A client asked me for advice on how to export data from a modest-sized file of <20,000 people. As we discussed what he wanted and why, he said he was looking for something he could upload to an on-line contact management system (CMS) that would replace his current FMP system. I thot this was kind of sad but doable. Then he got to the ugly bit. He's looking for flat-file output, because the people he'll have working on data entry in the future are too unsophisticated and ephemeral to deal with the complexities of relationality.
> 
> Let me spare you the effort you're about to invest in telling me what a terrible, terrible idea this is. I doubt that you could come up with anything I haven't already tried on him, to no avail. It's going to happen, with or without my help.
> 
> I explained that FileMaker Pro was kind of like a lobster trap. It's easy to get in (IE, move from a flat-file system to a relational one), because FMP has all sorts of tools to make it happen; but it's hard to get out (IE, move from relational to flat-file). I was kind of stumped right off the top of my head trying to figure out how I'd convert a portal of, say, 20 financial contributions, each with 8 fields (like amount, date, purpose, etc.) to 160 spreadsheet-style columns.
> 
> Has anyone else faced this kind of situation? Any advice (beyond the obvious "Don't do it!")?
> 
> 
> = = = = = =
> Richard S. Russell, a Bright (http://the-brights.net)
> 2642 Kendall Av. #2, Madison  WI  53705-3736
> 608+233-5640 • [log in to unmask]
> http://richardsrussell.livejournal.com/
> 
> = = = = = =
> No sense in being pessimistic. It won't work anyway.