Flat-File Data Sources

Flat file is a generic term that refers to a file containing data in row-and-column format. If you think of a spreadsheet that holds data about personal contacts, the spreadsheet will have some number of columns, for attributes such as first name, last name, address, and so forth, and some number of rows, each one representing a single contact.

The formats of flat files can vary. Some might separate one column from the next by tabs, and one row from the next by carriage returns (a tab-delimited file). Another might use commas to separate column values. Some might include a first row that gives a name for each column. Some might be in a plain text format that you could read with any text editor, whereas others might be in specialized file formats (such as FileMaker Pro or Microsoft Excel). In general, though, all flat-file data sources represent some variation on the idea of row-and-column data.

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.

Current Table Context

The current table context is determined by the active layout. You can examine the table context for a given layout by choosing View, Layout Mode, and then choosing Layouts, Layout Setup and inspecting the Show Records From menu. Be sure to switch back to Browse mode before trying to import records, though.

For a full discussion of table context, see "Understanding Table Context," p. 164.

 

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:

• Choose File, Import Records, then navigate to the file and select it.
• Choose File, Open, then navigate to the file and select it.
• Drag and drop the file directly onto the FileMaker Pro application.

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 Open command.

The Import Field Mapping Dialog

When you're importing data, after you've chosen your source file you'll be presented with the Import Field Mapping dialog box, shown in Figure 19.1. This dialog lets you choose how the records in your source file will be imported, and in what order.

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 tells FileMaker whether to try to add new records in the target table (one record per row of source data), or whether to try to update the existing FileMaker records with the source data. Updating on import is a topic in its own right, which we deal with later in this chapter. For now, we'll cover what happens when we want to create new FileMaker records based on the source data.

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 PhoneDisplay field, which is meant to create a formatted display from the AreaCode and Phone fields.

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 File, Import Records, File. 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 up-down arrows next to each target field to change the target ordering manually. In this case you'd just drag the Name_Middle field up one line to make it change places with Name_Last.

Caution

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 follows:

• Arrow Data from the source field will be imported into the target field.
• Straight line Data from the source field will not be imported into the target field.
• Equal sign The source and target fields are being used as part of a match criterion. This choice is available only if you've chosen one of the update import actions. We discuss the update options fully in the following section (see "Updating Records with Imported Data").
• Red x This indicates that the target field cannot receive data. Typical causes are that the target field is a calculation or summary field.

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 putting the target fields in creation order, for example, or in alphabetical order by name. If you choose one of these options, FileMaker rearranges the target fields in the order you've chosen, and then does its best to set the mapping indicators accordingly. Most likely you'll need to do some manual adjustment of the result, but these choices can often eliminate a lot of tedious hand labor.

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 correspond to the names of target fields, you can choose this arrangement option and all the fields with identical names will simply line up, no matter what position they have in their respective files.

Note

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 impossible to import any data into the target field (you cannot import into calculation fields).

 

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 remind you of the appropriate field mapping, or you might want to scan through the source data for other reasons.

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 buttons to scan through the inbound data, either to verify that you have the correct mapping of source to target, or to examine it for other reasons.

Performing the Import

After you've verified all your field mappings and made your choice of import action (so far we've looked only at adding records), pressing the Import button starts the import proper. When the import completes, FileMaker displays a dialog box telling you how many records were imported, and whether there were any errors in the import process.

Depending on how you have your field validation set up, the inbound data may or not be acceptable. Under certain circumstances, FileMaker may reject imported records for this reason. See "Imports and Validation" in the "Troubleshooting" section at the end of this chapter for more information.

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 group, such as a batch Replace operation.

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 considered to match if they have the same Social Security number. If there's a record on the FileMaker side that doesn't have a match in the source file, you'd expect it to be left alone. If there's a record in the source file that doesn't have a matching FileMaker record, you'd want to ignore the source record altogether.

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 numbers in the source.

Here the action Update Matching Records In Found Set has been selected. This tells FileMaker that you're going to specify at least one pair of fields as matching fields. This pair of fields acts a lot like a match field in a FileMaker relationship: Each row (or record) in the source is matched with any corresponding records in the target.

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.

Updating Records Without Using Match Fields

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 skipped. If there are more target records than source records, the extra target records are left untouched. In either case, FileMaker provides an extra message to tell you what happened.

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.

Special Flat File Formats

All the flat file formats that are available for import into FileMaker have many similarities. They are generally text-based, row-and-column data files, suitable for import into a single target FileMaker table. Two of the possible formats are worth special mention.

Importing from Another FileMaker Pro File

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.

Figure 19.7. When importing from a FileMaker database with multiple tables, it's necessary to pick the source table from which you want to draw data.

Importing from a FileMaker file can be particularly convenient in that it allows you to use the Matching Field Names option for lining up the source and target fields. Developers will often choose to open a source file within FileMaker, creating a new FileMaker file based on the originating document, and then use that new FileMaker file for importing, data cleanup, and so on.

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).

 

Importing from a Microsoft Excel File

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 named ranges (a group of cells that's been given a specific name). When you select an Excel file for import, if it contains multiple worksheets or named ranges, FileMaker prompts you to select either a worksheet or a named range as the source for the data, as shown in Figure 19.8.

Figure 19.8. When you import data into FileMaker from an Excel document, you can import from a specific tab or a named Excel range.

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 File, Open, 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 assigns a suitable field type to the resulting FileMaker field. If the data in the column are somehow "mixed"that is, the column contains some data that look like numbers, and other data that look like dates, for examplethen the resulting FileMaker field will be a Text field.

Note

When importing from an Excel file, FileMaker brings in only the raw data it finds in the file. FileMaker does not import Excel formulas, only their results. FileMaker also does not import any graphics or charts, nor does it import notes. Programming logic, such as Visual Basic macros, is also not imported into FileMaker.

 

Using an Import to Create a New Table

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.

Figure 19.9. FileMaker 8 enables you to create a new table from imported data.

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.