Northern Commander

Tables

A CSV or TSV file opens as a table: the names of the columns along the top, numbers lined up on the right, sorted by any column. The table editor changes it cell by cell, and only what you change is rewritten. A JSON Lines file opens as a table too, below.

Sorted by length, longest first. The numbers down the left are each row's place in the file.

What the keys do

Key For a table
Enter Hands it to the application macOS opens it with
F3 Shows the table
⇧F3 Shows its text in the text viewer
F4 Opens it in the table editor, below; a JSON Lines file in the text editor

Opening files says how to change them.

Recognized by

The extensions .csv, .tsv and .tab. What separates the cells is worked out from the file, not from its name — below. JSON Lines files use .jsonl and .ndjson.

At a glance

KeyAction
F2Sort by this column
⇧F2Sort by it the other way
⇧EnterOpen cell as text
⌘CCopy
⌥⌘CCopy with options…
⇧F5Copy row
⌘LSelect row
⇧⌘LSelect column
⌘GGo to…
⌥⌘→Make the column wider
⌥⌘←Make the column narrower
F8Freeze the columns left of the cursor
⌥F4How it is read…
F7Search…
F3Find next
F6Show only the matching rows…
⇧F6Show all rows
Statistics of this columnStatistics of this column
⇧F9All it says about itself

Reading it

  • The arrows move a cell; Tab and ⇧Tab move a column. Page Up and Page Down move a screen, and Home and End go to the first and the last row.
  • ⌘← and ⌘→ go to the first and the last column, and ⌘↑ and ⌘↓ to the first and the last row. ⌘G asks for a row by the number down the left, which stays with its row when the table is sorted, and goes to it.
  • The names of the columns stay along the top, and each row’s place in the file is down the left.
  • A column is as wide as what is in it, up to 40 characters. ⌥⌘→ and ⌥⌘← make the cursor’s column one character wider or narrower, past 40 and down to one, and so does dragging the line between two names. The widths stay while the file is open. A longer value is cut with …, and the lines along the bottom, the cell line, shows the whole of the cursor’s cell, wrapped, up to four lines of it. The bar above the cell line names the cell’s column. A line break inside a cell shows as ↵ in the table.
  • Enter goes into the cell line, to read a longer cell: the arrows, Page Up and Page Down move through it, ⇧ with them selects, and ⌘C copies the selected text, or the whole cell when nothing is selected. Esc or Enter goes back to the table. On a cell of more than 100,000 characters, Enter opens the text viewer instead.
  • ⇧Enter (⇧Enter, or the Table menu) shows the cell in the text viewer — for a cell that holds a whole document or a long query.
  • A cell that is a number is on the right, in its own color. A number is written with a point for its decimals and nothing between its thousands: 1250.5, -3, 2e6 — 12,50 is text.
  • The strip under the table gives the rows, the columns, the order and how the file is read.

F8 keeps the columns left of the cursor in view while the others scroll sideways, so a column such as a name or ID column stays put; a double line marks where they end. Choose it again to let them scroll, or use the view options. Frozen columns take at most half the width; any further ones scroll. The columns stay frozen while the file is open.

With the mouse, click a cell to go to it, or a column’s name to sort by it; the line between two names is for dragging. The wheel scrolls without moving the cursor.

Selecting cells

  • ⇧ with the arrows, Home, End, Page Up or Page Down selects from the cursor to where it goes.
  • ⇧⌘← and ⇧⌘→ select from the cursor to the first or last column, and ⇧⌘↑ and ⇧⌘↓ to the first or last row.
  • ⌘L selects the cursor’s row, and ⇧⌘L its column. With cells already selected, they select every row (or column) the selected cells are in.
  • Drag across cells to select them, or ⇧-click to select from the cursor to the click.
  • A double-click selects a row, and a ⇧-double-click a column; dragging after a double-click adds more whole rows (or columns).
  • While cells are selected, the bar above the cell line says how many, and the sum and average of those that are numbers — numbers as written above.
  • Moving without ⇧, or sorting, clears the selection.

The same works in the table editor.

Sorting

