Importing and Exporting Data - File Maker

You’ll often want to bring data into a FileMaker database from another FileMaker database, from another database or file, via an ODBC source, or even via an XML source. For instance, you might get property listings sent to you each week that you need to then quickly import into a FileMaker database for sorting and distributing to sales reps for follow-up.

You can do this quite easily in FileMaker using the built-in Import feature. Likewise, it’s just as easy to get data out of FileMaker, using the Export feature, where you can also export to many different file formats for use in other applications.

Importing Data from a File
Go to the Detail View 2 layout in Company.fp5, then select Import Records > File from the File menu. You’ll be asked what file or database you want to import. Select the ImportMe.mer file in the earlier folder, and you’ll see the following Import Field Mapping dialog. (We’ll look at all options in this dialog because the options you choose can severely affect a database, especially if you select the wrong ones:

Importing Data from a File

The lefthand frame shows the field names and data of the file you are importing from. The righthand frame shows the fields of the current FileMaker database, the one you’re importing into. Between them you see the Map column, which shows whether the field at left will be imported into the field exactly to the right of it or mapped to some other one. An arrow pointing right means that the fields on the left and right are mapped and will be used in the import. A circle with a slash through it means that the fields, left and right, are not connected and the field at left won’t be imported.

In order to map fields for importing you can either drag fields on the right up and down (hold the cursor over one and you’ll see up and down arrows), map them using the Map column, or use the drop-down at top right to automatically set up the map using various field sorting order criteria (creation order, custom order, A to Z, and so on).

Choose Matching Names for this import. You’ll see that FileMaker makes its best guess as to how the fields should map based on the names of the fields in both files and sets the matches it finds to import. In this case, all fields should match right up.

A few other minor controls are the Scan Data arrow buttons, where you can flip back and forth between real records in the data you are about to import. This will help you to make sure the fields are all matched up properly, especially if the data you’re importing doesn’t show the field names as the first column. Define Fields will bring you to the Define Fields dialog for the database you’re in so you can quickly add a field to import into if it doesn’t yet exist. The character set drop down menu (Windows only) allows you to choose which character set you would like to use when pulling the data into your database’s fields. (Some characters, like carriage returns and such, translate differently on Macs and Windows, for instance.)

Import Action
The Import Action box at lower left can severely affect your database if you don’t make the appropriate selection. Here’s what the three selections do:

  • Add new records: Means that the number of records in the file you’re importing will be added as new records to the database you’re importinginto. If there are duplicate records in the import file, they will be added also (creating havoc if there are duplicate primary keys used in relationships). After this import, the found set is the records that were imported.
  • Replace data in current found set: Means that if you’ve got, say, 100 records in the current found set, the first 100 records in the import file will overwrite data in the matched fields in the records of the found set (in order). If there are more than 100 records in the import file, they’ll be ignored. (Always make sure you’ve got the right found set specified before running this type of import and consider making a backup copy of the database first, just to be safe.)
  • Update matching records in current found set: Also overwrites data in the found set, but only records that have matching records in the import file. A match is defined as a record in the current database that has a corresponding record, via a designated match field, in the import file.

To designate a match field, first select this option under Import Action, and then align the match fields in the two windows, above. Then, click the map icon between the two fields until you see a double-sided arrow like.

This is useful as a solution when you have remote users taking a copy of the database away (where they might update some records) but later need to come back and sync up with the master database. For example, if a company record has no phone number value before Joe leaves with his copy, but Joe later updates his copy with a phone number and then syncs with the master database, the two company records will match based on CompanyID (using this type of import) and the phone number, as well as any other changes, will be updated in the master database. Note, though, that if Joe blanked out the CompanyName field in his copy, this change would not be made in the master database; only changes from nothing to something are updated, but not the reverse.

  • Add remaining records: If you use this feature, any records in the import file that have no match are added as new records to the database, using the field mapping specified. After this type of import, the matching records that were found are updated. Be careful if there’s more than one match in the destination database as all matches will be updated identically.

Now select Add new records and make sure that the CompanyID field is not mapped to import (you’ll see why in a second) and click Import. You’ll see this Import Options dialog:

Importing and Exporting data

Here, you specify whether to have auto-entry happen upon import, for fields with auto entry options specified. Check this box and click OK.

You’ll see that you’ve a found set of new records (they’re just the records that were already in there, but with new CompanyIDs), ready for editing, reporting on, and so on.

Try importing the merge (.mer) file a few more times to see what the different import actions do. Change data on both sides to see what happens; the .mer file should be editable in any text editor.

Importing Data from a Folder
You can also import a bunch of files or even a folder full of compatible images or movies into your FileMaker database all at once.

Importing a Folder Full of Text Files
With this function, you can import the text content of several files, en masse, into a FileMaker database, along with the file’s name and path. (Files must have the .txt extension for this to work.) Let’s jump right in and try to import a bunch of merge files at once.

  1. Go to File > Import Records > Folder. You’ll get this dialog:
  2. Importing a Folder Full of Text Files

  3. Under folder location, navigate to the folder called Folder of Files to Import on the CD-ROM, select it, and click OK. (You can include all subfolders.)
  4. Under File Type, select “Text files.”
  5. Next, you’ll get the field mapping dialog again. Match only the Text Content “field” to the Notes field and import.

This should give you a good idea of what this feature can do for you (like import documents a client sends you into a Document. fp5 database to give users access to their contents).

Importing a Folder Full of Images
With this function, you can import the graphic content of a bunch of files, en masse, into a FileMaker database, along with the file’s thumbnail graphic, name, and path. It works just about the same way as importing a bunch of text files, except that you can specify whether to import just a reference to each graphic or the whole graphic into the designated field (like when you use Insert Picture).

Go ahead and try to import the Images to Import folder on the CD-ROM, matching the Image “field” to the container field called Logo and you’ll get the picture, pun intended.

If you import the file path to a text or image file, you can open it quickly from within your database. Simply highlight the path with the cursor, then rightclick/ COMMAND-click it (in Windows or Mac OS X, respectively; sorry, no Mac OS Classic support!). Then pick the Open File option. This is very, very useful you can create an entire document management system with this feature.

Incidentally, you can even import a folder full of aliases to images or text files. FileMaker will follow the shortcuts to the original files and import them.

Importing Images from a Digital Camera Mac OS X Only
If you’ve got Mac OS X and a digital camera (or another device that connects in the same way, like a memory card reader), you get the bonus listed above. To import JPEG or TIFF digital photos, along with any Exchangeable Image File (EXIF) annotation data like aperture or shutter speed (if your digital camera provides it):

  1. Connect your digital camera to your computer and set it up to download photos.
  2. Next, go to File > Import Records > Digital Camera, pick your camera from the device list, and select which photos to download (some or all).
  3. After you click OK, you’ll have to map the fields that your camera sends to some FileMaker fields, which you know how to do by now.

Importing Excel
If a file you’re importing is a Microsoft Excel workbook, you can specify whether the first row of the worksheet you select (or named range, if there are any specified) contains the field names so that FileMaker can use them for matching, or import them if they contain data.

Other File Formats
Table below lists the file formats that FileMaker can import.

File Formats That FileMaker Can Import

File Formats That FileMaker Can Import

Tables belowlist graphic, sound, and video formats, respectively, which FileMaker can import.

Graphic Formats That FileMaker Can Import

Graphic Formats That File Maker Can Import

Sound Formats That FileMaker Can Import

Sound Formats That File Maker Can Import

Movie/Multimedia Formats That FileMaker Can Import

Movie/Multimedia Formats That FileMaker Can Import

Importing XML
Beginning with FileMaker Pro 6, you can now import and export XML right from within a FileMaker Pro database. Despite what you may think, XML is basically just another way to “mark up” data so that many different types of applications (like Microsoft Office applications, many Web and application servers, and other relational database management systems) can read the data.

try importing some XML-formatted data, try importing the ImportXML.xml file on the CD-ROM into the Computer Shop database system’s Company.fp5 database. When you import the XML data file, you’ll see an additional Specify XML and XSL Options dialog, in which you can select an XML file, select an external Web server to query with an HTTP request, and specify which XSL style sheet to use to transform the XML file into FileMaker’s own required FMPXMLRESULT grammar. After that, the standard FileMaker import dialog will be displayed as usual.

What About Importing Via ODBC?
No worries: Importing via ODBC (Open Database Connectivity) is discussed later.

Drag and Drop Imports/Conversions
FileMaker can create new databases based on all the above file types, too, simply by opening them. Try it. Go to File > Open and navigate to ImportMe.mer to start a conversion process. You’ll name and locate the FileMaker database, then FileMaker will create it, and import the data. It will even name the fields for you if they exist in the import file, and if the import file is a FileMaker Pro, Excel, or DBF type. (Because drag and drop is kind of like importing data, it’s mentioned here, although it’s technically “importing” the data into a newly created FileMaker database.)

Exporting Data
As you might expect, you can export data out of FileMaker also. To try it, go to Company.fp5 and select File > Export Records. FileMaker will ask you to name and locate your export, as well as pick the file format. Choose FileMaker Pro Files as the format and save the exported database to the desktop. Click Save. You’ll get the Specify Field Order for Export dialog, which looks like this:

Exporting Data

In the left white box are fields available for export. (You can use the dropdown menu to use related fields, too.) In the right white box are the fields you have chosen for the export order. To move a field over, double-click it or single click it and click the Move button. Or use the Move All button to move all fields from the specified database at left into the export order. Here’s what the other features of this dialog do:

  • The Character Set drop-down menu (Windows only) allows you to choose the character set you’d like to use when pulling the data into your database’s fields. (Remember: Some characters like carriage returns and such translate differently on Macs and Windows boxes.)
  • The “Format output using the current layout” radio buttons allow you to export data using the field formatting specified in Layout mode of the current layout. For instance, you may have a date field on the layout with the date formatted as “Mar-03-73” even though it was typed in as “3/3/1973.” When the data is exported formatted, it will look like “Mar-03-73”; otherwise, it will look just the way it was entered.
  • Use “Summarize by” to export subsummary values by the break field(s) you specify. For this to work, some fields must sort the data first; these are the fields you can use as break fields.

Be sure that you know what you’re doing here; data exported summarized looks different. If you export the company database summarized by state; for instance, you’ll get only one record in the export file per unique state. That’s the whole idea — exporting summarized data for use elsewhere like for an Excel chart.

Exporting XML
See FileMaker Pro’s built-in help system to learn the ins and outs of exporting XML. Essentially, when you select XML as your exported data’s output format another dialog appears (before the standard export dialog), asking you to select the XML grammar to use as well as any XSL style sheet you would like to use to format or translate the exported data. After that, the export file is created just like any other export data file that comes out of FileMaker.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics