Export Formats

When exporting data, you always create a file, but you get to decide what format the file should be. In the last example, you exported your data to a Comma-Separated Text file. This example is one of the many file formats FileMaker can produce when it exports. Most formats exist simply because computer software has put forth a lot of standards in the last 50 years, and FileMaker wants to be as flexible as possible. Some formats do have unique advantages, though.

The first question you need to ask is, "Where is the data going?" Your export format choice almost always depends on what the person you're sending it to needs, and most of the available formats are uncommon types you only use if the person on the other end asks for it. These include SYLK, DBF, DIF, WKS, and BASIC.

The remaining formats are explained below:

• Tab-Separated Text and Comma-Separated Text are very common formats for database data. They put each record on its own line. With Tab-Separated Text, you get a tab between each field value, while Comma-Separated Text has quotes around field values, and commas between them. Almost every program in the world that can import data supports one of these formats. If you're not sure, try Tab-Separated Text firstit's the most common.
• The Merge format is just like Comma-Separated Text, with one difference: The first line of the file shows individual field names. The advantage is that when you import this file in another program, you can see what each field is called, making it easier to get the right data. Unfortunately, most programs don't expect this extra line, and treat it as another record. People most often use this format for mail merge in word processing programs.
• If you want to put the data on a Web page, use HTML Table . The resulting file isn't suitable for importing into another program, but it can be displayed nicely in a Web browser. You can also open the file, copy the HTML table from inside it, and paste it into another Web page.

  UP TO SPEED
  Where's My Style?

  Most of FileMaker's export formats are text based. In other words, what gets produced is just a normal plain text file. The structure of this file determines which format it is, but you can open them all in Notepad or TextEdit and read them directly. A side effect of this reality is that none of them support styles. In other words, if you go to great lengths to change the first names in all your records so the font matches the customer's personality, you can kiss you hard work goodbye when you export.

  In addition to the font, you lose the size, style, and color of the text. The notable exception to this rule is the FileMaker Pro format. Since this export format creates another FileMaker Pro database, all the formatting you painstakingly put in place is preserved.

  If you must have text styles in your exported data, there is an option, but it ain't pretty. FileMaker has two calculation functions designed to aid this process: GetAsCSS and GetAsSVG.

  Each function takes a single text parameter and returns a snippet of ordinary text with style information embedded using special tags. GetAsCSS produces text that can be put on a Web page. When viewed in a Web browser, the text takes on its original fonts, sizes, styles, and colors. GetAsSVG works the same way, but uses a different tagging scheme: the one used in the SVG, or Scalable Vector Graphics format.

  To take advantage of these functions, you'd need to create a calculation field with a formula something like this:

   GetAsCSS ( First Name )
  

   

  You would then export this field instead of the First Name field. If you do this with the HTML Table export format, you get properly formatted text on your Web page. More realistically, you'd use these along with the XML format and a special XSLT style sheet that produces a Web page or SVG image. You'll learn more about this option at the end of this chapter.

• The FileMaker Pro format is your best choice if your data is destined to go back into FileMaker some day, or if you just want to view and work with the exported data directly. When you choose this format, FileMaker creates a brand new database with just one table and only the fields you choose to export. This format is the only one that preserves font, style, size, and color in field data (see the box above) and one of the few that supports repeating fields (Section 3.3.4.2).

  ---

  Tip: Usually, if you just want to export records from one FileMaker file to another, you don't have to export them first. Just go to the database where you want the data to end up and import them directly (Section 17.3). Of course, if one database is in South Africa and the other is in Tibet, then by all means export them first.

  ---

