Addons/tables/csv

tables/csv - CSV utilities

• Provides verbs to read from and write to comma-separated-value (CSV) files or strings.

• supports appending arrays to an existing csv file,

• ability to convert fields to numeric type where possible
• old code that uses the base library csv script should not need any modification (apart from loading) to use this addon instead

• CSV is a specific case of delimiter-separated-value (DSV) format and the verbs in this addon are covers of those in tables/dsv addon

Browse history, source and examples in SVN.

Verbs available

Installation

Use JAL/Package Manager to install both the tables/csv and tables/dsv addons.

If you wish to replace the use of the base library csv script with the tables/csv addon, add the following lines to your ~config/startup.ijs script:

PUBLIC_j_=: (<<<({."1 PUBLIC_j_) i. <'csv'){PUBLIC_j_
buildpublic_j_ 0 : 0
csv       ~addons/tables/csv/csv
)

If you do this, then require 'csv' and load 'csv' will target the csv addon rather than the base library csv script.

Usage

Load csv addon with the following line

   load 'tables/csv'

Verbs are documented in the csv.ijs script.

   ]dat=: (34;'45';'hello';_5.34),: 12;'32';'goodbye';1.23
┌──┬──┬───────┬─────┐
│34│45│hello  │_5.34│
├──┼──┼───────┼─────┤
│12│32│goodbye│1.23 │
└──┴──┴───────┴─────┘
   datatype each dat
┌───────┬───────┬───────┬────────┐
│integer│literal│literal│floating│
├───────┼───────┼───────┼────────┤
│integer│literal│literal│floating│
└───────┴───────┴───────┴────────┘
   makecsv dat
34,"45","hello",-5.34
12,"32","goodbye",1.23

   dat writecsv jpath '~temp/test.csv' 
47
   ]datcsv=: freads jpath '~temp/test.csv'
34,"45","hello",-5.34
12,"32","goodbye",1.23

   fixcsv datcsv
┌──┬──┬───────┬─────┐
│34│45│hello  │-5.34│
├──┼──┼───────┼─────┤
│12│32│goodbye│1.23 │
└──┴──┴───────┴─────┘
   readcsv jpath '~temp/test.csv'
┌──┬──┬───────┬─────┐
│34│45│hello  │-5.34│
├──┼──┼───────┼─────┤
│12│32│goodbye│1.23 │
└──┴──┴───────┴─────┘

Note that if you wish to use custom field and/or string delimiters, please see the tables/dsv addon (the tables/csv addon is a special case of the tables/dsv addon with the field delimiter set to ',' and the string delimiter set to '"'.

To see more samples of usage, open and inspect the test_csv.ijs script.

Comparison with `csv.ijs` script in base library

The tables/csv addon is no longer as concise (and clean) as the original csv script in the base library. However it supports more features, fixes some bugs? and, in most cases, has better performance than the original.

Most of the verbs from the base library csv script are unchanged. The structural changes can be summarised as follows:

• The algorithm used by chopcsv to convert a line from a csv string into a list of boxed fields has been replaced

• the portion of writecsv used to make a csv string from a J array has been factored out into a separate verb - makecsv

• the algorithm used by makecsv to make a csv string from a J array has been replaced. The new algorithm used, now depends on the type of J array

• appendcsv was added to allow a J array to be converted to a csv string and appended to an existing file

• makenum was added to convert cells of arrays created with fixcsv to be converted to numeric types where possible

Features

Feature changes from the base library csv script:

• supports appending arrays to an existing csv file,

• optional user-defined field delimiter and string delimiter(s) - see Addons/tables/dsv

• only literal cells of J array are enclosed by string delimiters

• writecsv/makecsv can handle boxed arrays with cells containing numeric arrays, boxed or complex data

   ]tstarry=: ((34j3;2;<<4),:2;3 6;3)
┌────┬───┬───┐
│34j3│2  │┌─┐│
│    │   ││4││
│    │   │└─┘│
├────┼───┼───┤
│2   │3 6│3  │
└────┴───┴───┘
   load '~system/packages/files/csv.ijs'
   tstarry writecsv jpath '~temp/tstcsv.csv'
|domain error: writecsv
|   dat=.,each     8!:2 each x
   load 'tables/csv'
   tstarry writecsv jpath '~temp/tstcsv.csv'
19
   freads jpath '~temp/tstcsv.csv'
34j3,2,4
2,3 6,3

Fixed bugs?

• writecsv/makecsv does not append LF to an empty string.

• fixcsv correctly unescapes quotes embedded in fields

   tstcsv=: '"Symbol "" is Rank",38,"abc"',LF,'"Hello world",56,"efg"',LF
   load '~system/packages/files/csv.ijs'
   fixcsv tstcsv
┌─────────────────┬──┬───┐
│Symbol "" is Rank│38│abc│
├─────────────────┼──┼───┤
│Hello world      │56│efg│
└─────────────────┴──┴───┘
   load 'tables/csv'
   fixcsv tstcsv
┌────────────────┬──┬───┐
│Symbol " is Rank│38│abc│
├────────────────┼──┼───┤
│Hello world     │56│efg│
└────────────────┴──┴───┘

Performance

• Performance of fixcsv is pretty much unchanged (a bit faster if anything).

• The new algorithms in makecsv are generally 3-9 times leaner, and in most cases faster.

• Large arrays of a single type or with columns, each of a single type, are processed at least as fast as the old version and simple numeric arrays are over 4 times faster.
• For small arrays containing different datatypes the new version can be up to twice as slow as the old version, but because total time taken is small, this will not generally be practically significant.
• Large arrays with multiple types within a column are about 80% as fast as the old version, but use 8 times less space. See table below.

   simpcol
┌──┬────────────────┬─┬─┬────┬────┬──┐
│12│The black dog   │1│E│9.32│54  │XL│
├──┼────────────────┼─┼─┼────┼────┼──┤
│15│likes to        │0│R│4.45│5.24│  │
├──┼────────────────┼─┼─┼────┼────┼──┤
│22│eat             │1│E│    │455 │XS│
├──┼────────────────┼─┼─┼────┼────┼──┤
│96│juicy, red bones│1│W│5.45│924 │M │
└──┴────────────────┴─┴─┴────┴────┴──┘
   mixedcol
┌────┬─────────────┬────────┬───┬────────────────┬────┐
│12  │The black dog│1       │E  │9.32            │54  │
├────┼─────────────┼────────┼───┼────────────────┼────┤
│XL  │15           │likes to│0  │R               │4.45│
├────┼─────────────┼────────┼───┼────────────────┼────┤
│5.24│             │22      │eat│1               │E   │
├────┼─────────────┼────────┼───┼────────────────┼────┤
│    │455          │XS      │96 │juicy, red bones│1   │
└────┴─────────────┴────────┴───┴────────────────┴────┘

Authors

Adapted from the base library csv script by RicSherlock

Suggestions and/or SVN improvements to the addon are welcome.

See Also

• csvedit addon - GUI application for creating and editing CSV files.

• dsv addon - general utility for any delimiter-separated-value formated string.