Export File Formats

FileMaker's Export Records feature can create export files in various formats. Many of these are text-based, and a few are binary. In this section we give an overview of available file types, with some specific notes on each. Each format has its own quirks and limitations. We attempt to call out the main features of each format, but you'll need to experiment to see just how a specific data set translates to a chosen file format.

Character Transformations

When exporting data, FileMaker often performs substitutions on certain characters that tend to cause confusion when they appear embedded in field contents. For example, FileMaker permits you to embed a tab character in field data, but because the tab character is frequently used as a field separator in text-based data, FileMaker transforms these internal tabs to spaces when exporting. In the same vein, carriage returns within fields sometimes get transformed to the vertical tab character (ASCII code 11). The specific transformations that occur depend on the output file format; see the notes on each format outlined in the following sections for further details.

Caution

The transformation of carriage returns to vertical tabs is a significant problem if your data will need to be handled as XML along the way because the ASCII 11 character (vertical tab) is not a valid character in XML. When you export data as XML, FileMaker does not make this transformationit simply eliminates the internal carriage returns. But if you export in a non-XML format, yet need the exported data to be processed via XML at some point, be aware that these embedded vertical tabs will cause the file to be rejected by XML parsers.

One other common transformation occurs when repeating fields are exported (for those formats that support it). Multiple repetitions of a field are often exported with the individual repetition data separated by the group separator character (ASCII code 29). Common transformations are listed in Table 20.1.

Table 20.1. Character Transformation Information for Exporting FileMaker Data

Character	Transformation
Tab-separated text	One of the most common data interchange formats, the tab-separated text format exports each record as a single line of text, terminated by a carriage return. The contents of individual fields are separated by the tab character. The repetitions of repeating fields are run together into a single string, with repetitions separated by the group separator character (ASCII code 29).
Comma-separated text	Comma-separated text (or values, commonly referred to as CSV) is another very common text interchange format. As with tab-separated text, records are separated by carriage returns; but individual records are separated by commas, and field contents are enclosed in quotation marks. (Quotation marks already present in the data are turned into pairs of quotation marks, so "data" becomes ""data"".) The repetitions of repeating fields are run together into a single string, with repetitions separated by the group separator character.
SYLK	The SYLK (Symbolic Link) file format is a text-based file format designed to be read by a software program. Generally it's been used for interchange between programs such as spreadsheets. The SYLK format doesn't accommodate repeating fieldsonly the first value in a repeating field will be exported. SYLK can preserve internal tab characters but eliminates internal carriage returns.
DBF	Originally the underlying file format for Ashton-Tate's dBASE software line, DBF is a binary file format that can be read by various software programs. Unlike many of the other export formats, the DBF format preserves FileMaker field names to some extent. Field names are converted to upper-case, spaces are converted to the underscore character, and the overall field name is limited to 10 characters. This can lead to field name duplication. The DBF format allows no more than 254 characters of data in a field and, like SYLK, does not support exporting more than the first repetition of a repeating field.
DIF	DIF (Data Interchange Format) is a text-based data format originally used with the VisiCalc program. DIF preserves field names during export, without either truncating or transforming them as DBF does. DIF preserves all repetitions of a repeating field, with repetitions separated by the group separator character.
WKS	WKS, the underlying file format for Lotus 1-2-3, is a purely binary data format, meaning it cannot be read sensibly with a text editor. Like most export formats, it has some limitations. Data is limited to 240 characters per field. Date and time values are not exported as raw data, but rather as date and time functions, if they are within a supported range of 1900 to 2099. Dates outside that range are exported as text. WKS does not support exporting more than the first repetition of a repeating field.
BASIC	BASIC (.bas) is a file format used for BASIC source code. Like the other text-based file formats, it has its export quirks. Internal tab characters are preserved. Internal return characters are converted to spaces. Internal double quotes are converted to single quotes. All field repetitions are preserved, with repetitions separated by the group separator character. Field length is limited to 255 characters.
Merge	The Merge format is intended for use with word processors and other applications that support mail-merge or similar functionality. Field names are fully preserved, as are internal tab characters. Internal returns are exported as vertical tabs. All repetitions of a repeating field are exported.
HTML Table	As the name suggests, this export format writes data from the selected records into a basic HTML table. Field names are output as column headers. Internal tabs are preserved, as are internal carriage returns. Field repetitions are exported into a nested table.
FileMaker Pro	This export format will create a new FileMaker Pro file with a field structure that matches the fields being exported. This is the only file format into which it's possible to export data from container fields. Not all FileMaker field types are preserved; summary fields become number fields, and calculation fields become data fields of the appropriate type (whatever the output type of the calculation is defined to be).
XML	FileMaker can export its data into two different XML formats, or grammars, called FMPDSORESULT and FMPXMLRESULT. You may choose whether to export raw XML, or to apply a style sheet as the XML is exported. When you choose to export as XML, a dialog box will prompt you for those choices, as shown in Figure 20.3.
Excel	FileMaker 8 can export data to a file in the native Excel format. When doing so, you can specify certain parameters of the result file, such as the name of the target worksheet, and whether to use the field names as column headers, as shown in Figure 20.4. Internal tabs and carriage returns are converted to spaces. Only the first repetition of a repeating field is exported. FileMaker fields will be assigned the appropriate Excel data type in their resultant columns where possible. (For an example of where this is not possible, consider FileMaker data that falls outside the range of dates supported by Excel.)

Figure 20.3. You have additional choices to make when exporting to XML.

Figure 20.4. You have additional options when using the new capability to export to Excel.

For more information on FileMaker's XML grammars, see Chapter 22, "FileMaker and Web Services," 669.