Addons/tables/csvedit

User Guide j602 j601 j504 Addons Development Categories Versions SVN Build

Overview

CSV Editor is a utility application that allows to edit CSV files. There is a specific CSV file format convention described below. For more information about using the grid controls, such as keyboard and mouse, see Using Grid in the Help menu.

Getting Started

Install: use JAL to install as tables/csvedit addon
Run

   load 'tables/csvedit'
   csvedit ''                  NB. empty
   csvedit 'folder/file.csv'   NB. file parameter

Screen Layout

Title bar: Contains current file name or Untitled for a new grid. Asterisk after the file name means changes are pending.
Menu: Contains application specific operations other than standard grid commands.
Grid area: Consists of numeric cells for data and column and row headers
Status Bar: Indicates current selection in the zero-based format: Column Row [Initial-Column Initial-Row]

File Operations

Before the content of the current window is replaced with open file or cleared, user is asked to save any pending changes.

Creating a CSV file: Running CSV Editor starts with a blank grid, which can be saved into a new file. Menu File|New clears current grid, menu File|New Window creates a new blank window.
Opening a File: Menu File|Open Ctrl+O will read a CSV file into the current window.
Saving a File: Menu File|Save, File|Save As will save current grid into a CSV file
Closing a File: Menu File|Close closes current window, or exits the application, if it is the last window.

Grid Operations

Grid headers, if not present are defaulted to A,B,... for columns and 1,2,... for rows.

Selecting Cells: Cells are selected by dragging the mouse or Shift+Arrow keys. Whole column or row is selected by clicking a header cell.
Editing Cells: Editing in data area is restricted to numeric values. Headers are character values. Editing commands are according to Help|Using Grid.
Missing Values: Currently the blank value is 0 only.
Inserting and Deleting Rows and Columns: Menu Edit has operations to insert or delete rows or columns. Current selection determined affected rows and columns, that is they do not have to be selected completely.
Editing Headers: Menu Edit|Headers switches to editing headers, where they become regular cells.
Flipping: Menu Edit|Flip transposes both the data and the headers. Can be useful for plots.

Viewing Data

CSV: Shows the format of the resulting CSV file
Viewmat: Shows viewmat of the current data
Plot: Show line plot of the current data

CSV Format

For all practical purposes, the choice of presence of headers is replaced with a heuristic discovery based on the following rules

If top-left cell is blank then both headers are present
If top-left cell is numeric then there are no headers
If top-second-left cell is numeric then there are only row headers
If second-top-left cell is numeric then there are only columns headers
Otherwise an error is signaled

A typical CSV Format has the following form:

,"head 1","head 2"
one,1,2
two,3,4
three,5,6

Parsing is done using CSV J package, which uses comma as a separator and supports quotes (") around any cells. While saving format uses quotes only when necessary: around cells containing a comma. Quotes in cells are represented as double quotes.

Importing Data

It is possible to import text data from other file formats and applications into CSV Editor. This is done by converting the tabular text in the original application into TAB-separated form, and using clipboard to copy the region and paste into CSV Editor.

Many applications, such as Microsoft Word and Excel already have the ability to copy tabular data into clipboard in the TAB-separated format.

If the source is a text file, use a text editor and replace space or other separators into TABs, before you copy the region. Make sure that the first column starts with the data, not with a TAB.

While the main table should only contain numbers, it is possible to include header labels with alphanumeric text. To paste such region, select the Edit Headers mode in the Edit menu.

Before you paste the data into CSV Editor, select the exact rectangle of cells corresponding to the region (including headers if necessary). Observe the status bar, which shows the size of the current selection.

Note: alternatively you can use a text editor or other software to convert the data into the CSV Format described above.