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

appendcsv	v	Appends an array to a csv file
fixcsv	v	Convert csv data into J array
makecsv	v	Makes a CSV string from an array
makenum	v	Converts cells in array of boxed literals to numeric where possible
enclose	v	Encloses string in quotes
readcsv	v	Reads csv file into a boxed array
writecsv	v	Writes an array to a csv file

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.

				Library csv.ijs		Addon csv.ijs		Ratio
Data type	Iterations	Code	Time	Space	Time	Space	Time	Space
Simple numeric	100	makecsv i. 50 70	0.0153	2913090	0.0035	417344	4.422	6.980
Simple numeric (big)	1	makecsv i.5000 70	2.3214	293626000	0.5485	45474900	4.232	6.457
Boxed numeric	100	makecsv <"0 i. 50 70	0.0148	2913340	0.0092	850624	1.602	3.425
Boxed numeric (big)	1	makecsv <"0 i.5000 70	2.3212	293626000	1.9981	87621100	1.162	3.351
Simple literal (big)	1	makecsv 5000 70$'abcd'	4.5013	644609000	4.0594	645135000	1.109	0.999
Columns of single type	100	makecsv simpcol	0.0002	38272	0.0003	9792	0.619	3.908
Columns of single type (big)	1	makecsv 5000$simpcol	0.3163	45443200	0.0904	5180220	3.499	8.772
Columns of mixed type	100	makecsv mixcol	0.0003	33536	0.0004	11648	0.589	2.879
Columns of mixed type (big)	1	makecsv 5000$mixcol	0.2862	38818700	0.3302	4959490	0.867	7.827
String (small)	100	fixcsv ssimpcol	0.0002	10624	0.0002	10496	1.029	1.012
String (big)	1	fixcsv 171250$ssimpcol	0.2588	4530620	0.2400	4530690	1.078	1.000

   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 Ric Sherlock

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.