F2 sorts by the cursor’s column, and ⇧F2 the other way; pressing the same one again puts the file’s order back. A click on a column’s name sorts by it, a second click the other way, and a third puts the file’s order back. An arrow in the heading shows which way.

  • A column in which every cell that says anything is a number sorts as numbers, so 9 comes before 10. Any other sorts as text, capitals and small letters together, and a number inside the text by its value, so Customer 2 comes before Customer 10.
  • Empty cells go last, whichever way round.
  • Rows that are equal keep the order they are in the file.
  • A sort asked for while the file is still being read waits for its last row.

Finding in it

F7, or ⌘F, asks what to look for:

Find
What to look for. Its list of earlier searches is the one every search in a file shares.
Search with
text, found anywhere in a cell; wildcard (* ?), matched against the whole cell, * standing for any characters and ? for one; or regular expression. Text that looks like a range of numbers or dates is matched as that range, below.
Only in column
Looks only in the cursor’s column, which it names.
Match case
As in the text viewer’s search.
Show only the matching rows
Hides the rows with no match instead of going to the next one, below.

Find next and Find previous go from the cursor’s cell along each row and down, in the order shown, and come round at the end. F3 and ⇧F3 look again.

Showing only some rows

F6 opens the Find box with Show only the matching rows ticked, and hides every row with no cell that matches. ⇧F6 shows them all again; both are in the Table menu too.

  • The rows keep their numbers, so a gap shows where rows are hidden, and the status line at the bottom says how many are shown: 12 of 480 rows shown.
  • Sorting, finding, selecting, copying and the column widths work on the rows shown, and ⌘G says when the row asked for is hidden.
  • Rows still being read are filtered as they arrive.

Text that looks like a range of numbers or dates is matched as that range, and the Find box says how it read it. Both ends are included. A cell that is not a number or a date is searched for the text as typed.

Type Matches
10-50, 10..50, 10–50, 10 to 50 Numbers from 10 to 50
> 100, >= 100 Numbers over 100, or 100 or more
< 0, <= 0 Numbers under 0, or 0 or less
= 5 Numbers equal to 5
2024-01-01 to 2024-06-30, > 2024-03-31 Dates, written year first; a date with no time is the whole day

A number is written with a point for its decimals and nothing between its thousands.

A column’s statistics

Statistics of this column, in the Table menu, shows what the values in the cursor’s column come to, as a table of its own in place of the file’s. The line above it names the column and the rows counted: every row, or only the rows shown while some are hidden.

Statistic What it is
Rows counted How many rows were looked at
Empty cells How many of them have nothing in the column
Different values How many different values the other cells hold
Smallest, Largest, Average For a column of numbers; Cells that are numbers says how many there are when not every cell is one
Earliest, Latest For a column of dates, written year first
Most common The values the most rows hold, up to 100, with how many rows hold each and what share of the rows that is
  • Enter on a value — a most common one, the smallest, the largest, or the empty cells — goes back to the table showing only the rows that hold it, exactly as written. ⇧F6 brings the rest back.
  • Esc goes back to the table as it was.
  • A large file takes a moment to count and says how far it has got; Esc stops it.
  • In the editor it works the same, from the Table menu.

Copying from it

  • ⌘C copies the selected cells, or the cursor’s cell when none are: a line for each row and a tab between the cells, which pastes into a spreadsheet. A cell holding the separator, a quote or a line break is put in quotes, so it pastes as one cell.
  • ⇧F5 copies the cursor’s whole row the same way.
  • ⌥⌘C asks first, and offers what you chose the last time:
Rows
Selected cells — the cursor’s cell when none are — All rows as shown, in the table’s order, or All rows in file order. With nothing selected, the last two take every column.
Separator
Tab or Comma.
Quotes
Only where needed, or Around every cell.
Column names on the first line
Puts the columns’ names first.

How it is read

What separates the cells is worked out from the first 200 rows: of comma, tab, semicolon and pipe, the one that gives the most rows the same number of cells. The first row has to agree, which is what keeps a semicolon file’s decimal commas from being taken for its separator. Where two tie, a .tsv or .tab file takes the tab and any other the comma.

