FME and the Tale of the Magical Transformation of the Excel Spreadsheet
Once upon a time, in land far, far away (the distance of which could considerably more accurately be determined with an FME workspace), an eager young Safe Software partner found himself in a boardroom in a castle at the center of a kingdom, ready to demo FME to a panel of strangers.
With a flourish of his mouse, he combined some cadastral CAD data with a database query, zapped some overshoots, snapped some polygons, reprojected the whole wazoo, and sent it flying over to a GIS system.
“Meh,” said the panel. One looked bored and checked his email.
Determined to wow them, he wrestled an orthoimage to the ground, draped it over a LiDAR-derived elevation model, lightly tinted the whole thing a lovely chartreuse, and popped a perfect, tiny, sparkling BIM rendition of the castle just exactly where it should be in the center.
“Meh,” said the panel. Another one scratched her ear and stared disinterestedly out the window, possibly at a cow.
At his wit’s end, he delved deep into his bag of demos, and pulled out his masterpiece – a workspace that did things with spatial data never before seen in the kingdom, a workspace of such ambition and depth and innovation that no one, upon having seen it execute (without a single red line in the log, no less), could fail to be at least a tiny bit impressed. He clicked run, and digits soared, calculations were made, queries were executed, labels were scribed, and a glorious cartographic thing of beauty emerged from the writers, accompanied, almost as an afterthought, by an Excel report.
The panel, eyes wide open and jaws agape, stared at him in wonder. “Why didn’t you say you do spreadsheets?!”
And they all transformed data happily ever after. And we learned that spatial – love it though we might – isn’t the only thing that our users need to get done.
Now, more or less, (and closer to home), this story actually did happen. And it was the driver that widened our vision on what FME does for you – and what it could be doing. Since then, we’ve paying attention and making substantial strides in more conventional, non-spatial tools, particularly Microsoft Excel spreadsheet handling. Our Excel readers and writers recently received a major upgrade, adding more flexibility and eliminating the need to have Excel installed in order to use them.
Why use FME for Excel?
You may very well ask that question. (We did.) But it’s remarkable how much, and how often, valuable data gets rather free-formedly stuffed into the user-friendly world of Excel. You could call it the shape file of non-spatial data. (We do.) A great deal of information, particularly online, is provided in Excel format – and it’s also a very, very popular way for end-users to look at results. Considering spreadsheets as simply another data source and delivery format, it just makes sense to integrate them with the transformation and automation power of FME.
So here are a few reasons you might want to do it. Large datasets can quickly exceed the limits of the Excel format – you might need to perform some analysis on something large, and then skinny it down or filter and organize it into sheets for your end user. Perhaps your Excel data is inherently spatial – has addresses, or x,y coordinate pairs, or can be linked to existing geometry via an attribute like a neighborhood name or asset code – and you can suddenly map it and perform spatial operations on it, too. Perhaps you simply would like to merge it with another spreadsheet or external data source, spatial or not.
And, perhaps most importantly, you may want to do this again in the future. All excellent reasons to tackle the job with FME.
Read and write spreadsheets.
As I mentioned, the Excel reader/writer has undergone a major overhaul, and it’s worth taking a look at a few of the key functions you can now make use of. The reader can read multiple sheets and formulas, and named ranges too – particularly useful in a more free-form spreadsheet where two different tables of information can sit next to each other in the same sheet (perhaps ogre sightings and troll sightings?). You can also now tinker with the schema in the reader, with interactive attribute adjustment and preview. And should two of those attributes happen to be coordinates of some sort, you can set a coordinate system and turn those into geometry on the spot.
On the writing side, multiple sheets are also supported. In fact, a feature type fan-out will automatically filter your data into a series of sheets by type. Named ranges can be defined, and cells formatted by column. You can write and define new formulas. And if you want your spreadsheets to be useful and pretty too, simply add an Excel template to your writer. You can format, chart, and decorate to your heart’s content in the template, and FME will just add in the data.
Spatialize, Manipulate, Query, and Pivot your data.
Some of the most interesting functionality comes in between the reader and the writer, where your spreadsheet data becomes highly malleable.
Spatialize Your Data
Spatialize your data using coordinates, geocoding, or joining it via an attribute to geometry from another source – and you now have a spatial dataset that you can map or perform any number of spatial functions and analysis on.
Merge and Query Your Data
Merge and query your data by bringing in data from other sources, even other spreadsheets. SQL operations on non-SQL data? You betcha – the InlineQuerier will treat it like a virtual database.
Pivot Your Data
Pibot your data with the AttributePivoter, and create all sorts of summary and statistical analysis results.
Manipulate Your Data
Manipulate your data just like any other dataset. You can reformat and create attributes; test, filter, group, and split your data; or QA it and clean it up. Perhaps you’ve started with a very large, non-spreadsheet source dataset and want to report on it – do your work in the workspace and write only the results out.
Automate the whole thing.
Possibly the most powerful aspect of FME is the persistent nature of a workspace. Once you’ve the done work to set it up, it’s there to use again and again and again. You can further automate the process with FME Server’s scheduling capabilities, but not everyone can justify having FME Server…. at least until now. With FME Cloud rolling out, access to FME Server just became a reality for everyone. (You can read our FME Evangelist, Mark Ireland’s preview of FME Cloud for an introduction.)
It’s a fairy tale ending.
So imagine the possibilities – and they go far, far beyond just simply reformatting some simple spreadsheet data. Reading XLS data live from online sources, performing spatial analysis, generating presentation-quality reports automatically – even emailing the results to stakeholders in a format they’re familiar with and like…
Combining FME workflows with Excel support with automation via FME Server or FME Cloud – it’s the stuff legends are made of.
The Princess Bride’s Vizzini demonstrates the fairy tale version of conditional arguments and the CoordinateSwapp