1. Overview
Sometimes we may wish to edit tabular data on our Linux filesystem. We may not have a GUI available, or any Office-type software installed.
In this tutorial, we’ll talk about spreadsheet editors available within our terminal. We’ll look at tools that are easily accessible from the command line or ssh.
2. Do We Really Need a Spreadsheet Editor for Our Task?
Depending on the complexity of the task that we need to do, these tools might be overkill. For example, if we just need to modify a couple of values from a CSV file, we can use vim or another plain-text editor.
Similarly, there are other tasks that we can do in Bash like parsing a CSV or merging multiple CSV files without the need of a spreadsheet editor.
However, we might want to edit spreadsheet files in detail for our tasks, and Linux has us covered.
Let’s go through some of the available tools, their advantages and drawbacks.
3. The Spreadsheet Calculator sc and sc-im
One of the most relevant tools to edit spreadsheets is sc. Once it’s installed in our system, we can see the spreadsheet view:
In this spreadsheet view, we can create and edit spreadsheets. Although sc can export other formats apart from plain-text files, sc cannot import formats such as CSV.
3.1. Creating Our First Table With sc
In order to interact with the spreadsheet, sc uses vi bindings.
We can move throughout the spreadsheet with either the cursor keys or h-j-k-l or with gCR (where C is the column and R is the row, for example, gF15).
We can add and edit the value or form with = followed by the value itself. Interaction with rows and columns is also possible: ir and ic insert a row and column, while dr and dc delete a row and column, respectively.
Once we’re done, we can write the spreadsheet as a plain-text file with W <filename>. This won’t preserve the functions that we’ve introduced. If we want to keep these, we use P <filename> to save a .sc file that we can reopen with G <filename>.
We can concatenate a series of actions to perform after starting sc:
> = 1
> gA1
> = 2
> j
> = 3
> gB0
> = @SUM(A0:A3)
> j
> dr
> P test.scThis returns a spreadsheet that looks like this:
If we want to dive deeper into the capabilities of sc, we can press ? in the spreadsheet view to see the help menu. To exit sc, we just need to press q.
3.2. The Spreadsheet Calculator Improved sc-im
The old and slowly-maintained sc has evolved into an improved version: sc-im. It includes many new features on top of those of sc: undo/redo operations, import and export of formats such as CSV and XLSX, interaction with GNUPlot, and scripting with LUA.
When we open sc-im we see an improved, but similar, interface to sc‘s but with extra information on top:
There are some sc commands that have changed (such as gCR) but the vast majority of bindings remain the same.
Overall, sc-im is considerably more complex than sc and it includes many other features.
4. Table Editor and Planner or teapot
teapot is also spreadsheet editor. Depending on our package manager it may also be called teapot-planner.
teapot allows us to manipulate spreadsheets in three dimensions. When we open it, we can see a spreadsheet view similar to sc but with numbers on both axes:
teapot’s editor has many features. Even if teapot is not very well documented, it’s still maintained; the last release dates from 2021.
4.1. Basic Usage of teapot
teapot uses emacs bindings to interact with the spreadsheet, for example: up with Ctrl+p, down with Ctrl+n, right with Ctrl+f, and left with Ctrl+b.
We can refer to the value from cells with @. There are interactive menus (by pressing F10). To save a file, we press ESC z and to quit, we press Ctrl+x Ctrl+c.
We can combine these actions one after the other:
> 1
> Ctrl+n
> 2
> Ctrl+n
> 3
> Ctrl+p
> Ctrl+p
> Ctrl+f
> @(0,0)+@(0,1)+@(0,2)
> ESC z test.tpWe end up with a spreadsheet that looks like this:
teapot saves spreadsheets natively in XDR for mathematical precision, and it also supports CSV files.
5. GNU oleo
oleo was developed under the GNU project. It’s discontinued and its last release dates back from 1999, whereas the last development release is from 2001. This means that it might be potentially complex to install on recent machines.
However, if we want to do so, we should skip the documentation build and X11 installation to avoid conflicts, and maybe even fix issues in the linker phase for some C++ headers.
Anyway, oleo is still considered a staple tool for spreadsheet manipulation. When we open it, we can see the common spreadsheet interface:
oleo lacks support for CSV or XLSX formats, having a custom format for its spreadsheets.
5.1. Interaction With oleo
oleo also uses emacs bindings, which results in a familiar environment for emacs users. It implements multiple ways to refer to cells, giving a lot of flexibility while increasing usage complexity. For example, cell R1C1 is also cell A1.
We can move up with Ctrl+p, down with Ctrl+n, right with Ctrl+f, and left with Ctrl+b. Otherwise, we can go to a given cell with Ctrl+x j <CELL>.
We can delete the current row with Ctrl+k and the current column with Alt+k. To save the spreadsheet we press Ctrl+x Ctrl+s.
We can combine these actions one after the other:
> 1
> Ctrl+n
> 2
> Ctrl+n
> 3
> Ctrl+x j R1C2
> sum(r1:3c1)
> Ctrl+n
> Ctrl+k
> Ctrl+x Ctrl+s test.oleoWe end up with a spreadsheet that looks like this:
There are many features inside oleo. We can also use the help menu with Ctrl+h. To exit the program we need to press Ctrl+x Ctrl+c.
6. Visidata
visidata is an interactive Python open-source multitool to mainly visualize spreadsheets and tabular data in multiple formats, including CSV, XLSX, and even SQL tables.
When we open it, we’re not presented with the spreadsheet interface as with the other tools but with a folder directory explorer:
Even though we can still create and edit spreadsheets, visidata is more targeted at data visualization and analysis.
6.1. Table Visualization and Analysis With visidata
We can open tables and see their content. To understand the potential of visidata, let’s create a frequency table. We move similarly to sc, either with h,j,k,l (imitating vim) or with the arrows. However, if we use a cursor in our device, we can directly interact with the terminal user interface.
Once we’re over the desired column, we can press Shift+f to create the frequency table:
We can see that it has automatically created a histogram with all the data in the table.
6.2. Table Creation With visidata
We can create a new file by pressing Shift+a. This creates an empty table with a single column.
To enter a value in a cell, we press e and the value we want to add.
Finally, to save we can use Ctrl+s. Columns have names, the first one by default is A.
To add rows we use ga and the number of new rows, whereas to add columns we use za and the name of the new column. However, we cannot include anything but values in the cells. This means that functions aren’t supported in cells, even if we can perform statistics in bulk for rows and columns.
Since visidata has a lot of features, the best option when in doubt is to use the help menu. We can access this help menu with Ctrl+h.
There are other features that would remind us of more recent spreadsheet editors. visidata allows undo with Shift+U and redo with Shift+R.
To force the quit of visidata completely we press Ctrl+q.
7. Conclusion
In this article, we’ve looked at several terminal spreadsheet editors. To choose between them, we need to consider our task. If we need to sparsely edit some cells without the use of functions and visualize data, visidata is the right tool.
If we’re more familiar with emacs, we can opt for either oleo or teapot. The former might be difficult to install, while the latter lacks more documentation.
If we’re used to working with vi or vim, we might prefer sc-im. sc can still be useful in some situations, but sc-im has superior features.