11

What is a good way to edit CSV files in Ubuntu?

The files look something like this:

This,is,data,with,a,header
2,2,3,4,,
1,,3,,6,6
,5,3,5,5,6
1,2,,,,
1,2,3,4,8,6
1,,9,,5,9
-1,,3,4,5,6
1,2,0,4,5,6

I've been using OpenOffice, but it takes about 5 clicks to turn off the default behaviour of quoting all of the fields.

I'd like to find something lightweight and easy to use that will allow inserting/deleting data, and column-based sorting.

8 Answers 8

3

For vim, there's a nice plugin csv.vim.

1
  • I just come from that plugin looking for an alternative. It has huge performance issue when the csv are "larger"; currently it loops for a csv having 500 lines.
    – k0pernikus
    Mar 15, 2016 at 16:34
2

The java CsvEditors (e.g. csveditor, reCsvEditor) may be worth a look

2

You might use gnumeric to this end. On my system (Crunchbang) and with a file as small as in your example, leafpad consumes about 2M of RAM; gnumeric, 4M; and scalc (from LibreOffice), 34M. Gnumeric clearly is on the lightweight end, and it should detect your separator correctly on opening the file.

But (there is a but...) gnumeric won't let you save the modified file without going through a hurdle of menus. What follows is a BASH script to fix this. The script relies on xsel (a lightweight commmand-line clipboard manager) to paste the modified spreadsheet content back into your file. If sourced (not run), this script gives you access to two functions, gn to open the file in gnumeric:

gn filename

and gp to paste the content back into the file and close gnumeric:

gp

(Personally, I source this script in my .bashrc to have the gn and gp functions available whenever I open a terminal.)

#! /bin/bash

# once sourced by the shell, this script provides two functions:

# gn        to open a file with gnumeric
# gp        to update the file with gnumeric's selection

# requires grep, sed, awk, and the xsel utility


# name of the target file: used in gn () and gp ()
# ==================================================
gn_file=

# take note of target file and open it with gnumeric if not already opened
# ==================================================
gn () {
    # sanity checks
    if [[ -z $1 ]]; then
        echo 'Usage: gn file'
        return
    fi
    if ! [[ -f $1 && -r $1 ]]; then
        echo "Cannot find/use $1"
        return
    fi
    # yes, this is right; job report, if any, has "$gn_file" not expanded
    if jobs -l | grep 'Running.* gnumeric "$gn_file"' > /dev/null; then
        echo 'Already editing with gnumeric.'
        return
    fi
    echo 'Once done, select the part of the spreadsheet you want to save,'
    echo 'press Ctrl-C, go back to the command line, and type gp [ENTER].'
    # do the job
    gn_file=$1
    gnumeric "$gn_file" &
}

# paste selection into target file and close gnumeric
# ==================================================
gp () {
    # sanity checks
    if [[ -z $gn_file || ! -f $gn_file ]]; then
        echo 'Cannot find/use target file.'
        return
    fi
    local gnumeric_job=$( jobs -l | grep 'Running.* gnumeric "$gn_file"' )
    if [[ -z $gnumeric_job ]]; then
        echo 'No gnumeric instance to paste from.'
        return
    fi
    if [[ -z $( xsel -ob ) ]]; then
        echo 'Nothing to paste.'
        return
    fi
    local temp_file=$( mktemp "$PWD/temp.XXXXXX" )
    # paste X selection (o = output, b = clipboard mode)
    xsel -ob > "$temp_file"
    # replace tabs to get a CSV file
    local tab=$'\t'
    sed --in-place "s/$tab/,/g" "$temp_file"
    # must close gnumeric before updating file
    local job_id=$( echo "$gnumeric_job" | awk '{print $2}' )
    kill "$job_id"
    mv --backup "$temp_file" "$gn_file"
    echo "$gn_file updated."
}

As the script itself will tell you when opening your file with gnumeric, when you are done with your editing, you must select the portion of the spreadsheet you want to save before pressing Ctr-C (to copy this portion to the clipboard). Going back to the command line (Alt-Tab), entering gp will update your file with the content of the clipboard and close gnumeric. Your modified values won't have quotes around them, but they will be separated by tabs; hence, the script uses sed to replace tabs by commas.

I have found this to be an efficient way to work on CSV data files from the command line. The script should save the file correctly as long as it does not contain tabs within the comma-separated fields (which seems to be the case in your data-analysis example).

1

I know what you mean about {Libre,Open}Office. TBH, I never have found anything really good on Linux that is also lightweight.

The "best" (yes, in Ironic quotes) I've found so far is a java app called csveditor It's pretty clean, but doesn't really follow the best UI guidelines.

1

I suggest tablr plugin for Atom editor.

It's not the most lightweight option but achieves simple edits in fewest clicks.

enter image description here

1
0

I use DMcsvEditor. It's fast and lightweight, and has some basic useful options.

1
  • It looks like the DMcsvEditor was turned into CSVpad
    – Youda008
    Oct 13, 2018 at 9:45
0

I am using phpstorm and have to deal with CSV files a lot, and it supports editing them in a table view and it performs much better than the vim plugin csv.vim or the atom plugin tablr.

Screenshot of table view

The same should apply to their other editors, i.e. IntelliJ Idea, Android Studio, Pycharm, and RubyMine. It's not free software, yet some of their products have community editions.

(Yet it sill isn't perfect as sometimes it just shows file too large errors.)

-1

I saved your example as test.csv and opened it with LibreOffice with no trouble at all:

$ cat test.csv
This,is,data,with,a,header
2,2,3,4,,
1,,3,,6,6
,5,3,5,5,6
1,2,,,,
1,2,3,4,8,6
1,,9,,5,9
-1,,3,4,5,6
1,2,0,4,5,6
$ libreoffice test.csv 

I then got this dialog, and chose "comma" as the separator:

enter image description here

I clicked on OK and got this:

enter image description here

What more do you need?

7
  • The problem I'm having with openoffice is that sometimes the separator options change without notice, and the file gets saved with quote delimiters (OO seems to lack forcing defaults). It happens more when saving a file out rather than loading a file in. Feb 13, 2013 at 19:52
  • It's certainly possible with OpenOffice though, you're right. Feb 13, 2013 at 19:55
  • 1
    As far as "what more do you need", I've been in @AndrewWood 's situation and generally need less :) I've personally asked this question before. I love LibreOffice. I use it all the time. But sometimes you want a simple CSV editor that doesn't take up a huge chunk of RAM and just stays out of your way. Feb 13, 2013 at 21:30
  • @RichHomolka, to tell you the truth, I use gawk :)
    – terdon
    Feb 13, 2013 at 22:57
  • Performance. Once the csv get a bit larger, just loading them takes forever, and I don't even try to edit such files within OpenOffice anymore.
    – k0pernikus
    Mar 15, 2016 at 17:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.