
Turn Vim into Excel: Tips for Editing Tabular Data (2013) - luu
http://alangrow.com/blog/turn-vim-into-excel-tips-for-tabular-data-editing
======
preek
Since no one mentioned it so far: Emacs has a mode built-in called Org Mode
which has fantastic table support:
[https://orgmode.org/manual/Tables.html](https://orgmode.org/manual/Tables.html)

I'm using it every day for so many things, importing and exporting of data,
calculations, writing offers to clients, there's an infinite amount of good
things to be done with spreadsheets within your text document.

~~~
bribri
Yes tables in org mode are very nice

------
watchdogtimer
If you want to use a spreadsheet with vi key bindings, most linux distros have
sc (spreadsheet calculator) in their repositories. It's kind of a cross
between vi and the DOS version of Lotus 1-2-3. A newer, improved version of sc
with features including undo/redo and csv/tsv export and import is sc-im
([https://github.com/andmarti1424/sc-im](https://github.com/andmarti1424/sc-
im)).

~~~
caymanjim
Vim keybindings are not Vim. The same claim is made about Vim emulators in
IDEs. IntelliJ has the best Vim emulator I've ever seen, and it's still
useless to a Vim power user.

~~~
btschaegg
While I don't know IntelliJ's emulation, I have to say that I find Evil-Mode
for Emacs to be nothing short of amazing.

~~~
vvillena
As a user of both (I use IntelliJ for Scala development and Spacemacs for
everything else), I'd say Evil is way way better. The IntelliJ vim plugin is
awesome, but it will never feel as integrated as Evil does. Emacs is way more
extensible.

~~~
mclehman
I think that's the most important differentiator evil has.

Embedding vim-style editing in an IDE or less extensible editor might get you
90% of what you want from Vim, but that 10% can be a dealbreaker depending on
your workflow. Without easy extensibility, you might not be able to get that
last 10% at all.

I'd say evil starts out a little better, maybe 95% out of the box, but now you
have all the power of emacs to make up that last 5% and then start to go even
further if you're not content then.

------
led
I've been finding visidata ([http://visidata.org](http://visidata.org)) the
perfect vim-inspired spreadsheet tool.

~~~
camel_Snake
Seconded. I enjoy using this software.

------
weinzierl
I found that for most problems where people use Excel I prefer CSV, various
command line tools (awk, perl and the awesome xsv) and Vim. I'm regularly
faster that way. When data manipulation is involved my solutions are robuster,
more reliable, work well even for huge data sets and most importantly: they
are reproducible.

That being said, there are two features that Excel - or any other spreadsheet
software for that matter - provides, which are missing from any Vim solution I
have found:

1\. Hiding overly long cells.

In most tables I see most of the cells have reasonably short lengths, but
almost always there are a few overly long outliers. What I want is a way to
manually set the column width and then all outliers cut-off. When vim's
conceal feature came out I hoped someone would make a plugin to that effect,
but as far as I know no one ever did. Looking into the problem myself a while
ago, I found that it's not trivial to do.

2\. Quick and easy filtering.

I find the column filters in Excel pretty useful. The first value they provide
is that they give an overview what values are actually used in the column.
Second value is to temporarily hide all the rows one is not interested in.
They are also very easy to use. I would like to have some easy way to do that
in Vim just for viewing and without actually manipulating the file.

~~~
danso
xsv is great -- as is csvkit, which preceded it and is probably a bit slower,
but has a few extra utilities that I find essential:

\- in2csv: extracts Excel worksheets into CSV

\- csvsql: produces (and executes) the SQL needed to create a table and insert
the data from a CSV

I found these scripts to be profoundly useful when working with govt data and
needing to provide a automated workflow that collects and collates their Excel
spreadsheets and converts them into CSV and SQLite databases.

------
meuk
I'm not convinced. Excel is used predominantly used to evaluate formula's that
are based on values in other cells. While I'm not a fan of Excel, vim simply
does not have this capability and really can't replace Excel for most
reasonable use-cases.

~~~
toasterlovin
Exactly.

Excel is so much more than tabular data. It is a directed acyclic graph, a
functional, reactive programming lanaguage with immutable data structures, and
a display layer for the results of your computation.

------
u801e
The filter command in vim allows you to use any external program to manipulate
the contents of the buffer. So, if you have a CSV or TSV and want to do
something like compute the sum of numbers in a column, you could write a awk
or perl one-liner with the appropriate field-separator parameter and do it.

So, for a CSV, you could do something like this in perl

    
    
        :% !perl -F/,/ -ane '$total += $F[3] if $F[4] =~ /Merchandise/; END {print $total;}'
    

This command will filter every line in the vim buffer through the perl one-
liner. The perl one-liner splits the line by comma, and adds the amount in the
fourth column to the total amount if the fifth column contains the word
"Merchandise". The lines in the vim buffer are replaced by the computed total.
I actually use this to determine how much I spend for each category in my
Discover Card statement after downloading it as a CSV.

~~~
chongli
This won't work if there are commas within fields, as permitted by CSV quoting
rules.

------
mohitmun
Checkout sc-im([https://github.com/andmarti1424/sc-
im](https://github.com/andmarti1424/sc-im)) if you are heavy vim user, you
would definitely love it

------
tgb
Why is tab separated value format superior to CSV? I understand why CSV is a
poor choice and commas are more likely to be needed in cell contents than
tabs, but there's not a standard format for TSV anymore than there is for CSV.

------
hiperlink
There is also SC (
[https://www.linuxjournal.com/article/10699](https://www.linuxjournal.com/article/10699)
) and the similarly named [https://github.com/andmarti1424/sc-
im](https://github.com/andmarti1424/sc-im)

But I'm happily reaching out to gawk most of the time for CSV processing.

------
aldoushuxley001
Why use Tab separated values instead of Comma separated values?

Is it possible to just use some random character, maybe even from different
charset, to act as delimiter for importing/exporting tabular data?

~~~
em500
There's a bunch of ASCII characters (28 - 31) specifically meant to be used as
delimiters, but they never caught on.

But even if they did, you still always need to handle the case where one might
legitimately want to use the delimiter character inside a field value.

~~~
kickingvegas
Wow, TIL this actually exists. Mind is blown on how they never got adopted
outside of folks thinking (foolishly) that these characters were not "plain"
text.

------
kev6168
It's VS Code + the vscodevim extension for me lately and it's been great. I am
not a first-rate Vim user anyway, so the convenience and ecosystem of VS Code
won me over easily.

~~~
btschaegg
Well... I'm using the same extension in VS Code, and I find the experience
rather... meh. It works, it makes VS Code _usable_ , but if you're accustomed
to the performance of the _real_ Vim, it's still underwhelming (especially if
you're used to macros).

~~~
kev6168
I have tried previously to switch from vim to other editors (IDEA and sublime
text). It felt painful. But this time (to VS Code) it has been smooth so far.
I think it might be the result of me forcing myself to forget about the vim
way completely. For example I have been deliberately using mouse more often,
and supressing the thoughts and longing of registers and macros etc. I am not
a vim power user to begin with so It's not that difficult. Losing vim power
but gaining the benefits of VS code, so far so good.

------
apas
This sounds fun but honestly reminds me of all these emacs folks who've turned
a text editor into a byzantine operating system with email, RSS, microwave
cooking, and laundry folding.

~~~
UlisesAC4
Well technically emacs being a lisp interpreter gives you infinity
possibilities.

I have heard that there was a saying that the only job of a Mac air was to
open emacs.

