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 existing records in the file, or update matching 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 database to the fields in the source data that you’re importing.
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.
2. 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.
3. In the Import Action area, choose the type of import to perform.
Important Updating existing or matching records overwrites data in the target file and cannot be undone. Choose
File menu >
Save a Copy As to make a backup copy of the target file. Make sure you understand how updating records works. See
Methods of importing data into an existing file.
To | Select |
Add new records to the end of the target file | Add new records Note If the table you’re importing into contains no data, you can only select Add new records; the other options described below are not available because there is no data to update. |
Update 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 | Update existing records in found set To also add any additional records in the source as new records in the target file, select Add remaining data as new records. |
Update data in the target file with the data from matching records in the source | Update matching records in found set To also add records from the source that do not have matching records in the target file, select Add remaining data as new records. |
4. If the first record of the data you’re importing contains field names (column headings) instead of data, select Don’t import first record (contains field names).
Selecting Don’t import first record (contains field names) skips the first record during import.
5. For Target Fields, drag fields to align with fields or data in Source Fields, to indicate where source data will be imported or updated.
To align a field:
•Windows: Move the pointer over the
icon next to a target field name in the list. When the pointer changes to a double arrow
, drag the field to a new location.
•OS X: Move the pointer over a target field name in the list. Drag the field to a new location.
You can do any of the following to align target fields and source fields:
•To preview the data you’re importing, click
or
below the fields lists.
•To create or change fields in the table you're importing into, click
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 and hosting files.)
•To reorder the target fields all at once, choose one of the following options from the Arrange by menu:
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 | |
Custom import order | A custom order (which you create by dragging field names). |
Note Data from a source field will not import when:
•The source field is not aligned with a target field. Drag a target field to align with each source field you want to import. Create a new target field if necessary.
•The
symbol appears between the source and target fields, which indicates that the target field type 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. (The
symbol may also indicate that access privileges do not permit importing data into a particular target field.)
6. In the space between each pair of fields in the list, click the field mapping symbol one or more times to indicate the field mapping you want performed.
Continue clicking until the symbol between the fields indicates the field mapping you want. For a description of each symbol, refer to the
Field Mapping area. 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.
Tip You can also set the field mapping for multiple fields at the same time. First select the target fields you want to change by holding down Ctrl (Windows) or Command (OS X), and clicking each target field. (You can also select a range of adjacent fields by clicking the first field and then Shift-clicking the last field in the range.) Then click a field mapping symbol for one of the selected fields until they all indicate the mapping that you want. (You can also press the Space bar one or more times to change the field mapping symbol.)
7. If necessary, for
Character Set, choose the
character encoding used in the source file.
The Character Set list may be dimmed or available:
•If the list is dimmed, then FileMaker Pro was able to determine the encoding of the file and picked it automatically.
•If the list is available, then FileMaker Pro was not able to determine the file encoding, and has tried to pick an appropriate character encoding based on your operating system and the file format you’re importing. You may need to change the encoding that FileMaker Pro picked. If you pick a different encoding, FileMaker Pro scans the import data, and will display an alert message if the data contains characters that are illegal in the character set you selected.
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.
8. Click Import.
You may see the Import Options dialog box, which appears when:
•the target file has fields for which automatic data entry (auto-enter) options have been enabled
•you’re importing and adding new records from a FileMaker Pro file with
repeating fields 9. If the Import Options dialog box appears, select one or more of the following options, and then click Import.
To | Do this |
Auto-enter new data in fields defined with auto-enter options. (For example, to enter the date in a Modification Date field.) | Select Perform auto-enter options while importing. Otherwise, auto-entered values will not be entered. 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), clear this option unless you want the auto-entered data to overwrite the data you’re importing. |
Specify how to import data from repeating fields in the source file | Select: •Keeping them in the original record to maintain the field(s) as repeating fields. •Splitting them 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. |
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. |
10. In the Import Records Summary dialog box, note the number of imported records and any data that was skipped. Then click OK.
The Import Records Summary dialog box displays three statistics detailing the imported records and any skipped data:
Statistic | Description |
Total records added/updated | The total number of records added and 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 in Methods of importing data into an existing file. |
Total records skipped due to errors | The number of records in the target file that were skipped due to: •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. |
Total fields skipped due to errors | The number of fields in the target file that were skipped due to: •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
•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.
•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 records, be sure your records are correct before discarding the backup copy of the file.