Export difficulties

[needhelp]

I have to export a copy of the data in a FileMaker database to another database but keep running into difficulties, any help would be much appreciated. I've never used FileMaker before so I fear I may have missed something that the rest of you consider obvious so please feel free to suggest such things if they sound like a possible solution

Problem 1. - hidden tables, the way the database has been configured, loading any table's file throws up a form/dashboard which only allows access to the table that matches the current tab on the form/dashboard, even clicking file/manage/database and looking under the tables tab only shows the one table (though the relationships tab shows more), unfortunately most of the tables do not feature on any of the tabs or even subtabs. As such we can't even use the export option on most of the tables to get any of the data out.

Problem 2. - Field truncation, the users of the database have used some fields for stuff they probably shouldn't have and as such the data gets truncated during export. Are there any ways to avoid this and get the data out intact? I can't recall if it truncated when exporting to csv but this file introduced a whole other set of problems due to no field headers and the fields being in a different order to in the table.

Problem 3. - The export type doesn't support repeating fields, again is there any way to avoid this and retrieve the data intact?

Problem 4. - "x" could not be opened. (Not Found), would I be correct in reading this as the user deleted a table but somehow the relationship between that table and another is still in the schema?

Any help would be much appreciated.

Thanks

From continuing to read the forums I get the impression that having the tables all in separate individual files is not the norm & that each file is usually an entire database, is this correct? If so would this explain some of the above?

Thanks

Edited January 21, 2011 by needhelp

[comment]

clicking file/manage/database and looking under the tables tab only shows the one table (though the relationships tab shows more)

Then the other tables are probably in other file/s (their titles will be in italics). If you hover over the dot to the left of the table's name, you will see this information.

In any case, if an external table has a layout in your file, you can export its records directly from your file - same as a local table.

The tabs on your "dashboard" are irrelevant here - you need to go to a layout of a table in order to export from there.

Problem 2. - Field truncation, the users of the database have used some fields for stuff they probably shouldn't have and as such the data gets truncated during export.

Some more details would be useful here, e.g. the field's type is ___, the field contains ___ and upon export we see ___.

Problem 3. - The export type doesn't support repeating fields,

I think you mean the target application cannot handle the way repeating fields are exported? You probably shouldn't be using repeating fields to begin with. There are ways to control the output during exporting, but they are not simple.

Problem 4. - "x" could not be opened. (Not Found)

What exactly is "x"? If it is "The file "x.fp7" ..." then that's what it means - the file containing the external table cannot be found (either deleted or moved to another location).

[needhelp]

Thanks for the input, I'll try clarify where I didn't give enough info:

Then the other tables are probably in other file/s (their titles will be in italics). If you hover over the dot to the left of the table's name, you will see this information.In any case, if an external table has a layout in your file, you can export its records directly from your file - same as a local table.The tabs on your "dashboard" are irrelevant here - you need to go to a layout of a table in order to export from there.

Gah! Found a way to navigate to them via the Window section of the toolbar! They simply don't show up at all in the listing under the manage database option. Thanks.

Some more details would be useful here, e.g. the field's type is ___, the field contains ___ and upon export we see ___.

The field type in FM is Text, and from what I can see the user was storing correspondence in it (not links to the correspondence, the actual content! ). This gets truncated on export and I was hoping there was some way to get it out intact.

I think you mean the target application cannot handle the way repeating fields are exported? You probably shouldn't be using repeating fields to begin with. There are ways to control the output during exporting, but they are not simple.

Quite simply when I select file/export and then proceed through to the export I get the message:

"This export type does not support repeating fields. Only the first item from each repeating field will be exported."

The other application hasn't yet gotten near the data, it's a issue with the file format itself. Is there a format that can take these and be read by some of the other database server types?

Unfortunately I didn't set up the database so there's nothing I can do to get rid of repeating fields and can only try my best (so far best < good enough ) to get the data out.

What exactly is "x"? If it is "The file "x.fp7" ..." then that's what it means - the file containing the external table cannot be found (either deleted or moved to another location).

Ah, that's pretty much what I thought. Thanks!

[comment]

"This export type does not support repeating fields. Only the first item from each repeating field will be exported."

Then you have selected to export as Excel? This would also be the likely reason why data is truncated: Excel has a limit on what a cell can contain (you should be seeing another warning about that when exporting).

In general, Excel is not a good intermediate format - try one of the plain text formats (tab-separated, CSV, Merge or XML) instead. This however, does not guarantee your target application will know how to handle the repeating fields.

[needhelp]

Then you have selected to export as Excel? This would also be the likely reason why data is truncated: Excel has a limit on what a cell can contain (you should be seeing another warning about that when exporting).

In general, Excel is not a good intermediate format - try one of the plain text formats (tab-separated, CSV, Merge or XML) instead. This however, does not guarantee your target application will know how to handle the repeating fields.

It was coming up for dbf too, I tried csv already and it loses the field headers and reorders the data from the fields => can't recreate the relationships between the tables in the copy of the database. XML looks to be the best option so, thanks for all the help.

[comment]

I tried csv already and it loses the field headers and reorders the data from the fields

The Merge format is CSV with headers. I am not sure what you mean by "reorders the data".

In any case, XML is the best of the bunch, if you know how to use it.