how to work with csv files in vim

Question

I want csv file to be opened in vim in the same way it opens in microsoft office . Data should be in column format and commas should not be seen and its should be traversed easily. Is it possible in vim with help of any plug-ins?

Solution 1

You probably want to look at sc as an alternative.. Have a look at this linux journal page

Solution 2

I am probably a little bit later answering that question, but for completeness I'll answer anyway. I have made the csv plugin that should make it possible to do what you want.

Among others, it allows:

• Display on which column the cursor is as well as number of columns
• Search for text within a column using :SearchInColumn command
• Highlight the column on which the cursor is using :HiColumn command
• Visually arrange all columns using :ArrangeColumn command
• Delete a Column using :DeleteColumn command
• Display a vertical or horizontal header line using :Header or :VHeader command
• Sort a Column using :Sort command
• Copy Column to register using :Column command
• Move a column behind another column using :MoveCol command
• Calculate the Sum of all values within a column using :SumCol command (you can also define your own custom aggregate functions)
• Move through the columns using the normal mode commands (W forwards, H backwards, K upwards, J downwards)
• sets up a nice syntax highlighting, concealing the delimiter, if your Vim supports it

Solution 3

I've tried Christian's csv plugin, and it is useful for quick looks at csv files, especially when you need to look at many different files.

However, when I'm going to be looking at the same csv file more than a few times, I import the file into sqlite3, which makes further analysis much faster and easier to perform.

For instance, if my file looks like this:

file.csv:
field1name, field2name, field3name
field1data, field2data, field3data
field1data, field2data, field3data

I create a new sqlite db (from the command line):

commandprompt> sqlite3 mynew.db

Then create a table in the db to import the file into:

sqlite> create table mytable (field1name, field2name, field3name);
sqlite> .mode csv
sqlite> .headers ON
sqlite> .separator ,
sqlite> .import file.csv mytable

Now the new table 'mytable' contains the data from the file, but the first row is storing the header, which you don't typically want, so you need to delete it (use single quotes around the field value; if you use double quotes you'll delete all rows):

sqlite> delete from mytable where field1name = 'field1name';

Now you can easily look at the data, filter by complex formulas, sort by multiple fields, etc.

sqlite> select * from mytable limit 30;

(Sorry this turned into a sqlite tutorial but it seems like every time that I don't import into sqlite, I end up spending much more time using vim/less/grep/sort/cut than I would have had I just imported in the first place).

Solution 4

There's also exist rainbow_csv vim plugin. It will highlight csv/tsv file columns in different "rainbow" colors and will allow you to write SQL-like SELECT and UPDATE queries using Python or JavaScript expressions.

Solution 5

Here's some tips for working with CSV files in vim:

http://vim.wikia.com/wiki/Working_with_CSV_files

I'm not sure if there's a way to display it in columns, without commas, though the tips in that link allow vim to traverse and manipulate CSV very easily.

View more solutions

Comments

[dguaraglia]

Hey, great work on that plugin. I spent some time massaging GeoNames data today and it was a life saver!

[Nubzor]

I can't figure out how to show delimiters by default when I'm using csv plugin, otherwise it looks great ...

[Christian Brabandt]

Try setting the variable g:csv_no_conceal to 1. If this doesn't help, please open an issue at the github page. Thanks!

[gerlos]

This is incredibly useful! I saved me from adding bogus spaces to make columns in a csv file I needed to edit.

[joelostblom]

This plugin is wonderful! With the automatic column resize it is now my favourite csv/tsv-editor. Thank you!

[Dominykas Mostauskis]

Another alternative is xsv. It uses a pipeline approach, as opposed to spreadsheet. It stands out for being fast and memory efficient on big files github.com/BurntSushi/xsv

[crw]

After the plugin is activated on a buffer, how can I conveniently switch between viewing the contents with your plugin and viewing the raw data? Apologies in advance if the answer is obvious or appears elsewhere.

[Christian Brabandt]

@crw :set ft=text

[Paul Rougieux]

You might as well use R read.csv or python pandas.read_csv for that purpose.

[crw]

Thanks @ChristianBrabandt, and I see that it is :set ft=csv to go the other way.

[ecjb]

many thanks for writing of the package @ChristianBrabandt. I have neovim and followed the instruction on your website. now there is the csv.vim file into .conf/nvim/ftplugin and when I open a csv file, there is some automatic highlighting but when I enter a commend such as :ArrangeColumn, I have an error message E492: Not and editor command: ArrangeColumn. What am I doing wrong?

[Christian Brabandt]

@ecjb you might want to followup on the github page so we can discuss this in detail.