export org table to other formats (gnumeric or scalc or xlsx)

export org table to other formats (gnumeric or scalc or xlsx)

[Uwe Brauer]

Hi

A couple of days ago I asked about importing excel formula into org
tables, and they only ways seems to do it manually.

I just realised that I need it also the other way around, exporting  to
some spreadsheet format, like gnumerica or scalc or xlsx.

But that look equally difficult, isn't. 

Some hints would be welcome.

Regards

Uwe Brauer 

Re: export org table to other formats (gnumeric or scalc or xlsx)

[Tim Cross]

Uwe Brauer <oub@mat.ucm.es> writes:

> Hi
>
> A couple of days ago I asked about importing excel formula into org
> tables, and they only ways seems to do it manually.
>
> I just realised that I need it also the other way around, exporting  to
> some spreadsheet format, like gnumerica or scalc or xlsx.
>
> But that look equally difficult, isn't. 
>
> Some hints would be welcome.
>

The big problem here is that there is no single format understood by all
these different programs which you can use. While CSV works OK for data,
it does not support formulas and other meta data. In particular,
translating formulas is a real challenge.

I went down this rabbit hole some years back i.e. having a workflow
which allowed me to interact with others who used Excel and allowing me
to use org mode.

It took hours and hours of additional work and never worked reliably
because

- I never found a way of 'exporting' to a format which could be imported
  by Excel and included formulas

- None of the Excel export formats support full export of Excel -
  especially at the meta data level i.e. Visual Basic macros and other
  'objects'. Workbooks were a real pain.

- Constantly having to do 'hand tweaking' to fix formulas and other
  'meta' information (both directions). When exporting to Excel, I would
  have to open the spreadsheet in another program to 'clean it up'
  before sending it to colleagues.  

 - Too many different Excel versions. This issue may not be as bad now,
   but back then, there were multiple xlsx versions and you would get
   different results depending on the version.

In the end, I gave up and either just used LibreOffice (Linux) or an OSX
program (I think it was called numerics, but too long ago to recall
accurately - it was not an Apple program). In the end, my decision was
to only use org if either I only needed the data (possibly adding
formulas manually), would not need to export back to Excel (no
collaboration) and only need export to org once. Otherwise, use
LibreOffice or another program able to understand xlsx natively.

One thing which we did use for a time was to connect the excel
spreadsheets with a database. The excel spreadsheet would pull the data
from the database and apply formulas/macros to that data. I was then
able to pull data from the database into org and then export it back
into the database. It took a bit to setup - used Visual Basic to manage
data import/export into database and needed an Excel based UI to do some
CRUD operations and then some scripts on the Linux side to
extract/update the data from org. This sort of worked, but had issues
with synchronisation of data. It really needed a much more sophisticated
database API to make it work well which could handle versioning or
resolve data conflicts and dependencies etc.

To some extent, I guess Excel is a good example of what RMS was
concerned about and the problem with closed proprietary standards. While
someone can reverse engineer the formats and spend the time to develop
converters, they remain at the mercy of MS who can simply change the
formatting at their whim.  Most efforts I've seen seem to run out of
puff because of the efforts needed to maintain things. 

Of course, life would probably be better if so many project managers
stopped using Excel for EVERYTHING! I've noticed over the last 10+ years
a growing use of Excel in the 'enterprise' as the default 'tool' to
collect and manage information. To many, it seems like a simple
solution, but in reality, you end up with all sorts of valuable
information floating around in multiple spreadsheets and spend far too
much time entering/updating data using a crappy interface and tweaking
format commands. I've seen more than one project almost hit the rocks
because someone was working off an old excel spreadsheet with the wrong
data. If you raise this concern, the likely outcome is a decision to use
MS Project, then your really stuffed!



-- 
Tim Cross

Re: [External] : Re: export org table to other formats (gnumeric or scalc or xlsx)

[Daniel Ortmann]

I highly recommend a recent LibreOffice.  Nearly everything I do is 
through LibreOffice and CSV files.  MS Excel has problems when using 
inter-field-separators such as semicolons.

When I receive Excel (or other) spreadsheets from people, I must first 
convert them into CSV files to clear out the crazy manual formatting in 
order to process them content.

Often a flow such as this helps:
*.xlsx => *.csv => Emacs (to cleanup text, newlines, whitespace) =>
LibreOffice (to sort, reorder, analyze with better use of graphical 
realestate) =>
*.org tables with Emacs macros to process further =>
*.csv => LibreOffice to generate fresh *.xlsx

