Setting the import action and mapping fields during import

When you import data into an existing FileMaker Pro file, the Import Field Mapping dialog box appears after you select the file or source of the data to import. Use this dialog box to specify the following import options:

  • Import action: Specify how to import the data into the existing file. You can add new records to the file, update matching records in the file, or replace existing records in the file. See Methods of importing data into an existing file.

  • Field mapping: Specify where to import the data by mapping the fields in your target file to the fields in the source data that you're importing.

    You can import data from any field type into any compatible field except calculation fields and summary fields. You can only import into container fields if you're importing from another FileMaker Pro file or importing a folder of image, video, or PDF files. When you import data into a global field, the last item imported into the field from the source file determines the global field value.

To set the import action and map fields during import:

  1. If you have not done so, choose the file or source of the data to import into an existing FileMaker Pro file.

    Follow the instructions in Importing data into an existing file, Importing a folder of files all at once, or Importing XML data. The Import Field Mapping dialog box opens.

  2. Click Import action icon and choose the import action to perform.

  3. To

    Select

    Add new records to the end of the target file

    Add

    Note  If the table you're importing into contains no data, you can only select Add; the other options described below are not available because there is no data to update or replace.

    Update data in the target file with the data from matching records in the source

    Update

    Replace the found set of records in the target file with the records you're importing from the source, in order, starting with the first record in each file

    Replace

    For Update or Replace, select Add remaining data as new records to also add any additional records in the source as new records in the target file.

    Important  Updating matching records or replacing existing records overwrites data in the target file and cannot be undone. To safeguard your data, choose File menu > Save a Copy As to make a backup copy of the target FileMaker Pro file. You can do this only for a local file (located on your computer). Make sure you understand how updating or replacing records works. See Importing data into FileMaker Pro.

  4. If you're importing data from another FileMaker Pro file and that file contains multiple tables, choose the table you want to import from the Source list.

  5. If a record in the source data you're importing contains field names (column headings) instead of data, click Backward arrow or Forward arrow until the record containing field names appears. Then choose Use as Field Names.

    Only the subsequent records will be available to import.

  6. If the target file contains multiple tables, choose a table from the Target list.

    Or, choose New Table. See About creating a new table for imported data.

  7. For Target Fields, choose a target field to indicate where source data will be imported or updated.

    You can do any of the following to map target fields to source fields:

    • To change a selection, click the current target field name and choose another field from the list. To find a field in the list, type a few characters of the field name in the search box. An indicator next to the target field name in the list shows whether the field is available to map.

    • Field mapping indicator

      Description

      Unmapped target field icon

      Not currently mapped to a source field

      Mapped target field icon

      Currently mapped to a source field

      Match target field icon

      Currently mapped as a match field

      Invalid target field icon

      Can't be mapped to the source field because the target field is an incompatible field type or you don't have sufficient access privileges

      Match fields in the target file cannot be container, summary, or global fields. The field type of match fields should correspond to the type of data in the matching field. For example, number fields should not be matched to fields containing text. If the import action is set to Update, target fields that can be used only as match fields appear in the list under For Matching Only. Choosing one of these fields sets the mapping to Match Field.

      Target fields that can't be mapped to source fields appear in the list under Not for Importing. You can't import source data into these fields or use them as match fields.

    • To preview the data you're importing, click Backward arrow or Forward arrow.

    • To create or change fields in the table you're importing into, click a target field name then Manage Database button, or in the Target list, choose Manage Database. In the Manage Database dialog box, make your changes, then click OK. (If the button is dimmed, either you don't have sufficient access privileges, or the database is shared over a network and does not currently permit database changes. See Sharing files on a network.)

    • To reorder the target fields relative to the source fields all at once, choose one of the following options from the menu in the Target Fields column:

    • Choose this option

      To list the fields by

      Matching Names

      Field names that match in both the target table and the source

      FileMaker Pro matches field names dynamically each time you choose this option. (This option is not available when the source data does not contain field names.)

      Last Order

      The order used the last time FileMaker Pro imported data into this table or the default order (creation order)

      Creation Order

      The order in which the fields were created

      Field Names

      Alphabetical order

      Field Types

      Field type

      Custom Order

      A custom order (which you create by changing target fields)

  8. In the Mapping column between each pair of fields in the list, click the current mapping and choose the field mapping to perform.

    You can choose whether or not to import each field into the target field, or use a field pairing as a match field for updating matching records.

    Note  Data from a source field will not import when:

    • Mapping is set to Don't Import. For each source field you want to import, set Mapping to Import and choose a target field. Create a new target field if necessary.

    • Invalid Target appears between the source and target fields, which indicates that the target field does not support the source field data. You cannot import data into calculation or summary fields, or import some mismatched data types such as container data into a text field. (Invalid Target may also indicate that access privileges do not permit importing data into a particular target field.)

  9. For target fields in which automatic data entry (auto-enter) has been defined, choose whether to perform auto-enter options while importing (for example, to enter the date in a Modification Date field).

  10. To auto-enter new data in fields defined with auto-enter options

    Do this

    For individual target fields

    Click Perform auto-enter options per field icon (deselected) next to a field in the Target Fields column and select Perform auto-enter options. Perform auto-enter options per field icon (selected) indicates that the option is selected. Otherwise, auto-entered values will not be entered for this field.

    Target fields that aren't currently mapped don't appear in the Target Fields column. To set this option for any target fields with auto-enter options, in the Import Options area, click Perform auto-enter options per field icon and select fields.

    For all target fields

    In the Import Options area, select Perform the auto-enter options for fields during import. Otherwise, auto-entered values will not be entered for all target fields.

    Note  If you select this option then deselect the option for some individual target fields, this option will indicate a mixed state. To see all the target fields that affect the state of this option, in the Import Options area, click Perform auto-enter options per field icon.

    Note  When you're importing into fields that are set to auto-enter a value (like modifier name, modification date or time, or lookup data), choosing this option overwrites the data you're importing with auto-entered data.

    See Defining automatic data entry.

  11. In the Import Options area, choose additional options if necessary.

  12. To

    Do this

    Choose the character encoding used in the source file

    For Character set for source file, choose a character encoding.

    The character set list is either dimmed or available.

    • If the list is dimmed, FileMaker Pro determined the encoding of the file and chose it automatically.

    • If the list is available, FileMaker Pro could not determine the file encoding, and chose an appropriate character encoding based on your operating system and the file format you're importing. If you choose a different encoding, FileMaker Pro scans the import data, and displays an alert message if the data contains illegal characters.

    Note  If you're importing a 16-bit Unicode (UTF-16) text file, the file must contain a Unicode standard Byte Order Mark (BOM). If the BOM is missing, FileMaker Pro will not properly recognize the file encoding.

    Specify the field delimiter used in a source file that is in custom-separated values format

    For Field delimiter for source file, choose the character from the list (or choose Other and type the character) that separates field values in the source file.

    This option is available only if you chose Custom-Separated Values for Files of type (Windows) or Show (macOS) in step 1.

    Suppress validation checking of container field contents during importing, to allow the target file to use existing external container field contents

    Select Preserve external container storage.

    Use this option, for example, to quickly import data into a cloned copy of a file without having to decrypt and re-encrypt files to move container data to the target file.

    The base directory of the source and target fields must be the same.

    Specify how to import data from repeating fields in the source file

    For Repeating Fields, select:

    • Keep values in the original record to maintain the field(s) as repeating fields.

    • Split values into separate records to import each repeating value into a separate record. (Use this option to work with individual values in repeating fields, such as to sort or summarize them.)

    Note  When you split repeating field data into separate records any non-repeating fields that you import are duplicated in each separate record. For example, if a record in the source file has values in three repetitions, splitting them into separate records imports three records, each identical except for the values in the repeating fields.

  13. Click Import.

    Note  If you're importing a large amount of data, you may see an Importing dialog box that shows the progress of the import process. To stop importing, click Stop. Records that were already imported remain in the target file (the import is not reversed). To permanently delete the records already imported, choose Records menu > Delete All Records.

  14. In the Import Summary dialog box, note the number of imported records and any data that was skipped. Then click OK.

    The Import Summary dialog box displays three statistics detailing the imported records and any skipped data:

  15. Statistic

    Description

    Records added or updated

    The total number of records added or updated in the target file during the import process.

    Note  This number will include multiple updates of the same record if you're updating matching records and more than one record in the source file matches a record in the found set of the target file. See the Notes section.

    Records skipped because of errors

    The number of records in the target file that were skipped because of:

    • Privilege errors: Access privileges prohibit modifying one or more records.

    • Locked records: One or more records cannot be modified because they are being accessed elsewhere, either in another window by the same user or (if the file is shared) by other clients.

    • Validation errors: One or more records cannot be modified because record-level field validation requirements are not met, and the record could not be committed. See About validating data during import.

    Fields skipped because of errors

    The number of fields in the target file that were skipped because of:

    • Validation errors: One or more fields cannot be imported because the data does not match the validation requirements defined for the field. See About validating data during import.

    • Privilege errors: Access privileges prohibit modifying one or more fields.

    Skipped fields do not prevent the updating of other fields in the same record.

Notes 

  • The records you import become the found set. After importing, check the data in the found set. Choose Records menu > Show All Records.

  • If you're updating or replacing records, be sure your records are correct before discarding the backup copy of the file.

  • If all specified match fields in a record are empty, that record will not be updated.

  • If a record in the source file matches more than one record in the target file, all matching records in the found set of the target file will be updated.

  • If more than one record in the source file matches a record in the found set of the target file, the target file will be updated with the last matching record in the source file. (During the import process, the matching record in the target file is actually updated multiple times—once by each matching record in the source file—so the last matching source record to update the matching target record determines its final content.)

  • The found set after an update contains the records that were updated (those that matched) and any new records added, regardless of the found set before update.

  • To set the field mapping for multiple fields, Ctrl-click (Windows) or Command-click (macOS) each row in the Source Fields column, or Shift-click a range of rows. Then click the current mapping for one of the selected fields and choose a mapping. (You can also press the Space bar, use the Tab, Up Arrow, or Down Arrow, then press Space or Return to choose the field mapping.)