| Many of FileMaker's export formats can be set to use the format settings of the layout from which you're exporting. That means, for example, that if you have a date field that contains the value 11/17/2004, and it's set to display as Wednesday, November 17, 2004, you can enable the Apply Current Layout's Data Formatting to Exported Data option in the Specify Field Order for Export dialog, and then the date value is exported in that format. Special formatting options applied to date, time, or number fields are carried through in the export if this option has been enabled. The formatting options also apply to calculated fields with date, time, or number results. NOTE In Layout mode you can apply date and time formatting to a time stamp field, but those settings are carried through in an export only if you use the XML format.
The formats that support the application of layout settings are Tab, Comma-separated (CSV), BASIC, Merge, HTML, FileMaker, and XML. WKS/WK1 partially applies the current layout style. Number formats come through with formatting, but time and date values are converted to time and date functions, the result being that they appear as unformatted times and dates when opened in a spreadsheet application. SYLK, DBF, and DIF do not support the application of layout styles at all. A quick example demonstrates the kinds of data formatting that can be achieved through using the Apply Current Layout's Data Formatting to Exported Data option. Suppose a FileMaker Pro 7 file has the following fields defined: Date Value [Date] Time Value [Time] Number Value [Number] Text Value [Text] Picture [Container] And suppose that the file contains the values formatted as shown in Figure 20.3. Figure 20.3. The date, time, number, and text values have all had custom formats applied. The container field is not shown. 
This data with the layout formatting settings shown in Figure 20.3 can yield a variety of different exported results, depending on the export file format chosen . Samples of several of the formats are shown in Table 20.1, along with some notes about each format. Table 20.1. Export File Formats | Text | | Tab | Tab-separated text. Container fields cannot be exported. Layout formatting can be applied. A great all-purpose format that works with almost any application that can import text files. Container fields cannot be exported. Layout formatting can be applied. | | Sample exported Tab data: | | Saturday, April 17, 2004 | 9:32 | $34.21 | The Big Dog jumped high. | 4/17/2004 9:32:53 AM | | Comma-Separated (CSV) | Works with almost any application that can import text files. Extension can be .txt or .csv . Carriage returns within field values are converted to vertical tab values. Group separator character between repeating field values. Any non-number characters in number fields (such as letters or currency symbols) are stripped out during the export process, although if number formatting options have been applied, those are preserved if current layout settings are applied during the export. Regular quotation marks (not smart quotes) convert to single quotes. | | Sample exported CSV data: | | "Saturday, April 17, 2004","9:32","$34.21","The Big Dog jumped high.","4/17/2004 9:32:53 AM" | | BASIC | This format works with Microsoft BASIC programs. Carriage returns within text fields are converted to spaces. Group separator character between repeating field values. Exports only the first 255 characters of text fields. Regular quotation marks (not smart quotes) convert to single quotes. | | Sample exported BASIC data: | | "Saturday, April 17, 2004","9:32",34.21,"The Big Dog jumped high.","4/17/2004 9:32:53 AM" | | Merge | Used for mail merge applications with word processors. This format retains field names . | | Sample exported Merge data: | | Date Value,Time Value,Number Value,Text Value,Timestamp Value | | "Saturday, April 17, 2004","9:32","$34.21","The Big Dog jumped high.","4/17/2004 9:32:53 AM" | | HTML Table | Exports data as an HTML table that can be viewed by a Web browser and copied into the HTML for another Web page. Fields become table columns ; records become table rows; repeating values become nested tables. | | Sample exported HTML Table data: | <HTML> <HEAD><META HTTP-EQUIV="Content-Type" CONTENT= "text/html;CHARSET=ISO-8859-1"></HEAD> <BODY><TABLE BORDER=1> <TR> <TH>Date Value</TH> <TH>Time Value</TH> <TH>Number Value</TH> <TH>Text Value</TH> <TH>Timestamp Value</TH> </TR> <TR> <TD>Saturday, April 17, 2004</TD> <TD>9:32</TD> <TD>.21</TD> <TD>The Big Dog jumped high.</TD> <TD>4/17/2004 9:32:53 AM</TD> </TR> </TABLE></BODY></HTML>
| | XML | FileMaker can export XML in two different grammars: FMPXMLRESULT and FMPDSORESULT. This is the only format (and only when the FMPXMLRESULT grammar is in use) that can apply date and time layout styles to timestamp fields. | | Sample exported XML data: | <?xml version="1.0" encoding="UTF-8"  ?><FMPXMLRESULT xmlns= "http://www.filemaker.com /fmpxmlresult"> <ERRORCODE>0</ERRORCODE><PRODUCT BUILD="02-10-2004" NAME="FileMaker Pro" VERSION="7.0v1"/> <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="Export samples" NAME="Export samples.fp7" RECORDS="1" TIMEFORMAT="h:mm:ss a"/> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Date Value" TYPE="DATE"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Time Value" TYPE="TIME"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Number Value" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Text Value" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Timestamp Value" TYPE="TIMESTAMP"/> </METADATA> <RESULTSET FOUND="1"> <ROW MOD RECORD> <COL><DATA>Wednesday, November 17, 2004</DATA></COL> <COL><DATA>9:32</DATA></COL> <COL><DATA>.21</DATA></COL> <COL><DATA>The Big Dog jumped high.</DATA></COL> <COL><DATA>April 17, 2004 9:32:53</DATA></COL> </ROW></RESULTSET></FMPXMLRESULT>
| | DIF | Field names are retained in the export. Format has a column (vector)/row (tuple) structure. FileMaker Pro conforms to DIF restrictions during export. Group separator character is inserted between repeating field values during export. Cannot apply layout format settings to data during export. | | Sample exported DIF data: | | TABLE | | 0,1 | | "Export samples.fp7" | | VECTORS | | 0,5 | | "" | | TUPLES | | 0,00000001 | | "" | | LABEL | | 1,0 | | "Date Value" | | LABEL | | 2,0 | | "Time Value" | | LABEL | | 3,0 | | "Number Value" | | LABEL | | 4,0 | | "Text Value" | | LABEL | | 5,0 | | "Timestamp Value" | | DATA | | 0,0 | | "" | | -1,0 | | BOT | | 1,0 | | "11/17/2004" | | 1,0 | | "9:32:21 AM" | | 0,34.21 | | V | | 1,0 | | "The Big Dog jumped high." | | 1,0 | | "4/17/2004 9:32:53 AM" | | -1,0 | | EOD | | Binary Formats (no examples because these aren't text formats) | | DBF | dBase format. This format is compatible with dBase III and IV, but not dBase II. The nice thing about this format is that field names are preserved in the process, but with some caveats. There's a limit of 10 characters per field name, and any spaces are converted to the underscore character. The field name is uppercase. This truncation can lead to inadvertent duplicate field names, which are illegal and which FileMaker resolves by prepending under score characters to the names. For example, First Name of the Dog, First Name of the Cat, and First Name of the Lizard fields export as FIRST_NAME, _FIRST_NAME, and __FIRST_NAME. This format can have a maximum of 128 fields, a maximum of 254 characters per field, a maximum of 400 bytes per record, and exports only the first value in a repeating field. Layout format settings cannot be applied to the export. This format does not support the Macintosh character set. | | SYLK | Only the first value in repeating fields can be exported, and the layout format settings cannot be applied to the export. | | WKS/WK1 | A spreadsheet format, this format can be imported by Lotus 1-2-3 and most other spreadsheet applications. Only 240 characters per field. Date and time fields are exported as functions, not fields. Lotus supports dates from only 1900 through 2099, so dates outside this range are exported as text. Only the first value in a repeating field is exported. Layout formatting applies to number and time fields, but not date fields. The export format is WK1 on Win, and WKS on the Mac, although the file extension is WK1. |
Generally, FileMaker can export two broad types of files: text files and binary files. Different types of text files are internally organized in different ways, but they are all text-based documents. Binary files are not stored in a text format. As a result, it's possible to show sample data for only the text file formats in Table 20.1, but not for the binary formats.  | If you have fields that you use to store lengthy notes or comments, you may find that those notes get truncated when they are exported to certain file formats. To learn how to deal with these situations, see "Data Gets Truncated with Certain Export Formats" in the Troubleshooting section at the end of this chapter. |
NOTE If you export data to the XML format, the Specify Field Order for Export dialog is preceded by the Specify XML and XSL Options dialog shown in Figure 20.4. Figure 20.4. FileMaker can export two different XML grammars: FMPXMLRESULT and FMPDSORESULT .  The FMPXMLRESULT grammar is the only export format that can incorporate date formatting options applied to a timestamp field. After you've selected a grammar and clicked Continue, the usual Specify Field Order for Export dialog comes up.
 For information on importing XML data, see "Importing from an XML Data Source," p. 559 .
|
