- Knowing Your Data
- FileMaker Field Types
- Importing Data
- Exporting Data
This chapter is from the book
This chapter is from the book
This chapter is from the book
Importing Data
FileMaker can import data from one or more static, external files on the hard drive of the FileMaker client machine or on a shared network volume. FileMaker can also import data from remote data services. The following sections outline the various sources for importing data and the specific requirements and limitations of each.
→For more information on importing data in FileMaker, see Special Edition Using FileMaker 8, Chapter 19 "Importing Data into FileMaker."
File-Based Data Sources
FileMaker can import data from individual files, available on a local hard drive or networked volume, in any of the following formats:
- Tab-separated text
- Comma-separated text
- SYLK
- DIF
- WKS
- BASIC
- Merge files
- DBase files
In addition to importing from these common text file formats, FileMaker can also perform more specialized imports from files created in Excel, or in FileMaker itself.
Importing from Excel
When importing data from an Excel file, FileMaker can detect multiple worksheets within the source Excel file. FileMaker can also detect the existence of any named ranges in the source document. When importing, if named ranges or multiple worksheets are detected, FileMaker will give you a choice as to whether to import from a worksheet or named range, and allow you to select the specific worksheet or range from which to import.
When importing from Excel, as with all imports, FileMaker brings in only the raw data it finds in the source file. Formulas, macros, and other programming logic are not imported.
FileMaker Pro assigns an appropriate field type (text, number, date, or time) if all rows in the column hold the same Excel data type. Otherwise, a column becomes a text field when imported into FileMaker.
Importing from FileMaker
FileMaker can also import data from other FileMaker files. These may be files that are present on the local client machine, or files that are hosted on another machine.
Much as an Excel file can contain multiple worksheets, a FileMaker database can contain multiple tables. It’s necessary to choose a single table as your data source when importing from a FileMaker file.
Importing from FileMaker can be particularly convenient if the source file has a structure that matches that of the target file. In this case, rather than manually configuring the import mapping on a field-by-field basis, it’s possible to choose the Arrange by Matching Field Names option. When you do so, fields of the same name in the source and target tables will be paired in the import mapping.
Importing Multiple Files at Once
It’s also possible to import data from multiple files at a time. You can import data from either text or image files. You can import both the raw data in the file, and also extra data about each source file’s name and location.
In each case, the files being imported must all be grouped in or underneath a single folder. You can specify whether to look simply inside the one folder, or whether to search all the way down through any subfolders.
Importing from Multiple Text Files
When importing from a batch of text files, you may import up to three pieces of data from each text file:
- Filename
- Full path to file
- Text contents
You may choose to import any or all of these.
Unlike with a regular import from a text file, the internal structure of the text file is disregarded. The entire contents, whether containing tabs, carriage returns, commas, or other potential delimiters, is imported into a single target field.
Importing from Multiple Image Files
Importing from multiple image files is quite similar to importing from multiple text files. When importing a batch of images, you may import any or all of the following data fields:
- Filename
- Full path to file
- Image
- Image thumbnail
In addition to filename, file path, and file contents (an image, in this case), FileMaker allows you to import an image thumbnail, either in addition to or instead of the full image. You may wish to do this to save file space or screen space. FileMaker creates these thumbnails via its own algorithms, so you have no control over the exact details of thumbnail size or quality.
When importing images, you have the choice (as you always do when working with data in container fields) of importing the full image into the database, or merely storing a reference. Importing full images will take up more space in the database (probably much more), whereas importing only the references will mean that you’ll need to make the original files continuously available from a hard drive or network volume that all users of the database can access.
Importing Digital Photos (Mac OS)
On the Mac OS, FileMaker can also import images directly from a digital camera, or from any device capable of storing digital photos. This process is quite similar to a batch import of images from a single folder with only a few differences.
FileMaker will allow you to specify which images to import. You may choose them individually, or via a range such as "last 12 images." FileMaker will also handle transferring the files from the storage device to a download location of your choice. And, as with other imports, you can choose whether to import the full image into FileMaker, or simply store a reference.
Whereas the regular batch import of images brings in only four pieces of data about each image, a digital image import may have access to much more data about each image. If the selected images contain EXIF data (a standard for embedding extra data into an image file), FileMaker can also detect and import many additional pieces of data about the image such as shutter speed, ISO setting, and the like.
Importing from an ODBC Data Source
FileMaker can import data from a data source accessed via ODBC. Many types of data can be accessed via ODBC, but it’s most commonly used to retrieve data from a remote database, often one running some flavor of the SQL language.
Working with OBDC data sources requires three things:
- A data source able to provide data via ODBC—Again, this is most often a remote database server of some kind. The administrators of the data source may need to perform specific configuration of the data source before it can accept ODBC connections.
- An ODBC driver, installed on the local computer that’s running FileMaker, that’s able to talk to the specific ODBC data source in question—ODBC drivers need to be installed on each computer that will access a data source. So, much like a FileMaker plug-in, ODBC drivers generally need to be installed on the computer of each FileMaker user who will be using ODBC access. ODBC drivers are specific to a particular data source (the PostgreSQL or Sybase databases, for example), and also specific to a particular platform (Mac or PC). In order to connect to an ODBC data source, you must have a driver specific to both your data source and platform (Sybase 12 driver for Mac OS, for example).
- A DSN (Data Source Name) that specifies the details of how to connect to a specific data source—DSNs are configured differently on each platform, and generally contain information about a specific data source (server name, user name, password, database name, and the like).
→For more information on configuring ODBC access in FileMaker, see Special Edition Using FileMaker 8, Chapter 19, "Importing Data into FileMaker," and Chapter 20, "Exporting Data from FileMaker."
Once you have successfully configured and connected to an ODBC data source, the process for selecting data to import is a bit different than for regular imports. Before proceeding to the field mapping dialog, you’ll need to build a SQL query that selects the fields and records you want. (For example, your SQL query might read SELECT name_last, name_first, city, state, zip FROM customer). Once you’ve done this, you’ll be able to map the resulting fields to those in your FileMaker database.
Importing from an XML Data Source
FileMaker can import data from XML-based data sources. The source can either be a physical file, stored on a locally accessible volume, or a remote XML data stream accessed over HTTP.
In order to import data from an XML source, the XML data must be presented in the correct format, which FileMaker calls a grammar. FileMaker can only import data from XML sources that use the FMPXMLRESULT grammar. If the XML data is not in the FMPXMLRESULT grammar, you will need to specify an XSLT stylesheet at the time of import; that stylesheet needs to be written in such a way as to transform the inbound XML into the FMPXMLRESULT grammar.
→For more information on FileMaker’s XML import capabilities, see Special Edition Using FileMaker 8, Chapter 22, "FileMaker and Web Services."
Creating New Tables on Import
FileMaker 8 has a nice new capability. When importing data, you can now specify that the inbound data should be placed in a new table rather than adding to or updating an existing table. The new table will take its field names from those present in the data source.