by Micheal Smith, via <editor@iso-ezine.com>
Excel Import: Not Quite Up to Speed
FileMaker Pro 4.0 boasts Excel import capability but is it an improvement?
One of the most discussed features of FileMaker Pro 4.0 is the ability to import Microsoft Excel spreadsheets. Clearly this feature has been long requested and desired, but reception of the feature as implemented is lackluster.
New Excel Import Feature
The newly implemented Excel Import feature in FileMaker Pro 4.0 allows you to directly import Excel files without using an intermediate file format. Import is simple: just drag and drop an Excel file on the FileMaker Pro 4.0 icon or choose Import from the File menu within FileMaker. If the Excel file contains multiple worksheets, FileMaker prompts you to choose which worksheet to import. The dialog does not include the ability to preview the worksheets in any way so you must be familiar with the file you are importing or be prepared to import all the worksheets separately and then choose the data you really want.
Maybe you're asking, “perhaps the new Excel Import capability preserves cell formatting or calculations?” Unfortunately, the answer is no. The first set of data shown below is from a sample Excel spreadsheet. The same data was created in Excel 5.0 on the Macintosh and Excel 6 in Windows 95. The second group of data shows the same data once it has been imported into FileMaker on the Mac. The third group shows the same data after it has been imported into FileMaker on Windows.
Data in Excel*
text date number (currency) number (percent) time
this 9/30/97 $3.00400% 1:34:00 AM
is 10/30/97 $2.674% 3:23:54 AM
some 9/30/63 $9,234,568.0040% 12:00:00 AM
text 9/30/01 ($12,894.34) 423%
total $9,221,679.33867%
Data in FileMaker (Mac)
f1 f2 f3 f4 f5
text date number (currency) number (percent) time
this 9/30/97 3 4 1:34:00
is 10/30/97 2.67 0.04 3:23:54
some 9/30/63 9234568 0.4 48:00:00
text 9/30/2001 -12894.34 4.23
total 9221679.33 8.67
Data in FileMaker (Windows)
f1 f2 f3 f4 f5
text date number (currency) number (percent) time
this 9/30/97 3 4 1:34:00
is 10/30/97 2.67 0.04 3:23:54
some 9/30/63 9234568 0.4 00:00:00
text 9/30/2001 -12894.34 4.23
total 9221679.33 8.67
Note that there is an error in the fourth row of the time column in the Macintosh import. Data in columns is placed into text fields named f1, f2, etc. - not terribly useful. With a few clicks of the mouse, you can turn text fields into date, number, and time fields as appropriate. A few more clicks while in layout mode and you have largely reproduced the data you had in the Excel file. To finish cleaning it up, you will probably want to rename the fields and delete the first record.
The last row in the test data totals the data above. The import treats this just like any other row of data. It will take more than a few mouse clicks to create the necessary summary fields and add them to the layout.
If not Excel import, then what?
DIF import is slightly better than Excel import. Columns are still rendered into text fields named f1, f2, etc. The data, however, appears closely to the original without hand tweaking. The total row is still treated as another record. While all of the data is treated as text, we have only lost the leading $ in the number (currency) column and have an anomoly with the negative value in the 5th row of the same column. DIF produced identical results on Macintosh and Windows.
Data in FileMaker (via DIF, Mac and Windows)
f1 f2 f3 f4 f5
text date number (currency) number (percent) time
this 9/30/97 3.00400% 1:34:00 AM
is 10/30/97 2.674% 3:23:54 AM
some 9/30/63 9,234,568.0040% 12:00:00 AM
text 9/30/01 12,894.34) 423%
total 9,221,679.33867%
SYLK format produces much the same results as dragging and dropping the Excel document onto FileMaker with the exception of the time field. Results there were different on Macintosh and Windows.
Data in FileMaker (via SYLK, Mac)
f1 f2 f3 f4 f5
text date number (currency) number (percent) time
this 9/30/97 3 4 1:34:0AM
is 10/30/97 2.67 0.04 3:23:54AM
some 9/30/63 9234568 0.4 12:0:0AM
text 9/30/2001 -12894.34 4.23
total 9221679.33 8.67
Data in FileMaker (via SYLK, Windows)
f1 f2 f3 f4 f5
text date number (currency) number (percent) time
this 9/30/97 3 4 1:0:0AM
is 10/30/97 2.67 0.04 3:0:0AM
some 9/30/63 9234568 0.4 12:0:0AM
text 9/30/2001 -12894.34 4.23
total 9221679.33 8.67
Using DBF 4 (dBase IV) format produced truly interesting results.
Data in FileMaker (via DBF 4, Mac)
TEXT DATE NUMBER__CU NUMBER__PE TIME
this 09/30/1997 3.00 4 01/01/1904
is 10/30/1997 2.67 0 01/01/1904
some 09/30/1963 9234568.00 0 01/03/1904
text 09/30/2001 -12894.34 4
total 9221679.33 9
Data in FileMaker (via DBF 4, Windows)
TEXT DATE NUMBER__CU NUMBER__PE TIME
this 09/30/1997 3.00 4 01/01/1900
is 10/30/1997 2.67 0 01/01/1900
some 09/30/1963 9234568.00 0 01/03/1900
text 09/30/2001 -12894.34 4
total 9221679.33 9
Interesting, but not helpful. While we did get real (more or less) field names, much of the data is destroyed in the process. To be fair to FileMaker, opening the DBF 4 formatted file in Excel displays the same data errors.
So what good is Excel import?
While it's not everything that we hoped it would be, Excel importing is still the fastest, most convenient and most reliable way to get Excel data into FileMaker. The downfall of every other file format is that you have to have Excel in order to use it. Having worked with organizations that use Excel and avoiding purchasing and installing it on my own machine, I have often been in the situation of having to hunt down a machine with Excel installed just to convert the file to DIF to be able to get the data into FileMaker. The Excel import capability will certainly save me some footwork.
As the examples above show, Excel import also produces the most reliable data. While DIF is close, it does produce errors when importing negative numbers.
The fastest way to get Excel data on the web
Another advantage to using the Excel import is the speed at which data in an Excel worksheet can be served up on the World Wide Web. Simply drag and drop your Excel spreadsheet on FileMaker. Choose a worksheet if necessary. Turn on the Web Companion and the data is available for all the world to see, or at least for your close personal friends behind your firewall.
## END ##