When you’re working with lots of data, few tools are more useful than a powerful querying language like SQL. The ability to isolate the data you’re looking for, make useful transformations, and generate statistics can be the difference between gaining genuine insights and being totally overwhelmed.
Say you have a data set stored in spreadsheets. You need to look at just some of the records — those that meet a certain set of conditions. The standard Excel Find function has failed you, and creating custom views in another sheet using equations is too much trouble to figure out (not to mention the time you’ll waste if you need to copy it to another spreadsheet). What you need to do is write a query. SQL has everything you need built in, but setting up a database for your data seems like overkill, and that’s assuming you have an RDBM server installed on your laptop in the first place.
That’s where Composable comes in. Our dataflow apps love tables. Composable makes it easy to:
- Import tables from a variety of sources. Excel spreadsheets? CSVs? Google Sheets? Remote database? We’ve got you covered.
- Query your tables from your dataflow app. Write any SQLite query and your app will execute it on your table as if it were interacting with a real database.
- Perform dozens of other useful transformations. Sorting, picking out individual columns, joins, filtering, counting, or even pinpoint manipulation with custom C# or VB code. If you need it done, there’s a way to do it inside Composable. And soon, we’ll be adding easy ways to cleanse your data without the need for custom code or an external cleaning tool.
Here’s how to get started. For this example, we’ll imagine our data is contained in a CSV file on our laptop.
From the Designer, drag in a CSV Reader module. This converts a CSV file into a queryable Table that other Composable modules can read and modify. To bring in our file, drag in a File Uploader module to the left of the CSV Reader. Click in the box labeled “File” and select the CSV spreadsheet in the file browser that appears. Connect the bottom output of the File Uploader (which contains a URI pointing to our uploaded file) to the URI input of the CSV Reader. Now the Reader has a file to work with. If you run your app now and view the result at the output of the CSV Reader, you should see the contents of your table (as show below).
To perform a query on our data, pull in a Table Query module and connect the output of the CSV Reader to the table input of the new module. Then, simply write an SQLite query in the Table Query module’s “Query” input, and its result should appear at the output of the module when you run the app!
In our example, we have a table of books and authors, and we’ll write a quick query to see the titles by our favorite sci-fi author. Note that our input table is named [t0].
|
1 2 3 |
SELECT * FROM [t0] WHERE Author = 'Neal Stephenson'; |
Once we press Run App, our query result is available at the output of the Table Query module:
By combining this technique with our array of other Table modules, you can perform sophisticated operations on your data, all without needing to waste time setting up a database or trying to accomplish your goals with less powerful tools in spreadsheet software.