Sometimes cleanup with *nix 'cut' and 'paste' tools helps greatly:
- *.csv => cut -d\; -f1-3 >file1
- *.csv => cut -d\; -f4- >file2
- Edit content or add a new set of data
- paste -d';' file1 file2
etc.

Very powerful.

On 7/4/21 5:00 PM, Tim Cross wrote:
> Uwe Brauer <oub@mat.ucm.es> writes:
>
>> Hi
>>
>> A couple of days ago I asked about importing excel formula into org
>> tables, and they only ways seems to do it manually.
>>
>> I just realised that I need it also the other way around, exporting  to
>> some spreadsheet format, like gnumerica or scalc or xlsx.
>>
>> But that look equally difficult, isn't.
>>
>> Some hints would be welcome.
>>
> The big problem here is that there is no single format understood by all
> these different programs which you can use. While CSV works OK for data,
> it does not support formulas and other meta data. In particular,
> translating formulas is a real challenge.
>
> I went down this rabbit hole some years back i.e. having a workflow
> which allowed me to interact with others who used Excel and allowing me
> to use org mode.
>
> It took hours and hours of additional work and never worked reliably
> because
>
> - I never found a way of 'exporting' to a format which could be imported
>    by Excel and included formulas
>
> - None of the Excel export formats support full export of Excel -
>    especially at the meta data level i.e. Visual Basic macros and other
>    'objects'. Workbooks were a real pain.
>
> - Constantly having to do 'hand tweaking' to fix formulas and other
>    'meta' information (both directions). When exporting to Excel, I would
>    have to open the spreadsheet in another program to 'clean it up'
>    before sending it to colleagues.
>
>   - Too many different Excel versions. This issue may not be as bad now,
>     but back then, there were multiple xlsx versions and you would get
>     different results depending on the version.
>
> In the end, I gave up and either just used LibreOffice (Linux) or an OSX
> program (I think it was called numerics, but too long ago to recall
> accurately - it was not an Apple program). In the end, my decision was
> to only use org if either I only needed the data (possibly adding
> formulas manually), would not need to export back to Excel (no
> collaboration) and only need export to org once. Otherwise, use
> LibreOffice or another program able to understand xlsx natively.
>
> One thing which we did use for a time was to connect the excel
> spreadsheets with a database. The excel spreadsheet would pull the data
> from the database and apply formulas/macros to that data. I was then
> able to pull data from the database into org and then export it back
> into the database. It took a bit to setup - used Visual Basic to manage
> data import/export into database and needed an Excel based UI to do some
> CRUD operations and then some scripts on the Linux side to
> extract/update the data from org. This sort of worked, but had issues
> with synchronisation of data. It really needed a much more sophisticated
> database API to make it work well which could handle versioning or
> resolve data conflicts and dependencies etc.
>
> To some extent, I guess Excel is a good example of what RMS was
> concerned about and the problem with closed proprietary standards. While
> someone can reverse engineer the formats and spend the time to develop
> converters, they remain at the mercy of MS who can simply change the
> formatting at their whim.  Most efforts I've seen seem to run out of
> puff because of the efforts needed to maintain things.
>
> Of course, life would probably be better if so many project managers
> stopped using Excel for EVERYTHING! I've noticed over the last 10+ years
> a growing use of Excel in the 'enterprise' as the default 'tool' to
> collect and manage information. To many, it seems like a simple
> solution, but in reality, you end up with all sorts of valuable
> information floating around in multiple spreadsheets and spend far too
> much time entering/updating data using a crappy interface and tweaking
> format commands. I've seen more than one project almost hit the rocks
> because someone was working off an old excel spreadsheet with the wrong
> data. If you raise this concern, the likely outcome is a decision to use
> MS Project, then your really stuffed!
>
>
>

Re: export org table to other formats (gnumeric or scalc or xlsx)

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 2448 bytes --]

>>> "TC" == Tim Cross <theophilusx@gmail.com> writes:

> Uwe Brauer <oub@mat.ucm.es> writes:

>> Hi
>> 
>> A couple of days ago I asked about importing excel formula into org
>> tables, and they only ways seems to do it manually.
>> 
>> I just realised that I need it also the other way around, exporting  to
>> some spreadsheet format, like gnumerica or scalc or xlsx.
>> 
>> But that look equally difficult, isn't. 
>> 
>> Some hints would be welcome.
>> 

> The big problem here is that there is no single format understood by all
> these different programs which you can use. While CSV works OK for data,
> it does not support formulas and other meta data. In particular,
> translating formulas is a real challenge.

