Ultorg can connect to data in Microsoft Excel files (XLSX and XLS files). Each worksheet (spreadsheet tab) is exposed as a data table () which you can query in Ultorg perspectives.

Data in Excel files can be edited from Ultorg and written back to the original Excel file.

Use cases for Excel connections include:

• You can query data in existing spreadsheets.
• You can create a complete, editable relational database out of Excel sheets. This lets you use all of Ultorg's functionality, for project management, customer relationship management, inventory tracking, or other tasks, without an external SQL database.
• You can combine data from Excel with data in other data sources.
• You can share data with other users, through Excel files stored on a network drive or in a Microsoft OneDrive folder.

Demo Videos

Excel Sheets as a Relational Database

In this demo, we show how you can build a complete relational database from scratch using only Ultorg and an Excel workbook (available here). As an example, we build a small “customer relationship management” database that tracks customers and customer interactions.

Joining a Database and a Spreadsheet

When you work with read-only data from some large external database, it is often useful to combine the data with some supplemental data that you prepare yourself in Excel or Google Sheets. You can make edits to the supplemental data directly from Ultorg. Changes that you make will be written back to the original spreadsheet, and the query updated.

In this demo, we use the technique above to experiment with hypothetical pricing adjustments in a database of products and orders.

Here is a closer screenshot of the Excel sheet and the Ultorg perspective from the previous video:

Spreadsheet Format

To make an Excel spreadsheet work well with Ultorg (and other tools) organize it as follows:

• Each worksheet (spreadsheet tab) should contain a single table of data.
• The top row in the worksheet, should contain names for each column.

  Alternatively, you can use View→Freeze Panes to designate multiple rows as part of the header. In this case, Ultorg will read column names from the last of the frozen rows.

• Each table column should contain data of one type only: text, numbers, dates, times, timestamps, or true/false. If you are designing a new table, you can provide a row of example data so that Ultorg can detect the column types.

Additionally, if you wish to edit the data from Ultorg, there is one more requirement:

• The leftmost column should contain values that uniquely define each row. These identifiers can be numbers (1, 2, 3, ...) or text (airport codes, JIRA tickets etc.).

  If the name of the first column is ID, and you use numeric identifiers, then Ultorg will assign auto-incremented values when you insert new rows. Prefixed numbers, such as DEPT-3215, can also be auto-incremented.

The requirements above are the same for both Excel and Google Sheets. For more tips applicable to either type of spreadsheet, see Designing an Editable Spreadsheet Database.

Here is a screenshot of a properly formatted Excel spreadsheet:

Excel File Types

Modern Excel files have the file extension xlsx. An older xls or xlsb workbook may be converted to the newer format by opening it in Excel and saving it again as an xlsx file.

Ultorg can connect to either xlsx or xls files. Data editing is supported for xlsx files only.

Connecting and Refreshing

To connect to an Excel file in Ultorg, click Add Data Source→Connect to File and select the file.

In the Folders sidebar, your Excel data source () will appear with available tables () underneath:

Refreshing Data

If you change the structure of a table, such as data types, column names, or the primary key column, invoke Refresh Table List/Metadata to have Ultorg detect the changes.

To refresh data only, use Refresh Data (, Ctrl+R/⌘R). Ultorg will detect if the Excel file has actually changed or not, and reload data as necessary.

For convenience, there is also an Open in Excel action in the context menu (see above). This opens the file in Excel, or whichever application is associated with Excel files on your computer.

Other Details

Connections to Excel files from Ultorg work on all operating systems, including Linux. It is not required to actually have Microsoft Excel installed.

Writing to Excel Files

• The Apply Edits action will perform sanity checks on affected sheets before proceeding to make changes. This includes checking for the continued presence of relevant rows and columns, and preventing duplicate values in the primary key column.
• If the XLSX file is already open in Excel when you press Apply Edits, Ultorg will prompt you to close it in Excel first.
  
• When writing data edits, Ultorg makes only minimal changes to the connected XLSX file. Only rows actually affected by edits will be modified. Formulas will not be overwritten.

  To avoid re-numbering existing rows, Ultorg may leave blank cells when deleting rows. This is normal. You can safely re-sort tables in Excel if desired.

• When writing new cell values, Ultorg will reuse the cell formatting of previous values in the same column (or technically, the last non-blank cell of the right data type).
• Ultorg keeps timestamped backups of modified XLSX files, in the folder Ultorg Excel Backups in the home directory of you operating system.