[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Table I/O [WAS: io-2.4.13 released]

From: Andrew Janke
Subject: Re: Table I/O [WAS: io-2.4.13 released]
Date: Fri, 18 Oct 2019 14:58:56 -0700
User-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:60.0) Gecko/20100101 Thunderbird/60.9.0

On 10/18/19 2:19 PM, PhilipNienhuis wrote:
apjanke-floss wrote
On 10/18/19 2:29 AM, Philip Nienhuis wrote:
Kai Torben Ohlhus wrote:
On 10/18/19 5:30 PM, PhilipNienhuis wrote:

For readTable() and friends Octave needs to have the Table class
implemented. To my knowlegde there are no definite plans for that;
there is
a prospect of a start by Markus Bergholz somewhere in (IIRC) the bug
tracker, from several years back.

Regarding this, I want to remind of Andrews project [1].

Thanks, I forgot about that. Yeah, an impressive piece of work. But,
still no Table I/O there AFAICS.
On github (or gitlab?) I saw a readTable() that effectively is a wrapper
for xlsread. Could be a temporary solution.

Thanks Kai and Philip! Table I/O is on my TODO list for Tablicious. Now
that I know there's appetite for it, I'll bump up its priority.


It looks like Forge io's spreadsheet reading is mature enough that I
could build Table I/O on top of that. And that seems like a good design:
no need for another package to re-implement basic spreadsheet I/O.

