taskGNU Octave - Tasks: task #15419, Implement Table I/O

 
 

You are not allowed to post comments on this tracker with your current authentication level.

task #15419: Implement Table I/O

Submitter:  Philip Nienhuis <philipnienhuis>
Submitted:  Sat 19 Oct 2019 12:42:32 PM UTC
   
 
Should Start On:  Fri 18 Oct 2019 10:00:00 PM UTC Should be Finished on:  Fri 30 Dec 2022 11:00:00 PM UTC
Category:  None Priority:  5 - Normal
Status:  In Progress Privacy:  Public
Assigned to:  philipnienhuis Originator Name:  Philip Nienhuis
Open/Closed:  Open Fixed Release:  None
Planned Release:  None

Jump to the original submission

Wed 29 Apr 2020 07:09:18 AM UTC, comment #9: 

io-2.6.0 and 2.6.1 (bug-fix) have been released and received some testing. I suppose it'll get much more "testing" once a new Octave-6.1.0 mxe-octave installer is released.

There are always more bugs to fix and improvements to make but I think the basic work on
the io package side has been done now.

Some notes about data representation and I/O:

As to date and time formats, that's one of the very difficult things to get together in spreadsheet I/O. The challenge is that:

  • each spreadsheet format (.xls, .xlsx, .ods, .whatever) has its own internal date, time and possibly datetime representation. Some formats even don't really have it (e.g., .xls) and the date/time datatype has to be inferred from formatting;
  • each spreadsheet format (.xls, .xlsx, .ods, .whatever) may have several "external" date representations dictated by formatting. Reading them requires some flexibility so to say;
  • each spreadsheet format (.xls, .xlsx, .ods, .whatever) has its own epoch;
  • some spreadsheet formats have additional intermingled bugs w.r.t. epoch, leap years, DST and maybe several others;


All of this is the reason that in the io package I didn't really bother to chase maximum Matlab compatibility as regards date and time. I tried to return dates as much as possible in the form of Matlab-compatible datenums (epoch 0000-00-0 or is it Dec 31, -1?).

