Ideas

Migrating Data From FileMaker To CiviCRM

FileMaker is a popular "personal database" package that runs on the Windows or Mac desktop.  Many organizations keep their data in a FileMaker database.  Because of the limitations of this method of maintaining a database, some organizations wish to migrate their database to CiviCRM.  This article describes the obstacles in doing that and how to overcome them.

First, use FileMaker to export each table in the database to a CSV file.  The CSV file you write will use the character encoding system used by the desktop.  In a locale that uses English or another western European language, the encoding will likely be Mac OS Roman on Macintosh desktops or Windows 1252 on Microsoft Windows desktops.  If your locale does not use a western European language, your desktop will likely use a different encoding in the CSV files you export.

The encoding system used by CiviCRM is UTF-8 Unicode, which is different from any encoding now used by FileMaker.  When CiviCRM imports a CSV file, it checks whether the encoding of the file is UTF-8.  If it isn't, and the system running CiviCRM has the optional PHP iconv function installed (which it probably does), the input file is converted on the assumption that it uses the encoding in the Legacy Encoding field under Administer > Global Settings > Localization, default Windows-1252.  If this assumption is incorrect, the input data will be imported wrong.  The usual symptom is that accented characters and some punctuation characters "look weird".  If this happens, the remedy is to delete the data just imported and do the conversion over.

If you know the actual encoding of the input file, you can type it into the Legacy Encoding field.  The encoding must be one recognized as a proper input encoding by GNU libiconv .  If you have shell access to the machine running CiviCRM, you may be able to get a list of recognized encodings with the shell command iconv --list.  If the Legacy Encoding field value is not recognized, the error message is the misleading "Input file must be in CSV format".

Fortunately, there is a free, easy-to-use tool called OpenOffice which can convert the character encoding of your CSV files to UTF-8.  Then when the converted files are imported by CiviCRM, the data will have the correct appearance.

To use OpenOffice to change encoding:

1. If you don't have OpenOffice installed on your computer, download and install it.
2. Open the CSV file to be converted with Calc, the OO spreadsheet function.  If the file extension of the FileMaker files is .csv, as it should be, Calc will be chosen to open the file by default.
3. You will see a pop-up Text Import which offers a selector for encoding.  Choose the current encoding of the CSV file.  There is a window in the pop-up which shows the file as interpreted by the selected encoding, so you can try various encodings to see which one makes the file "look right".  The default delimiters work for FileMaker exports.
4. Click OK.
5. Choose File > Save As from the top menu or type Ctrl-Shift-S
6. Type a different filename in the Name box, so you don't overwrite your input file.
7. Select Text CSV (.csv) from the selector below the file list window.
8. Check the Edit filter settings box in the lower left corner.
9. Click Save
10. You will get a warning pop-up that information might be lost, asking whether you want to save as Text CSV anyway.  Click Yes
11. You will get a pop-up Export of text files asking how you want the file exported.  Choose Character set Unicode (UTF-8), Field delimiter , (comma), Text delimiter " (double quote) and check Save cell content as shown.
12. Click OK
13. The saved file will be an RFC4180 CSV file encoded in UTF-8 Unicode.

Now you can import the converted CSV files into CiviCRM.  The hurdle to overcome at this stage is to decide how the information stored in your FileMaker database relates to the fields used by CiviCRM.  There is no easy answer to this, since FileMaker allows you to store data in an organization you choose.