The encoding is worked out as the text viewer works it out. A file that turns out not to be text opens in the text viewer.

⌥F4 changes how it is read:

A semicolon file with decimal commas, read as it should be.
Cells are separated by
Comma, tab, semicolon or pipe. Choosing another reads the file again.
First row names the columns
Ticked, the first row is the columns’ names. Unticked, it is a row like the others, and the columns are numbered.

The choice lasts while the file is open.

JSON Lines, and lists of records

A JSON Lines file has a JSON value on each line, usually a record: an object such as {"name": "Ada"}. Each line is a row, and each field name a column, in the order the names first appear; a record without a field is empty in its column. A field holding an object or a list shows it as compact JSON — Enter reads all of it in the cell line.

  • A line that is not JSON is a row of its own, its text as written in a column called Not JSON. The strip under the table counts them, and the rest of the file reads as usual.
  • A line holding a value that is not a record — a number, a word, a list — has it in a column called Value.
  • Blank lines are left out.

Everything else is as for any table: sorting, finding, showing only some rows, statistics and copying. There is no separator to choose, so ⌥F4 is not there, and the table can’t be changed: F4 opens the file’s text.

A JSON or YAML file whose top is a list of records opens as a tree. Show as a table shows it as a table like this one, and F5 goes back to the tree.

Big files

A table of any size opens at once: its first rows are on screen as soon as they are read, and the rest arrive as you look.

  • At most a million rows, or 32 million characters of them, are read. Past that, the strip under the table says not the whole file, and the panel says how much was.
  • Columns are made wide enough for the first 10,000 rows.
  • A single row longer than 1 MB stops the reading, and says so — almost always a quote that was never closed. In JSON Lines, a line longer than 1 MB does the same.
  • If the records use more than 1,000 different field names in all, reading stops at the line where that count is passed.

What the panel lists

⇧F9 lists the rows and columns, whether the first row is the names, how it is sorted and whether as numbers or text; how much was held and what the widths were fitted to; the separator, and whether it was worked out or chosen; the encoding; and how many rows have a different number of cells from the first.

Changing it

F4 opens the table editor: the same table, in the file’s own order, and every change you make is made to the file’s own text. A cell is quoted only where it has to be; every other cell’s quoting, the separator and each row’s line ending stay as they were, and so do the line breaks inside a cell you change. Each change is one step of ⌘Z.

Editing a cell in the cell line. It has a comma in it, so it is written in quotes.
Press And
Enter, or typing Edits the cell. Enter keeps the cell’s value to edit, in the cell line; typing replaces it, right there
⌘N Adds an empty row after the cursor’s
⇧⌘N Adds an empty column after the cursor’s
F5 Copies the row to just after it
⌫, ⌦ or ⇧⌦ Empties the cell, or every selected cell
F8 Takes the row out, or every row the selection reaches
⇧F8 Takes the column out, or every column the selection reaches
F6 Changes the column’s name, in the first row
⌘V Pastes cells copied from a spreadsheet, from the cursor or the selection, rightwards and down
⌥⌘V Asks how to paste first
⌥↑ and ⌥↓ Moves the row past the one before or after
⌥← and ⌥→ Moves the column past the one beside it
F4 Shows the same file as text, the cursor on the cell you were on; the same key comes back
  • While you edit a cell, Enter keeps what you typed and moves down, and Tab moves right, as in a spreadsheet; ⇧Enter and ⇧Tab move up and left, and Esc keeps the old value. Enter on the last row adds an empty row, and Tab on the last column goes on to the first cell of the next row. Any other command, such as a menu choice or a click, keeps what you typed first.
  • ⌥Enter starts a new line in the cell, and a pasted line break is kept; the table shows a break as ↵. The break matches the file’s own line endings.
  • Along the bottom, the arrows, Page Up and Page Down move through the value, ⇧ with them selects, and ⌘C and ⌘X copy and cut what is selected.
  • ⇧Enter shows the cell in the text viewer, and a cell of more than 100,000 characters opens there on Enter; change it by typing over it, or in the file’s text.
  • The first row stays first while it names the columns.
  • A row with fewer cells than a change reaches is given the empty cells it needs.
  • There is no sorting in the editor, since moving a row would mean nothing in a sorted table.
  • F6 and ⇧F6 are in the Table menu, since F6 renames the column here. The status line at the bottom says how many rows are shown. A row you change stays shown even when it no longer matches, until you ask again, and a row you add is shown. F8 deletes only the shown rows the selection reaches (rows hidden by the filter are not deleted), a paste goes over the shown rows one after another, and rows can’t be moved while some are hidden.
  • Each change, however many cells it reaches, is one step of ⌘Z.
  • The function keys belong to editing here: F2 saves. ⌘C, ⌥⌘C and ⇧F5 copy as in the viewer, and ⌥F4 works here too. So do ⌥⌘→ and ⌥⌘←; a width follows its column when columns are added, deleted or moved. F8 works from the Table menu, since F8 deletes rows here.
  • While the table is up, the editor’s commands for text wait until you switch back to the text.