With one exception I can see: cell formatting. I need to be able to
detect which columns are formatted as dates, so they can automatically
be converted to @datetime objects. (And eventually I'd like to provide
the option to efficiently read selected columns as @categorical, but
that's much lower priority.)

Is there an efficient way in io's spreadsheet support to detect the
"type"/format of cells/columns? Or would you be willing to work with me
to add one to io? (Maybe by just exposing the cell format as yet another
output arg of xlsread()?)

Same with writing tables: I'd need a way to control cell formatting so
that dates could be formatted as dates explicitly instead of shooting
them in as strings and relying on Excel's auto-parsing functionality
(which I think only works when you're using the Excel COM interface
anyway). Would also be nice to do stuff like bold column headers and
freeze panes.

First off, sorry for a long reply.

I think, no I'm sure that all that you want there is possible. But it isn't
going to be easy.

Glad to hear!

The first thing you'll hit (at least, I hit it) is that Octave itself has no
Date or Time type. Only since classdef got implemented there may be a way
out, but for classdef objects there still is no reliable I/O to e.g., .mat
This (no dedicated date or time type) is one of the reasons I left dates and
times aside for spreadsheet I/O. In fact, for file types and support
libraries that do offer date/time types I made the io package convert
date/time values into Octave datenums = doubles.

Tablicious provides a Matlab-compatible Octave @datetime class. If io can pass me datenums (or strings) and some indication that "this cell is a date value", Tablicious can do the rest.

The mat file I/O I'll leave for upstream core Octave to sort out.

Then, when you write about cell formatting to uncover cell type I can't help
smelling "Excel". But .ods has a much richer cell type spectrum, maybe you
can largely skip formatting there.

You definitely smell Excel. That's all I'm familiar with; the .ods format is new to me. If it exposes a true DateTime cell type indication, then probably no need for sniffing the formatting.

Furthermore, it looks like you imply something like dataframes with headers
and row id's but the spreadsheet file types I know of really only have
individual cell types inside. Spreadsheet I/O usually happens at a fairly
low level (i.e., individual cells). Formatting ("styles") might happen over
ranges but not necessarily along table paradigms.

Yep, table works like a dataframe, and contains homogeneously-typed columns. Converting from spreadsheet per-cell typing/formatting to table's column-oriented typing would be done with heuristics like "if all cells in a column contain date values, store that column as a @datetime vector; if they're all numbers (detected using other heuristics), use a primitive double vector; otherwise, use a heterogeneous cell vector".

But I know of Java support SW (e.g., jOpendocument) that might also offer
higher level I/O - the level of entire tables. ActiveX also does that
("Ranges"). I once tried such I/O with jOpenDocument but it was too
complicated for me at the time as there's also a lot of Java itself
involved, i.e., it seemed I needed to build my own .jars. (Oh, and now I
remember) javaArrays didn't work well at the time; even these days I have
the impression they're not so robust (but I may well be wrong).

When I've done this in the past for Matlab, I've used Apache POI. It worked fine, except for performance: to be fast at reading large arrays of numerics, you need to write a small custom buffering layer in Java so that you're not looping over per-cell read() calls in an M-code level loop to do the type discovery heuristics and array buffering there. So I ended up needing to build my own JARs, and expect we would for Octave too if we wanted to Go Fast using a Java spreadsheet interface. A Range in either ActiveX or POI (and I assume jOpendocument) doesn't work for Matlab/Octave well because the contents of each cell are themselves individual objects that need to be accessed with method or attribute calls, whereas to be Fast, you need to pass an entire *primitive* numeric array across the M-code/external language boundary.

But I'm happy to have it be slow for now, as long as the basics work. Then, doing accelerated spreadsheet I/O could be an enhancement for the io package some day, and if Tablicious were built on top of it, it would get the speedup automatically when it happened.

A bit of context:

.xls files (the old BIFF5 and BIFF8 ones) have no dedicated Date, Time or
DateTime cell type. In fact they basically only have Double, Text and
Boolean. The contents are further indicated by cell formatting; that way one
can find out -in theory!- which text cells are formulas and/or date/time. I
think this is what you referred to when mentioning "cell formatting"

Yep, that's what I meant. Dates are detected by seeing that a cell holds a Double, and then looking at the cell format to see if it's being presented as a date.

BUT: AFAICS most of the Java libraries for spreadsheet I/O that I used (and
on Windows, ActiveX) shield this from you and offer at most a Formula cell
type. So DateTime etc. is harder to uncover; although in Visual Basic
(ActiveX) there is a dedicated type (see __COM__.cc in the OF windows

The newer .xlsx files do have dedicated Date/Time cell types, see e.g.
__OCT_xlsx2oct__.m; the OCT interface explicitly processes them into
datenums :-)

I didn't know this! That'll make things easier. Or, rather, more complicated, since a robust readtable() will need to support both .xls and .xlsx, so it'll need code for both kinds of cell type detection.

.ods has a richer type spectrum, it does have Date, Time and even Currency
cell types. But again, not all spreadsheet interfaces return all cell types.

All in all, asking to uncover spreadsheet cell types beyond Double, Boolean,
String and Formula is asking to open a big and fully stuffed can of worms
:-)   It is all file type and interface dependent.

I know it's a big can of worms and a mess, and will never be 100% Right; I've done this whole shebang before for Matlab. But dates/times are *so* important in data analysis, I want to support those out of the box in Tablicious.

I don't care about Currency or other advanced types, I think. They don't crop up as much in data analysis, there's probably no corresponding Octave type to convert them to, and Matlab doesn't support them AFAIK, so there's no Matlab-compatibility need for them.

FYI, down in the io package NEWS file there's a table outlining which file
formats can be processed by what interfaces. That'll give a first indication
of complexity.

I am surely willing to help you out, given that I do not have not so much
time anymore for Octave these days. But yeah I think it'll be fun :-)
As the io package's spreadsheet I/O is so old (it started > 10 years ago)
and has been Just Working all that time I have to admit that my
"operational" knowledge of its innards got fairly rusty.

Thanks! I'll have a look through that table and start reading the io code to get familiar with it.

Maybe open a task in the Task Tracker? that tracker is largely, but IMO
unduly, dormant.

Where can I find the Task Tracker? I don't see it linked from the io Octave Forge package page.


reply via email to

[Prev in Thread] Current Thread [Next in Thread]