VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources] نسخه متنی

Importing Data from Text Sources

Some files from other applications (like Microsoft Excel) can be imported into FileMaker without much fuss. But to import data from most programs, you need to create intermediate files in a format readable by both FileMaker and the original program. (See the sidebar "About Data File Types" on page 286, and refer to your software's documentation for instructions on exporting files.)

Although text files are a universal output format, they don't usually contain field names. You have to determine field order either by looking at the data in the original program or by examining the data after you import it. Fortunately, FileMaker has a wonderful field-mapping feature right in the Import dialog box that allows you to match imported data to your existing FileMaker fields.

To import text data files

1 .

With the database you want to import into open, choose File > Import Records > File.

2 .

When the Open File dialog box appears, choose the format of the exported file from the "Files of type" (Windows)/ "Show" (Mac) drop-down menu. Navigate to the file and double-click to import it (Figure 12.1).

3 .If the export file includes field names, you'll see them in the Import Field Mapping dialog box (Figure 12.2). If not, you'll see the data from the first record (Figure 12.3). Click the field names on the right and move them up or down to match the data (or field names) on the left (Figure 12.4).

4 .For each field that you want to import, turn on the arrow in the column between the field names by clicking it. If there are fields that you don't want to import, click the arrow off (Figure 12.5). To help you locate all the match fields, examine the rest of the data in the import file by clicking the arrow buttons (Figure 12.6).

If you find information that doesn't match any current fields, click the Define Database button (Figure 12.7). This will bring you to the Define Database dialog box, where you can select the Fields tab and create a new field to hold the data.

5 .When you're satisfied that the field mapping is correct, click Import.

6 .In the Import Options dialog box, click the "Perform auto-enter options while importing" check box if you want the database to do lookups or add serial numbers to the new records (Figure 12.8). Click OK to start the import.

When the import is complete, the database will show the imported data as a found set. Check the data carefully. If you find that all the fields didn't match up properly, choose Records > Delete Found Records to delete all the imported records and try againFileMaker will remember the last field import order, and you can adjust it as necessary.

7 .

If the imported file had field names, the first record will display them. Delete this record (Figure 12.9).

Tips

If the data in a file you're importing doesn't match your existing FileMaker structure, give the information a thorough looking over in a spreadsheet program like Excel before you continue. Spreadsheet programs display tab-delimited data in columns. Each column corresponds to a field and each row to a record (Figure 12.10). You can insert a row at the top of the spreadsheet to create field names, making the match-up process a little easier (Figure 12.11). You can also make global search-and-replace changes, then save the data as an Excel file to retain the field names when you import.

For Windows users: If you're importing a text file from a Mac or an old DOS application, FileMaker can usually determine the correct character set automatically. If it doesn't, you can manually set the correct character set in the Import dialog box (Figure 12.12).

About Data File Types

Although FileMaker can import a variety of file types, the most common are text files. Not all word-processed document files are text filesin fact, most of them aren't. Text files are a special subset of files that contain only ASCII text. ASCII text contains only text, numbers, and basic punctuationno special characters and no font, format, or layout information. Because they're so simple, ASCII text files are readable on every type of computer, and virtually every application that works with data can produce text files. This universal readability makes text format the best choice for transferring data between different applications.

Although text files can't contain real formatting, they can carry some special charactersline return and tab, as well as punctuation like commas that can be used to separate fields of datathat make them useful for data transfer as well as simple alphanumeric text. Most programs that support ASCII text as well as their own formatting have an option for saving files with these two important characters (Figure 12.13). Some programs call this file type "Tab-delimited text," whereas others use "Text Only with Line Breaks." ("Delimited text" is another name for letters and numbers with tabsor commasseparating the fields and returns separating the records.)

Other common file formats you're likely to encounter when importing data are DIF and DBF, both of which FileMaker recognizes. DBF and DIF file names should end with .dbf and .dif, respectively.

DIF files are usually created by spreadsheets. Unlike plain text files, DIF files retain any field names they contain. DBF files are created in a format that dates back to the venerable grandparent of all modern databases, Dbase. Many database programs can both export and import DBF files. Like DIF files, DBF files retain the field name information.

Batch-Importing Guidelines

Although batch-importing is very easy, it can require some fancy mental footwork to be an elegant solution instead of a horrific muddle. Here are some important hints for importing multiple files:

You can't generate a brand-new database from the contents of a folder. The database already has to exist and have fields available for the new content.

All the items will be imported into the same field. Be sure to delete from the folder any files that belong in a different category.

Text files must all be simple text-only files. They can't contain embedded images or charts. Any formatting (like font or point size) won't transfer.

When you batch-import to update or replace data, records and incoming files have to match. If there are more files in a folder than there are records to hold them, the extra files will be ignored when the import takes place.

Be very careful when batch-importing replacement files. Create a found set of the records whose data you'll be replacing, and make sure that it is sorted correctly for the incoming files.

And most important: Unless it's a brand-new file, always make a backup of your database before you import, particularly if you'll be replacing or updating data, not just importing new files from scratch. The more material you import, the worse mess your file can become if you've overlooked something in your preparation.