> I went down this rabbit hole some years back i.e. having a workflow
> which allowed me to interact with others who used Excel and allowing me
> to use org mode.

> It took hours and hours of additional work and never worked reliably
> because

> - I never found a way of 'exporting' to a format which could be imported
>   by Excel and included formulas

> - None of the Excel export formats support full export of Excel -
>   especially at the meta data level i.e. Visual Basic macros and other
>   'objects'. Workbooks were a real pain.

> - Constantly having to do 'hand tweaking' to fix formulas and other
>   'meta' information (both directions). When exporting to Excel, I would
>   have to open the spreadsheet in another program to 'clean it up'
>   before sending it to colleagues.  

>  - Too many different Excel versions. This issue may not be as bad now,
>    but back then, there were multiple xlsx versions and you would get
>    different results depending on the version.

Thanks very much for this detailed answer. Actually only two solutions
crossed my mind, one is the database approach you mentioned. The other,
though much more restricted to relatively simple formulas, 
would require a more human readable spreadsheet formula, like SKL,
or stencila https://stenci.la/blog/humane-sheets/

So the idea would be to export/import from org to SKL or stencila and then,
hopefully let, excel or scalc, do the rest.

By the way, although scalc is open/free software its native format is
also somehow difficult to read and I presume, your description would also
apply to scalc's format as well. The same might be true for gnumerics...

Regards

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [External] : Re: export org table to other formats (gnumeric or scalc or xlsx)

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 497 bytes --]

>>> "DO" == Daniel Ortmann <daniel.ortmann@oracle.com> writes:

> I highly recommend a recent LibreOffice.  Nearly everything I do is
> through LibreOffice and CSV files.  MS Excel has problems when using
> inter-field-separators such as semicolons.

Well I do that myself basically, but my main problem is now: *formulas*, I
have to collaborate with colleagues using excel and I have to work with
their formulas in a way or another. That is why a pure CSV approach is
insufficient.


[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: export org table to other formats (gnumeric or scalc or xlsx)

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1143 bytes --]


> Uwe Brauer <oub@mat.ucm.es> writes:


> The big problem here is that there is no single format understood by all
> these different programs which you can use. While CSV works OK for data,
> it does not support formulas and other meta data. In particular,
> translating formulas is a real challenge.

> I went down this rabbit hole some years back i.e. having a workflow
> which allowed me to interact with others who used Excel and allowing me
> to use org mode.

> It took hours and hours of additional work and never worked reliably
> because

> - I never found a way of 'exporting' to a format which could be imported
>   by Excel and included formulas

> - None of the Excel export formats support full export of Excel -
>   especially at the meta data level i.e. Visual Basic macros and other
>   'objects'. Workbooks were a real pain.

So if I understand you correctly you exported/imported formulas
*manually*.

One of the problems I face is the fact that org-table formulas are
column based and show as such, while excel and friends only reveal the
formula for a cell and so one is forced to take care of this difference,
manually.

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [External] : Re: export org table to other formats (gnumeric or scalc or xlsx)

[Eric S Fraga]

On Monday,  5 Jul 2021 at 16:48, Uwe Brauer wrote:
> Well I do that myself basically, but my main problem is now: *formulas*, I
> have to collaborate with colleagues using excel and I have to work with
> their formulas in a way or another. 

Tell them to use R... ;-)

But I feel your pain.  I have to collaborate with people using Excel &
co. and it is a nightmare.

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-577-gf76d4d
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: export org table to other formats (gnumeric or scalc or xlsx)

[Tim Cross]

Uwe Brauer <oub@mat.ucm.es> writes:

> [[S/MIME Signed Part:Undecided]]
>
>> Uwe Brauer <oub@mat.ucm.es> writes:
>
>
>> The big problem here is that there is no single format understood by all
>> these different programs which you can use. While CSV works OK for data,
>> it does not support formulas and other meta data. In particular,
>> translating formulas is a real challenge.
>
>> I went down this rabbit hole some years back i.e. having a workflow
>> which allowed me to interact with others who used Excel and allowing me
>> to use org mode.
>
>> It took hours and hours of additional work and never worked reliably
>> because
>
>> - I never found a way of 'exporting' to a format which could be imported
>>   by Excel and included formulas
>
>> - None of the Excel export formats support full export of Excel -
>>   especially at the meta data level i.e. Visual Basic macros and other
>>   'objects'. Workbooks were a real pain.
>
> So if I understand you correctly you exported/imported formulas
> *manually*.

