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.
For vim, there's a nice plugin csv.vim.
The java CsvEditors (e.g. csveditor, reCsvEditor) may be worth a look
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).
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.
I suggest tablr plugin for Atom editor.
It's not the most lightweight option but achieves simple edits in fewest clicks.
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.
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.)
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:
I clicked on OK and got this:
What more do you need?
