Part IV: Data Integration and Publishing
Chapter 19. Importing Data into FileMaker Pro
In this chapter
Working with External Data
FileMaker Pro can work with data from a variety of other sources. It's possible to bring data directly into FileMaker from a number of different flat-file formats, as well as from remote databases and XML-based data sources. In many cases, you can
Flat-File Data Sources
is a generic
The formats of flat files can vary. Some might separate one column from the next by tabs, and one row from the
Choosing the Target Table
As you can tell from the previous description, a flat data file maps well onto the concept of a database table. And indeed, in FileMaker Pro, we do import data into only one table at a time. FileMaker chooses this target table for you automatically, based on the prevailing table context.
Initiating the Import
We give the example of importing tab-separated data because it's a good example of a typical text-based flat-file format. Many of the other text-based formats vary from tab-separated text only in small details. We'll note those differences further on. Here we'll walk through the process of importing from a tab-separated text file.
Like other types of data, a tab-separated data file can be imported in one of three ways:
Importing and opening nonFileMaker files are very similar actions in FileMaker Pro. The main difference is that the "open" action creates a new FileMaker file (complete with data from the originating document), whereas the "import" action is used to bring data into an existing file. Importing can also be used to bring in images from a digital camera, or data from multiple files in a folderneither of these is possible if you use either variation of the
The Import Field Mapping Dialog
When you're importing data, after you've
Figure 19.1. FileMaker's Import Field Mapping dialog. All importing processes pass through this dialog at some point.
The Field Mapping dialog lists two filenames at the top, called Source and Target. Source is the file from which you're importing, and Target refers to the current table in the current filein other words, the one that's receiving the imported data.
Choosing an Import Action
One of the things you need to choose in the Import Field Mapping dialog is called the Import Action. It's visible in the lower left of the dialog box. This choice
Aligning Source and Target Fields
You also need to decide which fields in the target are to receive data, and from which source columns they'll receive data. Figure 19.2 shows the field structure for a FileMaker table designed to hold customer information. It consists mostly of text fields, with the exception of the
field, which is
Figure 19.2. The field structure for a basic table of customer information.
Assume that you want to import some data into this customer table. The source file is a tab-separated file containing first name, middle name, last name, address1, address2, city, state, and zip. (Notice the order is a little different from the field order on the FileMaker side.) To do this, you'd choose F ile, I mport Records, F ile. From the Show menu choose Tab-Separated Text, and then navigate to your file and select it. The result is shown in Figure 19.3.
Figure 19.3. Another look at the Import Field Mapping dialog. Note that the source and target fields don't quite line up correctly. Jack R. Balance is about to enter the system as Jack B. Royal.
If we look at the way the source fields line up with the target fields, something isn't right. We have a record for someone named Jack Royal Balance. But this record will be imported into the system as Jack Balance Royal if nothing is changed in the import order. In the FileMaker creation order, middle name comes after last name, but in the source file, it comes before. It's not possible to manipulate the ordering of the fields on the left (the source fields), but you can use the black
When you change the target field ordering by dragging a field manually, the field you drag changes places with the field you drop it on. Often you might want to drop the field you're moving between two others in the import ordering, so that it pushes all the fields underneath it down a step, but this is not how the manual ordering works.
Deciding Where the Data Goes
After all the target fields are correctly aligned with the source fields, you need to make sure they're all set to receive data. Between the columns of source and target fields is a column of field mapping indicators. The possible indicators are shown in the Import Field Mapping dialog, in the section at the lower right called Field Mapping, which is shown in Figure 19.4.
Figure 19.4. FileMaker's Import Field Mapping indicators.
The meaning of the different indicators is as
To sum up, you'll want to make sure that all your target fields are aligned with the correct source fields, and that the mapping indicators are set so as to allow data to flow into the fields you intend to receive it.
Ways of Auto-Aligning Source and Target Fields
In the Import Field Mapping dialog, you might have noticed a menu at the middle right called Arrange By. This menu simply governs the ordering of the target fields in the column on the right. It may be that you can line the target fields up with the source fields by
One very useful choice in this menu is the first one, called Matching Field Names. This choice is available only when the source file has some kind of data in it that attaches names to each of the source fields. Examples of such files are actual FileMaker files (of course), or flat data files with field names in the first row. If your source file contains field names that
This doesn't guarantee that the target fields will be able to accept data. If a source field has the same name as a field in the target table but the target field is defined as a calculation, the two will line up, but it will still be
Scanning the Data Before Importing
When the Import Field Mapping dialog first opens, the Source column shows data from the first record in the source file. You may find that the first record's data is not enough to
Directly under the source column, you'll notice forward-arrow and back-arrow buttons, and a display that shows the total number of inbound records, as well as the record you're currently viewing. You can use the forward and back arrow
Performing the Import
Assuming that there were no serious errors and at least some records were imported, the newly imported records are isolated in their own found set after the import is complete. This is an important point because if there's something seriously amiss with the imported data you have an opportunity to delete the whole set and start over. Or, more optimistically, the records are all there in one set if you need to perform any other operations on them as a
Updating Records with Imported Data
When you import data into a FileMaker Pro table, you have a choice as to whether the source data should be used to create new records, or whether it should be added into records that already exist. (You can also choose to import the data into an entirely new table. This feature, new to FileMaker 8, is discussed in more detail later.)
As an example, suppose that you have a FileMaker file with a table of records about people. This table contains a name, address, Social Security number, and other information about each person. Let's say that you periodically want to import the most current address for each person, from some other source outside of FileMaker, and apply the most current address to each of your FileMaker records, without changing anything else about the record.
Assume that your table of personal data looks something like the data shown in Figure 19.5.
Figure 19.5. Data structure for a table containing personal information.
Then assume that you can get a data file from some other source, possibly governmental, that contains (among other things) a field for Social Security number and a few fields of address information. You'd like to match up the records in the source file with the records in your FileMaker table. Two records will be
All these goals are easily accomplished with FileMaker's import options. Figure 19.6 shows the necessary settings in the Import Field Mapping dialog.
Figure 19.6. The Import Field Mapping dialog, preparing to import address data for records with matching Social Security
FileMaker's Update Matching Records feature can be tricky. For an overview of some of the potential pitfalls, see "Matching Imports" in the "Troubleshooting" section at the end of the chapter.
In addition to choosing the Update Matching Records setting, it's also been chosen to bring in just the address fields. So these particular settings update just the address information, leaving all the other fields untouched.
As a final note on update importing, you should be aware that the update affects only records in the current found set on the target side. If a record on the target matches a record in the source, but the target record is outside the current found set, it is not affected by the import.
You've probably noticed that another update option is available in the Import Action section. It's called Update Existing Records in Found Set, and it's simpler than the Update Matching Records choice. When this action is selected, rather than matching records based on a match field or fields, FileMaker matches records based purely on their position: The first record in the source updates the first record in the current found set on the target side, the second source record updates the second found target record, and so on.
If the number of records in the source doesn't exactly equal the number of records in the target found set, FileMaker takes account of this. If there are more source records than target records, the extra source records are
The only exception occurs if you check the box labeled Add Remaining Data as New Records. In that case, if there are extra records on the source side, they are imported into the target as brand new records.
All the flat file formats that are available for import into FileMaker have many similarities. They are
As you might expect, it's possible to import from other FileMaker Pro files. If you choose FileMaker Pro as your source format, you also need to specify a table in the source file from which you want to draw data. This choice is available in the Import Field Mapping dialog, as shown in Figure 19.7.
Importing from a FileMaker file can be particularly
For some other uses of the FileMaker-to-FileMaker import feature, see "FileMaker Extra: Exploiting the FileMaker-to-FileMaker Import," at the end of this chapter ( p. 592 ).
FileMaker Pro has some special capabilities for importing data from Microsoft Excel documents. FileMaker is aware of multiple worksheets within an Excel document, and is also aware of any
(a group of
After you've chosen the specific part of the Excel document you want to import, the rest of the import proceeds.
If you're bringing Excel data into FileMaker by choosing
pen, and selecting an Excel file to open, FileMaker creates a new FileMaker file, as it does when opening other "importable" file types. In this situation, FileMaker can apply a little extra intelligence to creating the new FileMaker file. If a column in the Excel file contains only one type of data (numbers, text, dates), FileMaker
When importing from an Excel file, FileMaker
FileMaker 8 has added a nifty new capability to imports. In previous versions, you could only import data into an existing table within a file. In FileMaker 8, you may instead choose to create an entirely new table at the time of import, and have the imported data flow into the new table. Figure 19.9 illustrates the new feature.
The new table will behave in many ways like a table created by choosing File, Open and opening the data source directly (see the discussion of this behavior earlier in this chapter). This feature is particular useful, though, when importing from another FileMaker table. In this case, the entire schema of the table, including things like calculation and summary fields, is re-created. (Note that information such as value lists, custom functions, relationships, and security privileges will not be imported because they are attached at the file level rather than at the table level.) The newly created table will be an exact copy of the old one, including things such as field IDs (important if you're re-creating this table as a way of consolidating two formerly separate FileMaker files).
For a full discussion of consolidating multiple tables, see "Repointing Table Occurrence References," p. 561 .
This capability, which is available in regular FileMaker Pro 8, is similar, though not identical, to the Import Table feature available only in FileMaker Pro 8 Advanced. The Import Table feature is limited to importing tables from other FileMaker files, but it can import many tables at once. Furthermore, the Import Tables function imports just the schema but no data, whereas with the Import As New Table feature, it's necessary to bring the data alongthere's no way to copy just the schema of a table using this feature.