Yes, that was the big stumbling block. (plus anything which was based on
Visual Basic extensions is not exported by any of the Excel export
formats).

When collaborating with others, this becomes a real issue because there
is no easy way to detect a formula has been updated/changed/added. I
ended up spending more time working on improt/export than actually
working on the data in the spreadsheets themselves. 

>
> One of the problems I face is the fact that org-table formulas are
> column based and show as such, while excel and friends only reveal the
> formula for a cell and so one is forced to take care of this difference,
> manually.

Exactly and there is way too much scope for human error!


-- 
Tim Cross

Re: [External] : Re: export org table to other formats (gnumeric or scalc or xlsx)

[Greg Minshall]

Eric,

> But I feel your pain.  I have to collaborate with people using Excel &
> co. and it is a nightmare.

to be fair, though (though, who says you have to?), imagine some poor
excel user receiving your .org file and trying to decode
org/calc-inspired formulae!  :)

cheers, Greg

[R example for org-table with ifs] (was: [External] : Re: export org table to other formats (gnumeric or scalc or xlsx))

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1029 bytes --]

>>> "ESF" == Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> On Monday,  5 Jul 2021 at 16:48, Uwe Brauer wrote:
>> Well I do that myself basically, but my main problem is now: *formulas*, I
>> have to collaborate with colleagues using excel and I have to work with
>> their formulas in a way or another. 

> Tell them to use R... ;-)

Out of curiosity do you have an example of using R to generate stuff
like this.


#+begin_src elisp
| / | <>      |    <> |     <> |      <> |       <> | <>     | <>      |
|   |         | DMI G | DMNI H | ExNDM I | ExNDNM J | Result | Weight2 |
|   | Weight: |     1 |    0.2 |       1 |      0.1 |        | 0.1     |
|---+---------+-------+--------+---------+----------+--------+---------|
|   | User1   |     0 |      0 |      11 |        0 | 10.1   |         |
|---+---------+-------+--------+---------+----------+--------+---------|
#+TBLFM: $7=if($3>10,($3-10)*@3$8,0)+ min(10,$3)*@3$3+ min(10,$4)*@3$4 + if($5>10,($5-10)*@3$8,0)+min(10,$5)*@3$5 +@3$6*$6;f1::
#+end_src

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [R example for org-table with ifs]

[Eric S Fraga]

On Wednesday,  7 Jul 2021 at 09:01, Uwe Brauer wrote:
> Out of curiosity do you have an example of using R to generate stuff
> like this.

I don't; sorry.  I don't use R myself directly although my PhD students
do.  Others on this list have quite a bit of experience with R so maybe
somebody else can chime in.

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.6-577-gf76d4d
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: [R example for org-table with ifs] (was: [External] : Re: export org table to other formats (gnumeric or scalc or xlsx))

[Greg Minshall]

Uwe,

well, *i* no longer remember how to read calc-like expressions.  and,
i'm a notoriously poor R coders.  assuredly the following is not doing
what you want, but possibly you'll get the idea.  (if 102.01 is, indeed,
the correct answer, feel free to buy me a hot fudge sundae some day. :)

cheers, Greg

#+name: thing
| / | <>      |    <> |     <> |      <> |       <> | <>     | <>      |
|   |         | DMI G | DMNI H | ExNDM I | ExNDNM J | Result | Weight2 |
|   | Weight: |     1 |    0.2 |       1 |      0.1 |        | 0.1     |
|---+---------+-------+--------+---------+----------+--------+---------|
|   | User1   |     0 |      0 |      11 |        0 | 10.1   |         |
|---+---------+-------+--------+---------+----------+--------+---------|
#+TBLFM: $7=if($3>10,($3-10)*@3$8,0)+ min(10,$3)*@3$3+ min(10,$4)*@3$4 + if($5>10,($5-10)*@3$8,0)+min(10,$5)*@3$5 +@3$6*$6;f1::

- does "@3$3" mean the third column in the third row?
  - is that the "DMNI H" column?
  - is that the "User1" row?

i replace "@3" with the last row of the input table.

