Exporting to Excel and Spreadsheet Tools
Overview
Excel is one of the most popular tools in use by businesses. Qarbine can seamlessly query and analyze data from databases ranging from NoSQL to vector databases and feed into Microsoft Excel and other spreadsheet workflows. Qarbine’s flexibility ensures that users can easily manipulate, analyze, and visualize their data, further enhancing a business’s data-driven decision-making processes. Two commonly used file formats imported into Excel are:
- comma separated values (CSV) and
- tab separated values (TSV).
Data Oriented Tools
The Data Source Designer, Query by Example (QBE) tools each have a “Data” tab which displays the query’s answer set.
To initiate an export click the toolbar button highlighted below.
A dialog is shown with various options. The primary ones of interest (CSV and TSV) are shown below.
Choose the format.
Click
The next part of the dialog indicates where to send the content. For Excel use choose the option shown below.
Click
The final part of the dialog asks for a file name. Adjust it as desired.
To download the content click
The content is saved in the file within your browser’s download folder.
Report by Example (RBE)
The RBE tool has a “Data” tab which displays the answer set in a table similar to that of the Data Source Designer and QBE tools.
The result tab
also has an export option as shown below.
Consider this basic RBE component.
Its output is shown below.
Exporting this as CSV results in the following.
When exporting in CSV or TSV formats, any page headers and footers are not exported. Note the columnar aspects of the “Avg”, “Sum” and their values do not propagate in this example. For this, a template can be defined which better maintains such columnar aspects of the data.
Template Techniques
In some cases templates will be defined specifically for CSV and TSV exporting. This usually entails adding cells such that the number of cells across template lines are generally equal so that the columnar exporting yields the desired result.
Consider the Qarbine output and the resulting exported Excel spreadsheet below.
| Qarbine Output | Excel Spreadsheet |
|---|---|
 |  |
The Excel spreadsheet cell alignment is achieved by defining a few empty Qarbine cells. Note the “” cells for the first columnar values and for those in the “Color” column.
The CSV output file snippet is below.
Animals Grouped by Type
cat
,Name,Color,Age
,mouser,brown,4
,meower,brown,6
,kitty,grey,6
,kittycat,brown,6
,4, ,5.5,Avg
…
horse
,Name,Color,Age
,pokey,brown,16
,1, ,16,Avg
Summary,9, ,11.1,Avg
This example is in the catalog at “example/Qarbine techniques/Excel exporting example 1”.
Report Runner and Viewer Tools
These tools also have the option to export results to CSV and TSV files. The alignment of the resulting spreadsheet cells is dependent on the template definition. The techniques for alignment are discussed above.
Data Set Viewer
The Data Set Viewer can export into CSV and TSV in a similar manner as the Data Source Designer and other tools. The option is accessed using the boxed arrow option highlighted below.
From harem simply follow the steps described above.