Home > Articles > Data > FileMaker

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020