A file whose text does not read as a table opens on the text, at the row that broke. More about the editor: Editing files.

Pasting from a spreadsheet

⌘V puts the cells on the clipboard into the table, from the cursor or the selection, rightwards and down, over the cells there. Each line is a row and a tab separates the cells; a cell the spreadsheet put in quotes stays one cell, line breaks and all. Cells past the last row or column add rows and columns. A single value pasted with several cells selected goes into every one of them. The paste is one step of ⌘Z.

⌥⌘V asks first. Next time it offers what you chose this time.

Put the cells
Replacing the cells from the cursor, or As new rows above the cursor.
Separator
Tab or Comma.
The first copied row is the column names
The names go over the table’s first row, or are left out when the table’s first row does not name the columns.

Splitting the rows into files

Split rows into files…, in the Table menu, writes the rows of the table into new files: one for each value in a column, named like sales-East.csv, or a new one every so many rows, named like sales-1.csv. It says how many files that makes before writing any, and asks once more when it is over a hundred.

It splits the table as it is in the editor, unsaved changes and rows the filter hides too; the open file stays as it is. Each file keeps the delimiter, encoding and line endings, and starts with the column names when the first row names the columns. A value that can’t be a file name has what it can’t hold replaced with _, an empty one is called (empty), and values that differ only in case get a number, since a disk would take them for one file.

The files go beside the open file unless you say otherwise. A name that is taken is asked about as a copy asks. A row with a quotation mark that never closes stops it, and nothing is written.

Removing duplicate rows

Remove duplicate rows…, in the Table menu, takes out every row that matches an earlier one, in every column or in the columns you check. Columns you have selected start checked. It says how many that is first, and the first of each set stays where it is. The row of column names is never a duplicate. “Ignore case and spaces at the ends” makes Abc and abc the same.

With a filter on, only the rows shown count, and the others stay. It is one step of ⌘Z, and nothing is saved until you save.

Joining and splitting files

These work on files in a pane, without opening them, and on any size of file. Mark CSV or TSV files, or point at one, and find them in the Files menu, in the row’s menu, or with ⌘K. What they make goes in the other pane’s folder. They run like a copy, in the background if you want, and are in the Journal. A file with a quotation mark that never closes stops them and nothing is left behind.

Join rows into one file… writes the rows of the files marked into one new file, in the order the pane lists them. Each file is read with its own delimiter and encoding, and the new one is written with the first’s. If the first row of each names its columns, columns are matched by name and every one is kept, with empty cells where a file doesn’t have it; the dialog says so when they differ. Without names, the files simply follow each other and every row is kept. Another option adds a last column saying which file each row came from.

Split rows into files… writes each file marked into several new files, named after it, like orders-2023-East.csv. It can split by a value in a column, by a number of rows, or by file size: a file stops before it passes the size, always between rows. To split by a column, every file has to have it, and the dialog says which one doesn’t. The new files are only given their names once they are all whole, so a name that is taken is asked about at the end.

Limits

  • Only comma, tab, semicolon and pipe separate cells.
  • Numbers are recognized only as written above; a number with a decimal comma is text, and sorts as text.
  • Nothing is worked out beyond numbers: dates sort as text.