This file demonstrates a process by which you can import a vCard into FileMaker Pro using just FMP scripting. The parsing and data mapping involved is a bit complex, but the file should make it a painless process of just a few short steps. My intent was to make this as painless as possible for someone to import the scripts and fold the functionality into an existing solution without any significant fuss.
Here is the full text of the original article, it explains the techniques used, and clarifies what the sample file does:
Importing vCards into FileMaker
vCards are a handy way to pass contact information around in a standard format. While FileMaker does not import them nativley, a few techniques make this possible. The basic steps to importing vCards are; bring the vCard data into your database, do some pre-parsing work, and parse the card. If you’d like to follow along with the examples below you’ll need a table named “vCardImporter” with a text field called “data”, a global text field called “peskyNull_g” (choose the Auto-Enter – Data: option under Options, but leave the auto enter data field blank for now), and a global container field called “vCard_g.”
Bring the vCard Data into Your Database
If you try to import a vCard into FileMaker, you’ll discover that FMP won’t import a file with the “.vcard” file extension, it’s not a recognized file format. While manually changing the extension in your file system would work, there’s a more elegant way to do this. The steps to copy and rename a file with FileMaker are as follows:
Beginning with FileMaker 8, variables could be used to dynamically specify paths for script steps that have embedded file references (referred to as “unnamed” file references, because these file references aren’t saved with the Define File Reference dialog, and cannot be used anywhere else in your application). The best practice for this is to initialize a script variable in a previous script step and to insert the variable into the unnamed file reference dialog that is used for by the script step. Don’t try to perform calculations in that file reference dialog, simply insert the variable:
Note that when you use a script variable to specify a path for the Import Records script step, you will not see any records in the “Specify Import Order” dialog, this is because at the time you are editing your script, there is no value in your script variable, so FMP cannot find a file for you to use to work out your import order. Fortunately there’s an easy fix for this. To temporarily point to a vCard file to map your import fields, follow these steps:
- With your script still open for editing, switch to the file system and drop a copy of a vCard on your desktop (or wherever you like.)
- Change the file extension of the vCard to “.txt” (note that you may have to change the setting in your file system to allow you to see file extensions.)
- Switch back to your script, and choose “Specify Data Source/File”, if that dialog is not yet open.
- If the file reference dialog does not yet have “$path” entered, go ahead and type that in.
- Click “Add File…” in the upper right, and locate the vCard that you renamed with “.txt”, choose that file.
- Click “OK” through the dialogs and then open the “Specify import order” dialog, you should now be able to use your temporary file to map your fields.
Then, in your import script:
- Add an “Insert File” script step to add the vCard to the global container field.
- Be sure to leave “Specify File” option unspecified in the Insert File script step, this will cause the file open dialog to be presented to the user. Also, for this technique, it doesn’t matter if the user click’s “Store only a reference to the file” in the file selection dialog. This is because “Export Field Contents”, the script step that we use to write the file out with a different name, does it’s job the same for both a file that is stored in a container field, as well as a reference to a file on disk.
- Add a “Set Variable” script step to store the path and name that you want to use to rename your vCard file.
- An easy way to get a valid path is to copy it out of a script step that offers a file reference dialog, like Import Records, and then change the name of the file to your temporary file name. Remember that whatever file name you choose, you need to make the file extension “.txt” The sample code below shows how to specify a generic temporary file na
- Add an “Export Field Contents” script step, referencing the container field that holds the vCard and the variable that holds the path and file name that you want to rename the vCard to.
- Be sure to choose “Comma-Separated Text Files” for the file type in the Specify File dialog. Choose “Perform auto-enter options while importing” after setting the import order and closing the import order dialog (you only need to import into the “data” field, so just map the first “field” from the vCard to that field.
- Add an “Import Records” script step to import the records from the renamed file into the “data” field, entering $path in the Specify File dialog.
After you are done performing your field mapping, be sure to go back into Specify data source and remove the path to your temporary field, you can just carefully delete that text. When you are done, your script looks something like this:
Go to Layout ["vCardImporter"] Insert File [vCardImporter::vCard_g] Set Variable [$path; Value: "file:"&Get(DesktopPath)&"tmpCard.txt"] Export Field Contents vCardImporter::vCard_g; "$path"] Import Records [No dialog; "$path"; Add; Mac Roman]
After the vCard is imported each line will be imported into a separate record. I like to merge these into a single field or variable before parsing the card. Also, some vCards come in with an “ASCII null” character that must be cleaned up before parsing (I’ve run into it with vCards from OS X Address Book that have a photo embedded.) The steps for the pre-parsing are as follows:
- Loop though the records and merge them into a single field or variable.
- Use a call to Substitute() to remove the ASCII null character
The best way that I’ve found to clear out the invisible ASCII null is to open a vCard that contains this character in a text editor (like Bbedit, for example), show invisibles, copy the character, and then paste it into the “Auto-Enter – Data:” option for a global field in your temporary import table (peskyNull_g in our example). This method is “clone proof”, all that you have to do is turn on “Perform auto-enter options while importing” when you close the “Specify Import Order dialog on your Import Records script step, the null character will be available for your substitute function. In my testing, I found that trying to paste the null character directly into the any calculation dialog caused FMP to crash. Another handy way to know if your are dealing with invisible characters, in general, is to click into an FMP field where you suspect the problem exists, and tap the left and right arrow keys. Often times, if there are invisible characters in the field, it will appear that extra strokes are required to move the cursor. After you add these script steps, you import script looks like this:
Go to Layout ["vCardImporter"] Insert File [vCardImporter::vCard_g] Set Variable [$path; Value: "file:"&Get(DesktopPath)&"tmpCard.txt] Export Field Contents vCardImporter::vCard_g; “$path”] Import Records [No dialog; "$path"; Add; Mac Roman] Go to Record/Request/Page [First] Loop Set Variable [$vCard; Value:$vCard&"¶"&vCardImporter::data] Go to Record/Request/Page [Next; Exit after last] End Loop Set Variable [$vCard; Value:Substitute($vCard; vCardImporter::peskyNull_g;"")] Delete All Records [No Dialog] //imported records no longer needed
Parse the Card
At this point, you’ve got the contents of a vCard in a variable and are ready to start parsing out the data that you need. Parsing data out of a vCard requires a little bit of knowledge about the vCard standard. I’ll give a brief primer, and you can read more about the standard here: http://tools.ietf.org/html/rfc2426 The vCard format is basically a system that uses labels, attributes, and values. I refer to a particular data point in a vCard as a “node.”, however, the actual vCard standard uses more granular terminology like “Type”, “Key”, “Class”, etc. Values are delimited from labels and attributes by a colon. The first couple of lines of a vCard typically will look like this:
In the above example, the version node has a value of 3.0. It easy to see here that you can grab this value using basic text parsing techniques that look for “version:”. Note that every node in a vCard is followed by a line ending, and after you import the data you will have a searchable pilcrow (”¶”) after every node, this is very handy for parsing out the data. Some nodes have attributes, for showing things like which email is preferred, for example. Here is a very common example of a node with attributes:
In the above example, the node is “email” and there are several attributes, represented by name value pairs and delimited by semi-colons, finally the value for the node is delimited by a colon. Note that you can always count on the colon as the last value before the actual node value, this is very helpful for parsing purposes. You may be wondering what happens when a literal semi-colon or colon appears in the value for a node. The vCard standard states that these characters must be escaped (preceded by a “\”) This ensures that vCard parsers, of any kind, are not thrown off when they do their work of extracting values and attributes. For example, if somebody were to type “version:2.0? in an address book field and then export the vCard, that data would appear as “version\:2.0?, so a pattern count on “version:” would not find the user entered data. Here is an example of how to parse out values and attributes from a vCard. In this example I set the value into a variable, you may choose to do that (making your code more portable) or, you may choose to set the values directly into fields.
//Grab the name value: Set Variable [$name; Let( [ vNode = "¶N:"; vStart = Position($vCard; vNode; 1;1)+Length(vNode); vEnd = Position($vCard; "¶"; vStart; 1) ]; If(PatternCount($vCard; vNode); Middle($vCard; vStart; vEnd-vStart) ) )
You’ll also want to test that the tag you are searching for actually exists in the vCard, or else you may get strange results in come cases. The inclusion of the pilcrow character in the vNode initialization ensures that another label ending in “n:” is not found, for example “begin:vCard” would match if it were not for the pilcrow. The variable vStart is looking for the position of the first character after the “n:” value. The variable vEnd is looking for the position of the very next pilcrow, after the “n:” and whatever the value is for that node. Once we have those two data points, it’s simple to extract the value from the vCard, using Middle(). This code works for any vCard node that has just one instance. Note that the name (n:) value is actually a semi-colon delimited list that includes last name, first name, middle name, prefix and suffix. The position of each piece of data in this type of value is fixed, so it’s easy to pull out the part that you want. For n:, you will typically get a result like this: Smith;John;;; – each data point in the node always has the same position, i.e., you will always find the first name is the second position (if there is one), and you will always find the same number of semicolons in a given node. The predictable nature of the node format makes it a simple text parsing exercise to pull out the values that you want, here is a custom function that will pull out a specified value, as well as un-escape the result:
// fn_vCardGetValue (string; valNum) // email@example.com 2007-02-06 // takes a raw vCard node and returns the specified value number // also un-escapes values Let( [ vString = Substitute(string;"\;"; ")(*&^%$#@!" ); // need to take out literal semi colons vString = Substitute(vString;"¶"; "" ); vString = If(Right(vString;1)=";"; vString; vString&";"); // make it easy by adding a training semi colon vNumVals = PatternCount(vString;";");// see how many actual values there are vEnd = Position(vString; ";"; 1; valNum); // the end of the string is the semi colon that ends that value vStart = If(valNum = 1; 1; Position(vString; ";" ;1; valNum-1)+1); // there is no semi colon to count back from if we are looking for val 1 vValue = If(vNumVals >= valNum ;Middle(vString; vStart;vEnd-vStart);"") // only grab a value if it is there to grab ]; Substitute(vValue ;[")(*&^%$#@!";";"];["\n";"¶"]; ["\.";"."]; ["\:";":"]; ["\,";","]; ["\\\\";"\\"]) )
Note that if you don’t own FileMaker Pro Advanced, and can’t install a custom function, you can still utilize the code above, the PatternCount and Position calls are the heart of what is going on here. There are some interesting issues around dealing with nodes that can appear multiple times, extracting attributes of nodes, and the “looseness” of vCard implementation by certain applications. These issues will be the subject of a future article. Hopefully there is enough information here to get you started parsing vCards!