Interfaces/Text

Numeric tables

Courtesy of an email from Chris Burke on the original J mailing list (1996-12-05), here's one simple method to access data stored in a text file.

If you have numeric data stored in a file with column headers in the first row and with TAB characters separating entries, try:

   require 'files misc'
   rawdata =: 'm' fread fselect''
   data=: TAB chop"1 rawdata
   hdr=: {. data
   val=: 0 ".&> }. data

The call to fselect lets you navigate to the file using the operating system's normal file open dialog.

For example, if the file contains

Item	Price	Qty
1	13.5	3
2	10.3	5
3	5.25	25
4	13	-3

then executing those commands, one at a time, creates the two pronouns hdr and val:

   hdr
┌────┬─────┬───┐
│Item│Price│Qty│
└────┴─────┴───┘
   val
1 13.5  3
2 10.3  5
3 5.25 25
4   13 _3
   $ val
4 3

If columns of data are separated using one or more spaces rather than a TAB, make the third command

   data =: ' ' chop"1 rawdata

These ideas can be put together in a single verb (function) like this:

NB.*importTable v imports a numeric table with header row from a text file.
NB. returns: 2-item boxed list of header row and numeric data
NB. y is: name of file to import
NB. x is: optional delimiter (default space)
importTable=: 3 : 0
  ' ' importTable y      NB. default delimiter is space
:
  data=. 'm' fread y
  'hdr data'=. split x chop"1 data
  val=. _999&". >data
  hdr;val
)

So to import a text file called numtable.txt containing the table shown above (but delimited with spaces rather than TAB)

   importTable 'c:\temp\numtable.txt'
┌────────────────┬─────────┐
│┌────┬─────┬───┐│1 13.5  3│
││Item│Price│Qty││2 10.3  5│
│└────┴─────┴───┘│3 5.25 25│
│                │4   13 _3│
└────────────────┴─────────┘
   'hdr val'=: importTable 'c:\temp\numtable.txt'
   hdr
┌────┬─────┬───┐
│Item│Price│Qty│
└────┴─────┴───┘
   val
1 13.5  3
2 10.3  5
3 5.25 25
4   13 _3

Note that the techniques on this page will interpret multiple delimiters as a single delimiter.

For delimiter-separated-value (dsv) and comma-separated-value (csv) files, verbs in the addons Addons/tables/dsv and Addons/tables/csv will be useful.