I like to use xml/xslt as I can control what field maps to the content. Then I use the 'match names' feature on actual import. Saves time!
The Import dialog has been the source of a fair bit of grousing for a while. I suggest submitting a feature request:
In the meantime, Beverly's suggestion works fine. If you give us some more specifics on your use case, maybe we can give some more good options.
What I often do is create a filemaker file specifically for importing data.
Essentially, what we're aiming to do is to create a table that contains both the source data fields and the destination table fields.
Here is what I normally do... use the source data to create an "in between" filemaker file - your field names from the source data need to be the same in the "in between" filemaker file. Excel format is obviously going to be easiest to create the "in between" filemaker file - since you can convert excel to filemaker very easily.
Then, using copy and paste (filemaker pro advanced required) - copy the field names from the destination table into the "in between" file.
If there are duplicate field names in the "in between" file, this means that there is already a field with the same name... remove the duplicate field names, then what you need to do is change all the newly pasted fields into calculation fields, so you might want to sort by creation order - change then to calculation fields that you've just added, which just simply equate to the corresponding field in the in between file (make sure the calculation result matches the field type).
That way, when you import from the source data into the "in between" file, use matching field names and when you import from the "in between" file into the destination file, you use matching field names. Easy peasy.
I know it's a fair bit of work to do this, however...this also has an added benefit of being able to "doctor" certain data within the calculation if needed.
This also means that data can be viewed (or should I say reviewed) in the "in between" file BEFORE it goes into your live data set, making it a safer - less intrusive process. - how many times have you imported a set of records only to delete that set because of one problem or another??
As feature request I already wrote a while ago:
"Some more for import/export
- not showing fields in which you can´t import at all
- optionally showing field comments so that users know which field is intended to do what
- error messages that help, not only denying the import
- saving import and export sets in the dialogues, not only by scripting
- free choice of field and record separators with preview for import
- free choice of file name extension - FM´s use of .mer etc. is not common at all
- instant search for fields
- a separator in the field list view like in Excel spreadsheets, so that you can see the first and the last fields at once
- clearer icons and symbols for what fields are mapped
- import and export for vcal and vcard formats
- free choice of text encoding like UTF8, UTF16, ANSI, ASCII everywhere
- the ability to mark the data part in an Excel spreadsheet if this has parts, that are no data to import
- the ability to import into different tables at once, for example a flat address file into an address and a telephone table."
As an expansion on the bridge file idea, if you're working strictly with FileMaker files, you can use relational joins between tables and script it. This allows you to specify field names in a field in a delimited fashion and use Set Field By Name to do the mapping. It's considerably slower (and thus may not be suitable for large data sets or over a slow network), but it is considerably more flexible. It's good in situations where you can't monitor the process or where you know the schema may change (such as versioning for a mobile app, for example).
The import dialog for WebDirect is nice.
Too bad we can't see how many thousands of times this was requested on the Feature Request page. Other development environments have open lists where developers can vote on thing like this to show interest rather than posting to a black hole.
>not showing fields in which you can´t import at all
maybe, but don't have so many fields where you can't import. perhaps better a way to sort by type (thus moving calc, container and summary fields to the bottom)?
>optionally showing field comments so that users know which field is intended to do what
maybe, because the names of the fields were not intuitive
>error messages that help, not only denying the import
>saving import and export sets in the dialogues, not only by scripting
>free choice of field and record separators with preview for import
>free choice of file name extension - FM´s use of .mer etc. is not common at all
use of variable name does not care what extension. I can use .mer type, but call it .csv in the variable used for the filename
>instant search for fields
>a separator in the field list view like in Excel spreadsheets, so that you can see the first and the last fields at once
>clearer icons and symbols for what fields are mapped
larger icons so we an hit them easier.
>import and export for vcal and vcard formats
these are text and can be calclulated. however a choice of delimiters would be helpful on export
>free choice of text encoding like UTF8, UTF16, ANSI, ASCII everywhere
>the ability to mark the data part in an Excel spreadsheet if this has parts, that are no data to import
in Excel, is this not the ability to set the print area?
>the ability to import into different tables at once, for example a flat address file into an address and a telephone table.
a one-one relationship, perhaps, only
On Oct 9, 2015, at 7:48 AM, intex wrote
Don´t want to hijack this thread by answering in detail to your comments.
maybe, but don't have so many fields where you can't import.
If it were that easy. Since you can´t have calculations on layouts like in Excel you will have calculations in your tables (I know you can calculate variables by script and place these on the layout). And it may make sense: Only one formula definition can be used on x layouts - no need for repetitions. The formula result can be exported.
There is just no sense in Filemaker showing calc fields in the import dialogue.
I admit to being confused as to the implications of the "<field missing>" placeholders.
I'm also disappointed (tho only mildly so, not badly handicapped) by the fact that the default field order for importing from other FMP files (by far my most common import source) is locked in as the time of field creation, rather than the carefully organized current order in my own Manage > Database > Fields list.
You can also set variables outside of scripts. It's very handy to eliminate the need for a lot of calc fields.
Interface elements such as Conditional Formatting and Hide Object can help, too.
Absolutely. Though I don't use CF for that anymore. Hide Object and Placeholder text calcs are much more efficient and reliable.
As feature request I already wrote a while ago: "Some more for import/export
A few that I've long wished for is:
- the ability to auto-match based on FM's internal field IDs. This would help with importing from a table in one file into a newer version of the file where fields have been renamed with more logical names (like when we take over a solution and then do a ton of work on it and later need to migrate live data);
- the option to slide groups of fields at once rather than just one at a time;
- ability to specify a calc as a field source on the spot (so instead of just importing firstName, for example, being able to specify "trim( firstName )" ).
Of course there are others from your list that I'd really like to see, as well, including (with my own clarifications):
- the OPTION of not showing fields in which you can´t or usually don't want to import to (calc, summary, global);
- error messages that help, not only denying the import (I've spent many, many hours trying to narrow down which field or which record is causing the error);
- instant search for fields (in both the source and target tables; or else a totally different way of mapping, like specifying both fields in each source-target combination using drop-down lists of all available fields instead of the current sliding.