FlashRelate: extracting relational data from semi-structured spreadsheets using examples

Abstract

With hundreds of millions of users, spreadsheets are one of the most important end-user applications. Spreadsheets are easy to use and allow users great flexibility in storing data. This flexibility comes at a price: users often treat spreadsheets as a poor man's database, leading to creative solutions for storing high-dimensional data. The trouble arises when users need to answer queries with their data. Data manipulation tools make strong assumptions about data layouts and cannot read these ad-hoc databases. Converting data into the appropriate layout requires programming skills or a major investment in manual reformatting. The effect is that a vast amount of real-world data is "locked-in" to a proliferation of one-off formats. We introduce FlashRelate, a synthesis engine that lets ordinary users extract structured relational data from spreadsheets without programming. Instead, users extract data by supplying examples of output relational tuples. FlashRelate uses these examples to synthesize a program in Flare. Flare is a novel extraction language that extends regular expressions with geometric constructs. An interactive user interface on top of FlashRelate lets end users extract data by point-and-click. We demonstrate that correct Flare programs can be synthesized in seconds from a small set of examples for 43 real-world scenarios. Finally, our case study demonstrates FlashRelate's usefulness addressing the widespread problem of data trapped in corporate and government formats.

User Interaction Models for Disambiguation in Programming by Example

“…The FlashProg framework provides UI support for several PBE engines related to data manipulation, namely FlashFill [7], FlashRelate [4], FlashExtract [17], and FlashWeb. Even though PBE has been applied to various application domains, we focus our attention in this paper on data manipulation, which we believe is one of the most impactful applications for PBE.…”

“…The synthesis algorithm uses a divide-and-conquer based strategy to decompose the original learning task to smaller sub-tasks [27]. This approach has been used to develop several PBE systems in the domains of syntactic string transformations [7], semantic string transformations [8], data extraction from semi-structured sources [17], and transformation of semi-structured tables [4].…”

“…For example, FlashExtract does not support arbitrary boolean conjunctions and disjunctions. Hence, if the tasks require learning a complex boolean expression, FlashExtract will not be able to perform it [7,17,4].…”

“…PBE has traditionally been applied to synthe-sizing small programs in various domain-specific languages (DSLs) such as string and table transformations [8] and data extraction [17]. PBE has been pursued in various communities including programming languages [18,6,4], inductive programming [9], machine learning [21], artificial intelligence [28], and databases [30]. Work in these communities has focused on addressing one of the key challenges in PBE, that of efficiently searching the huge state space (potentially infinite) of programs defined by the underlying DSL for a program that is consistent with the user-provided examples.…”

Programmatic and direct manipulation, together at last

“…[23,24,31]), programming by example (e.g. [1,18,26]), and bidirectional programming techniques (e.g. [22]).…”