• For the ultimate in flexibility, choose XMLthe un-format. When you export XML, you get to apply something called an XSLT Style sheet. An XSLT Style sheet is a document written in a programming language all its own that tells FileMaker exactly how the exported data looks. If you need to produce an export format that FileMaker doesn't support directly, XSLT is the way to do it. But be forewarned: XSLT is not in the same league as FileMaker itself, ease-of-use-wise. Here is where you might need some hired help. XSLT is introduced briefly at the end of this chapter.

  ---

  Tip: Although it applies to one field value and not a set of records, don't forget about the Edit Export Field Contents command. This command lets you export the data in the current field to a file. Text, number, date, time, and timestamp fields are exported to a plain text file. Container fields create a file whose type is appropriate for the data in the field.

  ---

  17.2.1. Save/Send Records As

  After wading through the technical details about FileMaker's amazing flexibility in exporting to practically any format you might need, the following two export types should give you some breathing room. Forget about arcane formatting rules and settle into more familiar territory. Gone too, is the need to specify a field order for export. The File images/U2192.jpg border=0> Save/Send command lets you choose between an Excel spreadsheet and a PDF (Portable Document Format) document. Both commands grab whatever fields are on the current layout and whip them and their data into the shape you choose.

  17.2.1.1. Excel

  If people need to work with the data you send them, but they aren't lucky enough to have FileMaker, you can create an Excel file for them, as shown in Figure 17-3. Once you've sent the file, they're free to do anything to the data that Excel allows make a table, create a chart, or run totals and summaries.

  The Save option is a pop-up menu that lets you choose between the "Records being browsed" or the "Current Record" so you don't even have to do a find if all you want to do is send the current record. Like the other formats under the Export menu, you have the option to "Automatically open file" and "Create email with file as attachment," so it's easy to check your work and create a quick email with the data your boss just asked to email her.

  

  Figure 17-3. The Save Records As Excel window gives you the option of automatically opening the file you're creating, so you don't have to go rummaging around your hard drive looking for it. Just check "Automatically open file" and soon, you'll be looking at your FileMaker data as an Excel spreadsheet.

   

  If you click Options, you can set up some basic details for your new Excel file. For example, you can choose whether you want your FileMaker field names put in the first row of the spreadsheet. You can also type in a file name and a title, subject, and author (which appear in the Properties dialog box).

  17.2.1.2. Portable Document Format (PDF)

  PDF files are viewable by just about anybody with a computer. With PDF files, you get to choose exactly how the data looks, since this format preserves your beautifully crafted layouts. With FileMaker's layout tools, your keen design sense and the Save/Send Record as PDF command, you could use email to distribute invoices, product catalogs, sales brochures, or annual reports. You can even send vision impaired people a file their software can read out loud. Even if all you need to do is send people data they can see, but can't change, then a PDF file is just what the software engineer ordered.

  FREQUENTLY ASKED QUESTION
  Making It Fit

  A lot of FileMaker's Export formats use special characters for important things. For instance, the Tab-Separated Text format uses a return character to separate records. What happens if I have a return character in my field?

  Good question! Special characters are one of those problems with no ideal solution. But FileMaker does the best it can within the limitations of each export file type. For a file to be called Tab-Separated Text, for example, you simply can't have return characters inside records. It's just against the rules. In this particular case, FileMaker turns the return character into something else, called a vertical tab, which is a standard but rarely-used character left over from when computers had green screens. Presumably, you don't have any of these in your fields (you can't type them, so it is a pretty safe bet you don't), so it's easy enough to turn vertical tab characters back into return characters when you open the file in another program. In fact, that's exactly what happens when you import a Tab-Separated Text file into FileMaker.

  Another character of concern is the quote mark. If you have these in your fields, and you export a Comma-Separated Text file, FileMaker has to do something with them so they don't interfere with the quotes around field values. In this case, FileMaker turns your quote mark into two quote marks together.

  That doesn't sound like a solution, but it is. If you assume any quote mark that is immediately followed by a second one is really just data, and not the end quote mark for a field value, you can figure out which is which in the export file. Most programs that support Comma-Separated Text understand this convention. (You might think commas would also be a problem with Comma-Separated Text, but they're not. Since every field value is in quotes, commas are OK. Only the commas between quoted values are considered field separators.)

  The HTML and XML formats have all kinds of special characters, but each has a special "entity" form that's used if they're supposed to be treated as ordinary data. FileMaker converts any such characters appropriately, and every program that processes these formats understands the conventions.

  Finally, FileMaker has a data-structure concept that most formats simply don't understand: repeating fields. The idea that one field could hold several values is foreign to most database programs. When you export repeating fields, FileMaker pulls another freaky character out of its hat: It separates each value with a character called the Group Separator. Thankfully, this action is almost never a problem because you generally don't export repeating field data to a file that needs to be read by a program that doesn't understand repeating fields. One last note: The FileMaker Pro export format obviously does directly support repeating fields.

   

  ---

  Tip: The most common PDF viewer, Adobe Acrobat, is a free download at http://www.adobe.com/products/acrobat/readstep2.html. Mac OS X also comes preloaded with its own PDF viewer, called Preview.

  ---

  The basic choices are the same as for Excel. You choose between sending just the current record or the whole found set. You can have the file opened in a PDF viewer or attached to a new, blank email just as soon as FileMaker's created it. But the PDF Option set is much richer. There are three tabsDocument, Security and Initial View. Starting with the Document tab, you can set:

  • Title. This title isn't the name you give the file in the dialog box. It's an additional title that becomes part of the properties of the document. Most, but not all, PDF viewer programs let you see a file's properties.
  • Subject. This document property helps you tell a series of similar documents apart from each other.
  • Author. This document property is usually your name, but may also be the name of your company or department. Again, it helps you organize a bunch of similar files.
  • Keywords. Some file management programs can search these keywords to locate documents.
  • Compatibility. Choose from "Acrobat 5 and later" or "Acrobat 6 and later." Choose the lower number if you think your recipient might not have the latest and greatest PDF viewer.
  • Number pages from. You can make a different numbering system than the one you have in FileMaker.
  • Include. You can set a limited page number range with these options, so that only a part of the found set is included in the PDF file. You may have to go to Preview mode in FileMaker first, though, to help you set the page range properly.

  ---

  Note: You can see the PDF file's Title, Subject, and Author in Adobe Acrobat's PDF viewer's Document Properties Summary window. For those on OS X, whose PDF viewer of choice is Preview, these items aren't available.

  ---

  In the Security Tab, you can decide how much access you give your recipients when they receive your file. You can choose:

  • Require password to open the file. Click the checkbox to turn this option on. Then enter a password. This checkbox is useful if you're selling a catalog and provide passwords only to people who've paid to receive it. Then, of course, there's the standard use; you just don't want every Malcolm, Reese, and Dewey poking around in your PDF files.
  • Require password to control printing, editing and security. Click the checkbox to turn this option on and enter a password. You might want your PDF freely distributed, but not so freely used. If so, don't require a password to open the file, but lock it down so nobody without a password can use the material without your permission. With this option checked, a whole raft of new options become available. You can set:
  • Printing. Choose from Not Permitted, Low Resolution (150 dpi) or High Resolution. These options would protect photographic or other artwork images that you want to send in a catalog, but don't want people to reprint freely.
  • Editing. These options let your recipients interact with your document, but still allow you to protect your intellectual or business property. You can choose from Not Permitted for total control, restrict recipients to filling in form fields only, and so forth.
  • Enable copy of text, images and other content. With this option checked, recipients can copy and paste material from your PDF file.
  • Allow text to be read by screen reading software. This option allows people with vision or reading problems to let their screen reading programs read your document out loud.

  The final tab in the PDF Options window is probably be the one you'll use the least. But if you like to control which PDF viewer options are visible when your recipient first opens your PDF file, then Initial View is the panel for you:

  • Show. Your choices include Page Only (just the FileMaker layout, with no extra tools or panels), Bookmarks Panel and Page, or Pages Panel and Page to offer viewers some navigation options.
  • Page Layout. Control the way the PDF viewer displays multipage documents. If you choose Default, your recipients' preferred view remains in force. But you can also specify Single Page, Continuous, or Magnification.

  Importing Data