When I was the “knowledge manager” for an architecture firm, my responsibilities included populating a company-wide FileMaker solution with decades’ worth of data from a variety of sources, including many Excel, .txt, and .csv files. This experience exposed me to the various methods for exchanging data between Excel and FileMaker. Later, for a course in technical writing, I wrote a chapter of a hypothetical manual on FileMaker-Excel data exchange. I posted the chapter online and then received many queries about “where is the rest of the manual? ”
That proposed manual is unlikely to become a reality, meanwhile both Excel and FileMaker have launched many newer versions. But below is a summary of the current methods for exchanging data among these two programs as well as guidelines for deciding which option is best for a particular task. The information is current for FileMaker Pro 15 and recent versions of Excel.
Excel versus FileMaker: When Should You Use Them?
These two tools perform many of the same functions and produce reports and other output that can appear virtually identical. So the decision to use one or the other can seem difficult. One deal breaker is timeframe – setting up a simple data table that you will use once for a quick analysis or presentation and then never touch again is faster in Excel. Another is output – you can force Excel to mimic (or be exported to or embedded in) a great many presentations types but FileMaker’s layout capabilities can produce alternatives to the spreadsheet mode more rapidly and more reliably.
In comparing the two, remember that the equivalent of a cell in an Excel spreadsheet is a single incidence of a data field for a particular record. If you present FileMaker data in the table layout mode (the equivalent of a spreadsheet view), a record is the equivalent of an Excel row with each column representing a distinct field.
Use Excel when….
- The cells in a column (the equivalent of a FileMaker data field) can vary by data type or appearance, particularly on an ad hoc basis.
- You have complicated, nested calculations, especially if they aren’t applied consistently across a row or a column.
- You are doing scenario testing but don’t want to touch the original data that is stored in a data warehouse.
- You need a great many options for charting (FileMaker offers a nice charting option but it is not as full-featured as Excel).
- You want to embed tables or charts in other Microsoft Office programs through technologies such as OLE (object linking and embedding); FileMaker dispensed with OLE several releases prior to 15.
- Your primary task is to analyze and manipulate data.
- You have simple lists to sort and filter.
- You lack skills in Microsoft scripting languages and have little need to automate processes.
- You’re in the initial stages of planning a database – a spreadsheet can serve as an effective tool for determining the fields you would want in a database (you can import that information into FileMaker).
Use FileMaker when….
- Your time horizon potentially verges on medium to long and you will use and reuse the data multiple times.
- You want to protect the integrity of your data.
- You have data sets that would require the use of multiple, linked Excel spreadsheets to manage, e.g. you need to view relations among data sets or a 3D view.
- You need to use subsets of your data in a wide range of outputs, from reports to personalized letters to labels.
- You seek more control over document layout, graphics, and typography (FileMaker isn’t InDesign or Quark but it provides more graphics control than does Excel).
- Your primary task is to store data and present it in multiple formats (including exports to Excel).
- You’d like to automate tasks and routines through an accessible,visual scripting language. The FileMaker scripting language is easy to learn and the Script Workspace enables management of script libraries.
Exchanging Data between Excel and FileMaker
The basic options for exchanging data between these two software are listed below. The FileMaker website provides a summary of exchange options as well as information on how FileMaker manages Excel data.
Where available, links to the relevant FileMaker 15 documentation are provided. Some tips are included.
Option 1: Create a FileMaker solution directly from an Excel Spreadsheet
Related FileMaker documentation
This option opens an Excel spreadsheet directly in FileMaker. You will be given the option to have the first row in the spreadsheet import as either a database record or as the names of database fields.
- If you have multiple worksheets or a “named range” (a subset of cells that you’ve assigned a name to) in the source spreadsheet, FileMaker gives you a choice of what to import.
- FileMaker will import determine the field “type” (text, numbers, dates) based on the spreadsheet contents. It will import calculations as direct data rather than as FileMaker calculation or summary fields.
- FileMaker assumes every row containing data should import as a standard database record with uniform fields. It can’t determine, for example, whether a footnote or an extraneously populated cell is not part of the desired data import. So it is best to eliminate all extraneous contents prior to importing the data. Headers, footers, and charts (check) do not import.
Option 2: Create a FileMaker solution and import the Excel data
Related FileMaker documentation
This option imports data from Excel – or any other compatible source – into an existing FileMaker solution. This is a good solution for when you have a FileMaker database already established and you want to reuse that database structure or add new records to an existing data set.
- This option gives you several options for importing – for example, you can ignore both certain Excel and FileMaker content and you usually should ignore the first potentially importable record (e.g. first Excel row) if it contains field names.
- You can perform multiple imports from the same Excel sheet into the same FileMaker table and you can do so on a recurring basis. With these, you can treat the imports as additions to the existing data set or as updates or overrides. Be very careful with these options as you can overwrite existing data that you may not want lost or else end up with lots of duplicates. Reimporting will not reflect any subsequent changes to the data that you may have made to the initially imported data.
- You can automate the process by a FMP script, which is handy if you’re augmenting an existing database on a routine basis.
Option 3: Link to ODBC data via a driver
Related FileMaker Documentation
You can create links between an Excel spreadsheet and a FileMaker database through a ODBC (Open Database Connectivity) driver (you can do something similar with FileMaker and Microsoft Word or with other data sources such as SQL or Oracle databases).
Unlike Options 1 and 2 described above, ODBC linking enables dynamic linking between multiple data sources of different types. That can reduce the risk of dealing with outdated information while enabling either a “client” document to tap into a “data warehouse” without modifying the warehouse. It also enables you to take advantage of each software’s most powerful tools – for example, using Excel’s charting capabilities to present FileMaker data in various ways.
The challenge is that this mode of linking takes a lot of configuration and can be finicky. If you operate in an environment with a lot of firewalls, you may face challenges getting access to ODBC sources. You will need to read FileMaker’s guide on the topic and do a lot of testing. But this can be worthwhile for any type of long-term relationship between multiple resources (for quick data “dating,” the other options may be better uses of your time!).
Option 4: XML
Related FileMaker Documentation
Both Excel and FileMaker can exchange data through the Extensible Markup Language (XML) and Extensible Stylesheet Language (XSL) formats. These are powerful languages for twisting and turning data in insightful ways but they involve a learning curve. They are overkill for basic tabular data exchange such as can be achieved by the options described. However, they can be useful for extracting metadata from date sources and for dealing with summary data, prepping data for use in Excel pivot tables, and other high-level data uses. XSL, Document Type Definition (DTD), and other related standards can identify quality-control issues and verify character encodings. Because most applications nowadays support XML and XSL, you can use these standards to build data exchange frameworks among many different software, such as desktop publishing software, for a systematic deployment of data from a specific source.
If you use FileMaker Advanced (a high-powered developer version of FileMaker), you can generate an XML-formatted report of a FileMaker database and open that in Excel and other applications.
For guidance on XML-based exchange between FileMaker and Excel (and other applications), look not only at the FileMaker Community resources but also more general online “communities” such as Stack Overflow).
Option 5: Exporting Data from a FMP Database
Related FileMaker Documentation
With the same ease that Options 1 and 2 provide from importing data from an Excel spreadsheet into FileMaker, you can easily export data from FileMaker into Excel. FMP provides the following options:
File Menu…Save/Send Records As > Excel…
This provides the option to export either the current record or the current found set (which may or may not be all the database records) and to add metadata such as worksheet name (the name of the spreadsheet’s tab), title, subject, and author. You also can email the results as an attachment. The resulting spreadsheet is a table with the FileMaker field names appearing as column heading names and the metadata options converted to Excel document properties.
File Menu…Export > Excel….
This option also gives you the option to export metadata but it also opens up a dialog window that lets you select the fields to export and the order in which the fields will appear as columns (the FileMaker default is to include only basic text, numeric, and date fields but you can add summary, calculation, or global fields, although these will present the same values in many records). You also can apply the current layout’s data formatting to the data (handy if you want to skip the formatting in Excel) and you can export data from related tables (although you’ll likely only import the value of the first related record if multiple records are attached).
Option 6: Plain Text Formats
Both FileMaker and Excel can read and write common text interchange formats such as tab-separated (with a .tab or .txt file extension) or comma-separated (with a .csv or .txt file extension). These file formats treat every Excel line or FileMaker record as a separate line of text, with field or column information separated by tabs or commas. They do not contain content other than text nor do they retain formatting information and they import field names (column header content) as generic terms.
Because FileMaker enables reliable direct exchange with Excel, an interim import or export via plain text formats isn’t ordinarily required. However, if you experience conflicts or failed direct exchange, you could try using these file formats as a diagnostic; sometimes, this is useful with files created in older versions of a software. And some people who are concerned with long-term archival access recommend storage in these formats because they may outlive proprietary file formats. For example, it is not possible to open up a Filemaker solution created in an older version (pre-FileMaker 7) in version 15 without having going through a series of interim upgrades that require access to older versions of the software. If you only need to preserve the raw data, backups in these formats can provide a means of future access.
Scripting and Automating the Process
Related FileMaker Documentation (Opens as an Acrobat PDF file)
For many of the options above, you can use FileMaker’s scripting features to automate the process of sharing data with Excel spreadsheets. The Scripts Step window displays options for importing and export records as well as steps for converting Excel files to FileMaker files and saving records in Excel format. With scripts, you not only can automate the data exchange options but you can build more complex routines of working with multiple exchange processes and modifying data and controlling record sets before or after the exchange process. For example, you could initiate a complicated Find strategy to nail a particular subset of records that you routinely want to export. You also can build in steps to provide guidance and helpful hints for users, which is nice for non-programmers executing routines that they haven’t designed.