Copy and Export

Data in an Ultorg perspective can be exported to external applications, such as Microsoft Excel, using either copy-and-paste or the Export to CSV action.

For relational databases, you can generate a SQL query for external use.

Copy and Paste to Excel

To copy data from a perspective, invoke Select All (Ctrl+A or ⌘A on Mac) and then Copy (Ctrl+C/⌘C). Or, you can select specific columns to copy using multiple selection. Then paste your data into Excel or an external application of your choice.

An Ultorg perspective may visualize one-to-many relationships that are not directly representable in Excel. Ultorg will “flatten” such relationships using a format that keeps Excel functions such as filtering and sorting working reasonably well.

Screenshot showing how nested data in an Ultorg perspective is flattened into a simple table of values that can be pasted into Microsoft Excel.

Parallel one-to-many relationships, such as CoursesOne-to-Many SymbolReadings vs. CoursesOne-to-Many SymbolMeetings in the example above, are handled specially, to avoid excessive duplication of data. In these cases, rows from each relationship are listed separately, with the asterisk character (*) used as a placeholder for data that is listed elsewhere.

Copy-and-paste is limited to the data that is currently loaded and shown on the screen. You can scroll down to load more data, or use the Export to CSV action to export all rows.

Output Format

For copy-and-paste to external applications, Ultorg uses the tab-separated values (TSV) format, with text values quoted as necessary. For numbers and timestamps, period or comma is used as a decimal character, based on your operating system's regional settings.

The TSV format will normally paste cleanly into Excel, except in the case where Excel's Text to Columns action has recently been used with a different character as a column separator. Restarting Excel will usually reset the latter setting.

Excel may sometimes perform undesired data type conversions. For example, an unsuspecting biologist may find the protein name SEPT2 to be changed to “02-Sep” when pasted into Excel. There is no fully reliable way to avoid this, short of renaming the protein.

Copied values are unaffected by Ultorg's format options. For example, numbers retain full precision even if they are normally shown with rounded decimals.

Export to CSV

To export complete query results, use the Export to CSV action from any perspective. You can invoke it from the File menu, or by right-clicking a selection in the Folders sidebar.

The CSV format is similar to the TSV format, but uses commas to separate fields on each row. Values containing commas, quotes, or line breaks are quoted as necessary. The CSV format is universally accepted by most data processing tools, including Microsoft Excel.

Multiple tables or perspectives can be exported in one operation, by holding down Ctrl or Shift to select multiple items in the Folders sidebar (Command/Shift on Mac).

The Export to CSV dialog, showing a list of perspectives to export, the output file for each, and the current progress.

You will be prompted for a folder to export to. You can specify the decimal character to use for numbers (comma vs. period), and the maximum number of rows to export. A warning will be shown after export if the row limit is reached for any query.

Nested data, from joins and one-to-many relationships, will be flattened into a simple table using the same format as was described for copy-and-paste.

When possible, exported data is retrieved in a single SQL query and streamed directly to disk. This means that millions of rows can be exported without having to fit in memory.

Compile to SQL

For relational database connections, the File→Compile to SQL action creates a SQL query from the current perspective. You can use this SQL query to retrieve the same data, in the same order, from an external application or programming language of your choice.

The Compile to SQL feature, with a visible output SQL query.

The generated query is tailored for the specific dialect of SQL that your data source expects, as indicated in the dialog box. The generated query works as follows:

  • Each visible primitive field in the perspective becomes a column in the SQL query's outer SELECT clause, in the same order.
  • Column names are set automatically with the AS keyword, to correspond with field names in the perspective. Fields without a name may be assigned a name automatically.
  • Some extra columns may be included at the end of the outer SELECT clause, if needed for ordering or row identification purposes. These columns may be disregarded.
  • Perspectives with multiple levels of subqueries are flattened using left joins and inner joins. For cases where flattening would lead to a combinatorial explosion of duplicated rows, an error message is shown with suggestions to modify the perspective.
    The dialog box that warns about parallel one-to-many relationships.
  • A sample LIMIT clause is included at the end of the query.

Queries generated by the Compile to SQL action are similar to those used internally by Ultorg, but have the more predictable structure outlined above.

For data sources that do not use SQL (e.g. Excel spreadsheets or CSV files), or perspectives over multiple data sources, an error message will be shown.

Print to PDF

You can print the open perspective to a PDF file or a physical printer. See Print and Page Setup.

JSON Output

A JSON version of the latest loaded perspective data is available via the SQL/JSON Output action in the Help→Diagnostics menu.