#+begin_src R :var some=thing :session R :colnames yes
  ## in imported colnames, spaces are replaced with periods
  some[,"Result"] <- ifelse(some[,"DMNI.H"] > 10, (some[, "DMNI.H"] - 10.0) *
                                                 (some[nrow(some), "Weight2"]),
                            0.0) +
    (min(10, some[, "DMNI.H"]) * some[nrow(some), "ExNDM.I"]) +
    (ifelse(some[, "ExNDNM.J"] > 10, some[, "ExNDNM.J"] - 10 * some[nrow(some), "DMNI.H"], 0)) +
    (min(10, some[, "ExNDNM.J"]) * some[nrow(some), "ExNDNM.J"]) +
    (some[nrow(some), "Result"] * some[, "Result"])
#+end_src

#+RESULTS:
|      x |
|--------|
|        |
| 102.01 |

Re: [R example for org-table with ifs]

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 2037 bytes --]

>>> "GM" == Greg Minshall <minshall@umich.edu> writes:

Greg


> Uwe,
> well, *i* no longer remember how to read calc-like expressions.  and,
> i'm a notoriously poor R coders.  assuredly the following is not doing
> what you want, but possibly you'll get the idea.  (if 102.01 is, indeed,
> the correct answer, feel free to buy me a hot fudge sundae some day. :)


Thanks but I have to disappoint you the correct result should be 10.1

> cheers, Greg

> #+name: thing
> | / | <>      |    <> |     <> |      <> |       <> | <>     | <>      |

> |   |         | DMI G | DMNI H | ExNDM I | ExNDNM J | Result | Weight2 |
> |   | Weight: |     1 |    0.2 |       1 |      0.1 |        | 0.1     |
> |---+---------+-------+--------+---------+----------+--------+---------|
> |   | User1   |     0 |      0 |      11 |        0 | 10.1   |         |
> |---+---------+-------+--------+---------+----------+--------+---------|

> #+TBLFM: $7=if($3>10,($3-10)*@3$8,0)+ min(10,$3)*@3$3+ min(10,$4)*@3$4 + if($5>10,($5-10)*@3$8,0)+min(10,$5)*@3$5 +@3$6*$6;f1::

> - does "@3$3" mean the third column in the third row? 
yes
>   - is that the "DMNI H" column? That is 0.2
>   - is that the "User1" row? 

No, it is the weight row

> i replace "@3" with the last row of the input table.

> #+begin_src R :var some=thing :session R :colnames yes
>   ## in imported colnames, spaces are replaced with periods
>   some[,"Result"] <- ifelse(some[,"DMNI.H"] > 10, (some[, "DMNI.H"] - 10.0) *
>                                                  (some[nrow(some), "Weight2"]),
>                             0.0) +
>     (min(10, some[, "DMNI.H"]) * some[nrow(some), "ExNDM.I"]) +
>     (ifelse(some[, "ExNDNM.J"] > 10, some[, "ExNDNM.J"] - 10 * some[nrow(some), "DMNI.H"], 0)) +
>     (min(10, some[, "ExNDNM.J"]) * some[nrow(some), "ExNDNM.J"]) +
>     (some[nrow(some), "Result"] * some[, "Result"])
> #+end_src

Thanks I will play around a bit, but for the moment I think I have to
stick with calc

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [R example for org-table with ifs]

[Greg Minshall]

Uwe,

> Thanks but I have to disappoint you the correct result should be 10.1

ah, well.  :)

i realized you probably wanted the table back.  below it is, with your
explanation of "@3$3".

> Thanks I will play around a bit, but for the moment I think I have to
> stick with calc

well, takes all kinds.  :)

cheers, Gerg

----

#+begin_src R :var some=thing :session R :colnames yes
  ## in imported colnames, spaces are replaced with periods
  some[, "Result"] <-
    ifelse(some[, "DMNI.H"] > 10, (some[, "DMNI.H"] - 10.0) *
                                 (some[X == "Weight:", "Weight2"]),
           0.0) +
    (min(10, some[, "DMNI.H"]) * some[X == "Weight:", "ExNDM.I"]) +
    (ifelse(some[, "ExNDNM.J"] > 10, some[, "ExNDNM.J"] - 10 *
                                     some[X == "Weight:", "DMNI.H"], 0)) +
    (min(10, some[, "ExNDNM.J"]) *
     some[X == "Weight:", "ExNDNM.J"]) +
    (some[X == "Weight:", "Result"] *
     some[, "Result"])

  some
#+end_src

#+RESULTS:
| X       | DMI.G | DMNI.H | ExNDM.I | ExNDNM.J | Result | Weight2 |
|---------+-------+--------+---------+----------+--------+---------|
| Weight: |     1 |    0.2 |       1 |      0.1 |        |     0.1 |
| User1   |     0 |      0 |      11 |        0 |   10.1 |         |