FileMaker Extra: Importing from a Spreadsheet Simplified

In FileMaker Pro 10 in Depth I described how you can use XML-like structures to pass multiple values into a script parameter ("Passing Structured Data Elements", pp 392-393). Here's a real-life example of how you can use that technique to import a spreadsheet. Now FileMaker already has an import command (File->Import) that can handle spreadsheets, but you have to provide a mapping of the spreadsheet columns to FileMaker fields at import time or at least in a mapping that will be valid at import time. A number of people have pointed out that this can be a serious limitation in solutions for users who aren't used to the FileMaker Pro import dialog (it's not complicated, but can be daunting with its list of all the columns in the spreadsheet and all the fields in the layout. This is a general-purpose solution that lets you import data from a spreadsheet where the first row consists of column names (a common situation). You then can import them into a FileMaker database based on those names. In only one place in your FileMaker script do you need to reference the column names which can make for easy maintenance and reuse. Read on for the details.

The idea is to import the spreadsheet data into an intermediate table. Once you have that buffer between the spreadsheet world and the FileMaker world, all is well. (This is an extension of the layered/MVC architecture I've talked about at user groups lately.) The data structure for the parameter is XML-like as shown on page 392. I have added some spaces here for clarity. < First Name>Fred< /First Name>< Favorite Number>123< /Favorite Number>< Favorite Animal>Monkey< /Favorite Animal> On page 393 I provide the code that can parse such a string. (Note that the code give here differs slightly from the code on page 393 to make it more generalized.) Create a custom function called GetXMLParam (or whatever you want to call it). Give it two parameters: param_name param_string

Here's the body of the function: Let ( [ openElement = "<" & param_name & ">"; closeElement = ""; startPos = Position ( script_parameter ; openElement ; 1 ; 1 ) + Length ( openElement ) ; endPos = Position ( script_parameter ; closeElement ; 1 ; 1 ) ] ; Middle ( script_parameter ; startPos ; endPos - startPos ) )

Now create a new FileMaker table with field names like F1, F2, F3, etc. Don't worry, you'll never see them again. After that, create an import that imports the first spreadsheet column into F1, second column into F2, etc. When you want to move the spreadsheet into your own table, here's the code (remove the extra spaces after <):

Go to Record/Request/Page [First]

Set Variable ($F1open;Value:"<" & importtable::F1 & ">"

Set Variable ($F1close;Value:"< /" & importtable::F1 & ">"

Repeat this for as many columns as you want to import. To generalize the code further, add a test to see if importtable::F1 is blank and skip that column. Then create a loop that goes through each record in the file and builds a string such as the XML-like string shown previously and creates a new record in the FileMaker table you really want to use. (Note the first line in the loop is different from the subsequent lines.)

Loop

Set Variable [$my_param; $F1open & Evaluate ( importtable::F1 ) & $F1close ]

Set Variable [$my_param; $my_param & $F2open & Evaluate ( importtable::F2 ) & $F2close ]

Set Variable [$my_param; $my_param & $F3open & Evaluate ( importtable::F3 ) & $F3close ]

New Record/Request

Set Field [realtable::widget_name; GetXMLParam ("Name", $my_param)]

Set Field [realtable::SKU; GetXMLParam ("mfr_SKU", $my_param)]

Commit Records/Requests []

End Loop

This is the only place in the FileMaker code where the spreadsheet column names appear, and, as you can see, they can differ from the FileMaker field names. Because you are always importing into that temporary table, you can set up the import without worrying about the mapping of fields because you're mapping the fields in your script.