Importing data with queries — Nixt Sheets docs

A query is a recorded import. It remembers three things: where the data comes from, the steps that tidy it — remove blank rows, change a column’s type, keep only some rows — and where the result goes. Because the steps are recorded, you can look at what each one did, change them, and run them again. Queries are saved in the workbook.

The tools are in Data › Get & transform: From text, From range, refresh all, and Queries.

Import pasted text

Use From text for delimited text you have copied — a CSV report in an email, a block copied from a web page.

Click the cell where the imported table should start.
Click Data › Get & transform › From text.
In the Import text dialog:
- In Called, type a name for the import, such as August invoices. It becomes the query’s name.
- Paste the text into the TEXT box.
- Leave The first row is headings on if the first line holds column names.
Click Next. (It is unavailable until the box holds some text.)
Check the steps and the preview in the query editor, described below.
Click Load.

The delimiter — comma, tab, semicolon or pipe — is worked out from the text. Quoted fields can contain the delimiter.

Import a range or a table

Use From range to shape data that is already in the workbook without changing the original.

Select the data, including its header row, or click a cell inside a table. If you select a single cell outside a table, all the data on the sheet is used.
Click Data › Get & transform › From range.
Check the steps and the preview in the query editor.
Click Load.

When the current cell is inside a table, the query reads the table by name, so it keeps covering rows added to the table later. Otherwise it reads the fixed range. The result is placed two rows below the source.

If there is nothing to import, the status bar says Select the data to import first.

The query editor

The query editor opens when you import, and when you edit a query. Its title is Import followed by the source for a new query, or the query’s name when you edit one.

Applied steps

The left side lists APPLIED STEPS. The first line is the source. Each step below it is applied in order.

For a new import, some steps are suggested for you:

Remove blank rows, when the data has rows with nothing in them.
A change of type to number, for a text column whose every value can be read as a number — for example numbers with thousands separators.
Trim, for a text column with spaces before or after its values.

With no steps, the editor says No steps yet. The data below is exactly what the source holds.

To	Do this
See the data as it was after a step	Click the step. The preview stops there and the count above it adds after step and the step’s number. Click the step again to see all steps.
Move a step earlier	Click its up arrow (Move earlier)
Remove a step	Click its × (Remove this step)
Add a step	Click Add step…

The preview

The right side shows the data as it stands, with the number of rows and columns above it — for example 120 rows · 5 columns. Numbers are shown in a monospaced font, which makes a column that is still text easy to spot. If there is no data, it says Nothing to show.

Finish

Button	What it does
Load (new query) or Apply (editing)	Saves the query and writes the result to the sheet
Connection only	Saves the query without writing anything to a sheet
Cancel	Closes the editor without saving

Add a step

In the query editor, click Add step….
In Add a step, choose the kind of step and fill in its fields.
Click Add.

Step	Fields	What it does
Keep rows where…	Column, condition, Value	Keeps only the rows that meet the condition: equals, does not equal, is greater than, is less than, is at least, is at most, contains, begins with, ends with, is blank or is not blank
Change type	Column, type	Converts the column’s values to Text, Number, Date or True/False. Any leaves them as they are.
Sort by	Column, Ascending or Descending	Sorts the rows
Rename column	Column, New name	Renames the column
Remove column	Column	Removes the column
Trim column	Column	Removes spaces before and after each value
Replace values	Column, Find, Replace with	Replaces text in the column’s values
Split column	Column, Split on	Splits the column into several at the text you give. Left empty, it splits on commas.
Group by	Column to group by, summary, column to summarise	One row per distinct value, with a summary column named after the summary and the column — for example Sum of Amount. Summaries: Sum, Average, Count rows, Count distinct, Min, Max, First, Last.
Remove blank rows	—	Removes rows with nothing in them
Remove duplicate rows	—	Removes rows that repeat an earlier row
Keep top rows	5, 10, 25, 50 or 100	Keeps only the first rows
Remove top rows	5, 10, 25, 50 or 100	Removes the first rows

Each step appears in the list with a description, such as Sort by Sales descending, Keep top 10 rows or Replace “n/a” with “0” in Status.

What loading does

A loaded query is written to the sheet as a table named after the query, so formulas such as =SUM(August_invoices[Amount]) keep working however many rows later refreshes bring. The status bar says, for example, August_invoices: 42 rows loaded as August_invoices. Refresh re-runs the steps.

Query names are made from the name you give: characters other than letters, digits and underscores become underscores, a name that starts with a digit gets Query in front, and a number is added if a query or table already has the name.

A query saved as Connection only says … saved as a connection. Nothing was written to a sheet.

If the result can’t be written, the status bar explains:

Message	What to do
The result will not fit below and to the right of the destination.	Import from a cell with more room.
There is no sheet called ”…” to load … into.	The destination sheet was renamed or deleted. Edit the query or import again.
There is already a query called …	Use another name.

The Queries panel

Click Data › Get & transform › Queries to open the Queries panel on the right. The button shows the number of queries once there are some.

For each query the panel shows its name, its source, and a line such as 3 steps · 42 rows in Sheet1!A12:E54, or connection only. Each query has three buttons:

Button	What it does
Refresh (Re-run the steps against the source)	Runs the query again and rewrites its result
Settings (Edit the steps)	Opens the query editor
Bin (Delete this query)	Deletes the query. The rows it loaded stay on the sheet, and the status bar says so.

The refresh button at the top of the panel (Refresh every query and pivot) refreshes everything. With no queries, the panel explains how to make one.

Refresh

To refresh one query, click its refresh button in the Queries panel.
To refresh every query in the workbook and every PivotTable on the current sheet, click the refresh button in Data › Get & transform (Refresh all — re-run every query and pivot).

A refresh clears exactly the cells the query wrote last time, then writes the new result, so a result that got shorter leaves nothing behind. The status bar says, for example, Refreshed 2 queries and 1 pivot — 180 rows., 1 of 2 queries could not be refreshed., or There is nothing to refresh — no queries and no pivot tables.

Links to other workbooks

A workbook made in another app can contain formulas that point at a different workbook, such as =[1]Budget!B4. Such a workbook carries the last value it saw for each referenced cell, and Nixt Sheets shows those values.

To see what the workbook links to:

Click Data › Connections › Links. The button reads N links when there are some.
Read the Links to other workbooks dialog. For each linked workbook it shows its number and file name, how many values it remembers across how many sheets, and where the file was.
Click Close.

If there are none, the dialog says No formula in this workbook points at another one.

The links and their values are kept when you save.