While there, additional issues may be that the spreadsheet formats out there support an interesting mix of supported data types. E.g.,
- Excel .xls supports just double, boolean and text. Only when using jJava and/or ActiveX/COM there's a way to also "support" date and time;
- .xlsx does a little better and does support datetime (IIRC even without epoch, it's just text);
- .ods internally has boolean but AFAIK in LibreOffice there's no API to distinguish it from numerical 0 and 1. Some Java-based libraries (ODF Toolkit and jOpenDocument) do support it, luckily;
- .ods does supports datetime, date, time, currency, percentages, double, boolean, string;
- not all spreadsheet formats "cache" results of spreadsheet formulas (e.g., Gnumeric doesn't), but more tricky is that many others do but only if the spreadsheet was "calculated" first. Yet even more tricky is that even if there are cached formula results they may be outdated or even stale.

This isn't all meant as a rant (admittedly it looks like it, sorry) but merely to motivate this opinion:
The message is that it may be wise to concentrate on just one, maybe two, spreadsheet formats at first.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 31 Jan 2020 04:56:51 PM UTC, comment #8: 

Fine by me. I'm just going to have Tablicious take a dependency on the io package, and thus io's requirements for Excel interop. That'll mean Tablicious has to stay as a package instead of being merged into core Octave (at least the I/O parts) but I think that's fine; table I/O is important enough that it's worth taking the dependency.

Andrew Janke <apjanke>
Fri 31 Jan 2020 04:53:49 PM UTC, comment #7: 

I suppose there's little harm in already starting with the current io repo. Any mods can easily be merged.

FTR you mentioned Apache POI; TTBOMK the core devs, and perhaps several contributors, are reluctant to introduce dependencies on Java into core Octave. So such dependencies will have to live in the io package for the time being.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 31 Jan 2020 04:22:50 PM UTC, comment #6: 

Great. I've made a new start on Table I/O here: https://github.com/apjanke/octave-tablicious/commits/table-io. I'll pick up your 2.6.0 as soon as it's out and target that.

Andrew Janke <apjanke>
Fri 31 Jan 2020 04:13:29 PM UTC, comment #5: 

A few minutes ago I just pushed the fixes to clean up the io package code redundancy w.r.t. ods and xls spreadsheet functions.
An io-2.6.0 release should be imminent.
I hope we now can have a better start at looking how to integrate Table I/O with it (or vice versa).


Philip Nienhuis <philipnienhuis>
Group Member
Sun 19 Jan 2020 12:38:19 PM UTC, comment #4: 

During last Xmas holiday I've cleaned up & simplified the spreadsheet I/O in the io package. After a bit more testing I'll push it and upload io-2.6.0 to the release tracker.
That should give a more useful base for Table I/O than current io package releases (up until 2.4.13).

Philip Nienhuis <philipnienhuis>
Group Member
Sun 20 Oct 2019 01:39:53 PM UTC, comment #3: 

Sounds like a good initial plan.

1. I think we should expose all the cell data types, not just the ones we care about right now. That way a client can decide whether they're important and what, if anything, to do about them.

2. & 3. Yep.

4. Instead of using the options struct, maybe we could add an additional input and output argument to xls2oct()/ods2oct()/etc. Make the signature like this:


[ rawarr, xls, rstatus, extra_arrs ] = xls2oct (xls, wsh, range, options, extra_data_items)


Where extra_data_items is a cellstr vector naming what additional data items should be pulled out (e.g. "datatype" for the raw cell datatype name, "format" for the display format that's needed for detecting Excel dates, "cellstyle" for font/bold/italic/border information, "formula" to unconditionally grab underlying formulas, maybe "type" for a higher-level cell type detected by io's heuristics), and extra_arrs is a cell vector containing the corresponding arrays of retrieved data. extra_arrs is the same size as extra_data_items, and extra_arrs{i} contains the data item identified by extra_data_items{i}.

Selecting what items of data are to be returned is maybe important/common enough to justify its own input argument, instead of being stuck in one of the options. On the other hand, the options form might be easier to read, since it wouldn't be just a positional parameter in a getting-long function signature. So maybe it should be a new argout, but a new "extra_items" option instead of a brand new argin.

BTW 2. If we get this working for table in Tablicious, we can totally port it over to the dataframe package. They're similar data structures and will have the same needs from the io package, except that dataframe doesn't know about @datetime or @categorical.

BTW 3. I didn't know about importdata(). I'll have a look at it.

I'm pretty familiar with Apache POI, and could handle updating the POI interface to do this, once we decide on a signature and how we're going to represent cell types etc at the M-code level.

Andrew Janke <apjanke>
Sun 20 Oct 2019 12:46:09 PM UTC, comment #2: 

Just a first throw at what is needed for the io package to support Tablicious.

I found it the easiest to first try to read / get info from file. Usually that gives enough info & self-confidence to attempt writing to file in a next stage.

1. Define cell types we want to know. The regular numerical ones (double, ?integer? (probably not supported), boolean), text, various date/time types, ...?

2. For each file format (provisionally .xls, .xlsx, .ods, maybe a few more?) find out if these cell types are supported at all.

3. For each interface, find out how the cell types we want to know about can be extracted.

4. Think about how to convey the cell type info to Tablicious.


As to 3., AFAIK for most if not all interfaces, large parts of the req'd infrastructure are already in place, as each cell type usually requires its own way to be copy data to the output cell array. Just copying the type info to some output array, rather than discard it as happens now, will do.
But for each interface, additional code may be needed extract more detailed info as regards cell types. That info could be contained (hidden) in the spreadsheet file or we might need some heuristics.

As to 4., I think this might be the easiest part. There's already an options struct in the spreadsheet I/O functions that can have some extra fields.


BTW 1. In the io package there's also csv2cell() and cell2csv() for reading /writing from/to mixed-type .csv files. That could be a start for Table I/O..

And then we have examples:

BTW 2. Perhaps the dataframe package has useful info. An old wish from me would is to support dataframes in the I/O package. Table I/O comes close.

BTW 3. Octave already has importdata() that more or less encapsulates file type info for data I/O.

Philip Nienhuis <philipnienhuis>
Group Member
Sat 19 Oct 2019 06:27:08 PM UTC, comment #1: 

The io package already has the basics that Tablicious needs. The main thing we need to add is support for exposing cell types and formats so that the Tablicious client can detect (or guess at) which cells contain date values, and should be converted to @datetime objects.

Andrew Janke <apjanke>
Sat 19 Oct 2019 12:42:32 PM UTC, original submission:  

Aim:
Implement Table I/O for Tablicious project, using a.o., OF io package spreadsheet I/O.

Philip Nienhuis <philipnienhuis>
Group Member

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

No files currently attached

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by apjanke (Posted a comment)
  • -email is unavailable- added by philipnienhuis (Submitted the item)
  •  

    There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

     

    No changes have been made to this item

    Back to the top

    Powered by Savane 3.13-f8d8.
    Corresponding source code