Use babel to import table data from OpenDocument spreadsheets?

Use babel to import table data from OpenDocument spreadsheets?

[James Harkins]

Just wondering if anyone out there has some magic code snippets that will automatically bring in data from a LibreOffice spreadsheet during HTML export.

I think I could save, by hand, the required worksheets out of the ods file into separate CSVs, and then write an emacs-lisp Babel block to import the table from file -- presumably this would run the lisp function at export time.

But it would be so much slicker if something could go into the ods file directly and return the results in org table format. If somebody has already done something like this, it would save me quite a bit of time.

If not, I would have to weigh my available time (not much), the time it would take to implement (maybe not extremely difficult, but not trivial either, especially if it involves digging through LibreOffice library documentation) and the weekly time I would save by just hitting C-e h h, instead of doing another manual process before C-e h h.

(Or possibly an alternative would be to write a macro in LibreOffice to write the CSV files and run that from the commandline as part of the org export process.)

Thanks -- this is just, no harm in asking. Otherwise I'll deal with it some other way.

hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[Jambunathan K]

M-x org-odt-convert RET

or

(org-odt-convert ......)


James Harkins <jamshark70@gmail.com> writes:

> Just wondering if anyone out there has some magic code snippets that
> will automatically bring in data from a LibreOffice spreadsheet during
> HTML export.
>
> I think I could save, by hand, the required worksheets out of the ods
> file into separate CSVs, and then write an emacs-lisp Babel block to
> import the table from file -- presumably this would run the lisp
> function at export time.
>
> But it would be so much slicker if something could go into the ods
> file directly and return the results in org table format. If somebody
> has already done something like this, it would save me quite a bit of
> time.
>
> If not, I would have to weigh my available time (not much), the time
> it would take to implement (maybe not extremely difficult, but not
> trivial either, especially if it involves digging through LibreOffice
> library documentation) and the weekly time I would save by just
> hitting C-e h h, instead of doing another manual process before C-e h
> h.
>
> (Or possibly an alternative would be to write a macro in LibreOffice
> to write the CSV files and run that from the commandline as part of
> the org export process.)
>
> Thanks -- this is just, no harm in asking. Otherwise I'll deal with it some other way.
>
> hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[James Harkins]

At Tue, 02 Jul 2013 19:49:43 +0530,
Jambunathan K wrote:
> M-x org-odt-convert RET
> 
> or
> 
> (org-odt-convert ......)

Is this valid for .ods, or only .odt? The file is from LibreOffice Calc, not Writer.

I made a quick test and got only "failed." From the Messages, I can see it's using a headless soffice, so maybe it needs some other command line parameters. That will have to wait for another day... busy with other things now.

Debug (ox-odt): Searching for OpenDocument styles files...
Debug (ox-odt): Trying /usr/share/emacs/etc/org/styles/...
Debug (ox-odt): Using styles under /usr/share/emacs/etc/org/styles/
Debug (ox-odt): Searching for OpenDocument schema files...
Debug (ox-odt): Trying /usr/share/emacs/etc/org/schema/...
Debug (ox-odt): Using schema files under /usr/share/emacs/etc/org/schema/
Making completion list...
Executing soffice --headless --convert-to csv --outdir /home/dlm/Documents/xinghai/12-13-spring/ /home/dlm/Documents/xinghai/12-13-spring/gradesheet.ods

Export to /home/dlm/Documents/xinghai/12-13-spring/gradesheet.csv failed

hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[Jambunathan K]

James Harkins <jamshark70@gmail.com> writes:

> At Tue, 02 Jul 2013 19:49:43 +0530,
> Jambunathan K wrote:
>> M-x org-odt-convert RET
>> 
>> or
>> 
>> (org-odt-convert ......)
>
> Is this valid for .ods, or only .odt? The file is from LibreOffice Calc, not Writer.
>
> I made a quick test and got only "failed." From the Messages, I can
> see it's using a headless soffice, so maybe it needs some other
> command line parameters. That will have to wait for another
> day... busy with other things now.
>
> Debug (ox-odt): Searching for OpenDocument styles files...
> Debug (ox-odt): Trying /usr/share/emacs/etc/org/styles/...
> Debug (ox-odt): Using styles under /usr/share/emacs/etc/org/styles/
> Debug (ox-odt): Searching for OpenDocument schema files...
> Debug (ox-odt): Trying /usr/share/emacs/etc/org/schema/...
> Debug (ox-odt): Using schema files under /usr/share/emacs/etc/org/schema/
> Making completion list...
> Executing soffice --headless --convert-to csv --outdir
> /home/dlm/Documents/xinghai/12-13-spring/
> /home/dlm/Documents/xinghai/12-13-spring/gradesheet.ods
>
> Export to /home/dlm/Documents/xinghai/12-13-spring/gradesheet.csv failed

Works here for simple files.

        M-: (org-odt-convert "file.ods" "csv")

Executing libreoffice4.0 --headless --convert-to csv --outdir /home/kjambunathan/ /home/kjambunathan/file.ods
convert /home/kjambunathan/file.ods -> /home/kjambunathan//file.csv using Text - txt - csv (StarCalc)

Exported to /home/kjambunathan/file.csv
"/home/kjambunathan/file.csv"

AFAIK, if you can save as CSV from LO UI, then it should work from the
command line.  

Start with a simple file and isolate the issue.

> hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[James Harkins]

At Tue, 02 Jul 2013 20:40:40 +0530,
Jambunathan K wrote:
> Works here for simple files.
> 
>         M-: (org-odt-convert "file.ods" "csv")
> 
> Executing libreoffice4.0 --headless --convert-to csv --outdir /home/kjambunathan/ /home/kjambunathan/file.ods
> convert /home/kjambunathan/file.ods -> /home/kjambunathan//file.csv using Text - txt - csv (StarCalc)
> 
> Exported to /home/kjambunathan/file.csv
> "/home/kjambunathan/file.csv"
> 
> AFAIK, if you can save as CSV from LO UI, then it should work from the
> command line.

Famous last words: "It should work" :)

Okay, I've just tested with two very simple files at the command line. LibreOffice fails to write any output file at all. Apparently this is a common problem. Bing finds [1], but ask.libreoffice.org is down.

Here is the transcript for a simple Writer file containing only the word "test":

$ soffice --headless --convert-to html --outdir /home/dlm/Documents/orgtests/ /home/dlm/Documents/orgtests/html-table-test-word.odt 

$ ls ht*
html-table.html   html-table.ods  html-table.org~
html-table.html~  html-table.org  html-table-test-word.odt

Anyway, I'm not convinced that org-odt-convert would meet my needs anyway. It will have to be a multi-worksheet file, and I'll have to extract just one or two of the worksheets. The macro might be the way to go.

hjh

[1] http://ask.libreoffice.org/en/question/2641/convert-to-command-line-parameter/

Re: Use babel to import table data from OpenDocument spreadsheets?

[Jambunathan K]

James Harkins <jamshark70@gmail.com> writes:

> Famous last words: "It should work" :)

Sure.  As magical as running and talking at the same time.

Re: Use babel to import table data from OpenDocument spreadsheets?

[James Harkins]

At Wed, 03 Jul 2013 10:35:51 +0800,
James Harkins wrote:
> Anyway, I'm not convinced that org-odt-convert would meet my needs anyway. It will have to be a multi-worksheet file, and I'll have to extract just one or two of the worksheets. The macro might be the way to go.

I've made a bit more progress with this. I found a utility, unoconv[1], which I can persuade to produce a tab-separated export of the first worksheet of an ODS document. That much should work for my needs -- I can reserve the first worksheet for the data to be published, and put all the calculations in other sheets.

I'm not sure how to import this as an org table. I found org-table-import, but it seems that this must insert a table into a buffer and then convert to org-table format in the buffer. I don't know how to integrate that with babel. If I set ":exports results," should I assume then that the code block should return a string consisting of the org-formatted table?

- Or, do I have to say ":exports none" and do some save-excursion magic with moving the point to the right place before calling org-table-import? (That's probably okay for this small-scale usage, but it would be slicker to put a #+CALL in the right location.)

- Or, do I have to write my own lisp function to format the table as a string?

The goal is that I should be able to do C-c C-e h h from the org document, and babel will run a short emacs-lisp block to invoke unoconv (producing a CSV file on disk) and then insert the table under the right heading.

Thanks,
hjh

[1] http://linux.die.net/man/1/unoconv

Re: Use babel to import table data from OpenDocument spreadsheets?

[Jambunathan K]

James Harkins <jamshark70@gmail.com> writes:

> I found a utility, unoconv[1], which I can persuade to produce a
> tab-separated export of the first worksheet of an ODS document.

FYI, unoconv is an option provided by `org-odt-convert-processes'.

Re: Use babel to import table data from OpenDocument spreadsheets?

[James Harkins]

At Wed, 10 Jul 2013 09:20:25 +0530,
Jambunathan K wrote:
> James Harkins <jamshark70@gmail.com> writes:
> 
> > I found a utility, unoconv[1], which I can persuade to produce a
> > tab-separated export of the first worksheet of an ODS document.
> 
> FYI, unoconv is an option provided by `org-odt-convert-processes'.

Thanks.

Okay... I finally had some time to come back to this task.

While I do appreciate all the suggestions so far, unfortunately I have to admit that I'm still stuck. What I'd like to do now is run down what I know about it so far and put questions in the middle of that.

1. I have a LibreOffice ODS file, where the table to obtain will be in the first worksheet.

1a. I can use unoconv to get a tab-separated text table out of it, in a file on disk.

1b. I will need to invoke this synchronously in an emacs-lisp block. (shell-command ...) I guess, right?


2. I want the tab-separated text to be converted into an org-table, so that HTML export will turn it into an HTML table. This is where it becomes completely baffling to the novice.

2a. There's a function, org-table-import, which looks like it will do the job. It inserts the table contents at the point. I just can't find any examples online of the correct way to do this. I assume I will have to run an emacs-lisp source block. I had been thinking I could use ":exports results" with a #+call, supposing that I could write out the table text as a stream and those results would be put in place of the #+call in the export. But of course that doesn't make sense if org-table-import is significant for its side effect (modifying the buffer) rather than its result.

2b. So (I guess) I would have to use emacs-lisp to move the point to a specific location in the file before calling org-table-import (obviously not by character index, since I can't assume previous text will not change).

QUESTION: What is the best way to do that?

^^ This is the point where I'm stuck. I really have no idea. Say I put in an org comment "# raw data here" -- how do I get the point to go to the line after that? How do I delete the entire old table before importing the new one? Working example, perhaps?


Anyway, I'll be happy to write this up as a worg page after I get it working, as it seems to me this would be a fairly common need but the information is not easy to find.

Thanks,
hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[Jambunathan K]

1. Give me a sample worksheet.

2. Give me the specific unoconv command that you used for converting the
   worksheet.

If you do (1) and (2), I will post a recipe.




James Harkins <jamshark70@gmail.com> writes:

> At Wed, 10 Jul 2013 09:20:25 +0530,
> Jambunathan K wrote:
>> James Harkins <jamshark70@gmail.com> writes:
>> 
>> > I found a utility, unoconv[1], which I can persuade to produce a
>> > tab-separated export of the first worksheet of an ODS document.
>> 
>> FYI, unoconv is an option provided by `org-odt-convert-processes'.
>
> Thanks.
>
> Okay... I finally had some time to come back to this task.
>
> While I do appreciate all the suggestions so far, unfortunately I have
> to admit that I'm still stuck. What I'd like to do now is run down
> what I know about it so far and put questions in the middle of that.
>
> 1. I have a LibreOffice ODS file, where the table to obtain will be in the first worksheet.
>
> 1a. I can use unoconv to get a tab-separated text table out of it, in a file on disk.
>
> 1b. I will need to invoke this synchronously in an emacs-lisp
> block. (shell-command ...) I guess, right?
>
>
> 2. I want the tab-separated text to be converted into an org-table, so
> that HTML export will turn it into an HTML table. This is where it
> becomes completely baffling to the novice.
>
> 2a. There's a function, org-table-import, which looks like it will do
> the job. It inserts the table contents at the point. I just can't find
> any examples online of the correct way to do this. I assume I will
> have to run an emacs-lisp source block. I had been thinking I could
> use ":exports results" with a #+call, supposing that I could write out
> the table text as a stream and those results would be put in place of
> the #+call in the export. But of course that doesn't make sense if
> org-table-import is significant for its side effect (modifying the
> buffer) rather than its result.
>
> 2b. So (I guess) I would have to use emacs-lisp to move the point to a
> specific location in the file before calling org-table-import
> (obviously not by character index, since I can't assume previous text
> will not change).
>
> QUESTION: What is the best way to do that?
>
> ^^ This is the point where I'm stuck. I really have no idea. Say I put
> in an org comment "# raw data here" -- how do I get the point to go to
> the line after that? How do I delete the entire old table before
> importing the new one? Working example, perhaps?
>
>
> Anyway, I'll be happy to write this up as a worg page after I get it
> working, as it seems to me this would be a fairly common need but the
> information is not easy to find.
>
> Thanks,
> hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[James Harkins]

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

At Thu, 18 Jul 2013 14:49:16 +0530,
Jambunathan K wrote:
> 1. Give me a sample worksheet.

Attached.

> 2. Give me the specific unoconv command that you used for converting the
>    worksheet.

unoconv -f csv -i 9,34,system,1,1/5/2/1/3/1/4/1 html-table.ods

Maybe the filename needs a full path, not sure. I can supply that later.

> If you do (1) and (2), I will post a recipe.

Really appreciate that!

hjh


[-- Attachment #2: html-table.org --]
[-- Type: text/plain, Size: 677 bytes --]

#+OPTIONS: html-postamble:auto html-preamble:t tex:t
#+CREATOR: <a href="http://www.gnu.org/software/emacs/">Emacs</a> 23.3.1 (<a href="http://orgmode.org">Org</a> mode 8.0.2)
#+HTML_CONTAINER: div
#+HTML_DOCTYPE: xhtml-strict
#+EXCLUDE_TAGS: noexport


* Table test

After running the script, I expect the existing table (with 1, 2, 3 in
the second column) to be replaced by the new table data (10, 20, 30).

#+name: gettable1
#+begin_src emacs-lisp :exports none
(... don't know what to write in here...)
#+end_src

#+name: table1
#+ATTR_HTML: :border 2 :rules all :frame border
| Simple | Table |
|--------+-------|
|      1 |     1 |
|      2 |     2 |
|      3 |     3 |


[-- Attachment #3: html-table.ods --]
[-- Type: application/vnd.oasis.opendocument.spreadsheet, Size: 8788 bytes --]

Re: Use babel to import table data from OpenDocument spreadsheets?

[Jambunathan K]

I don't want to venture in to Babel. 

I don't want to experiment with unoconv either.  I have a non-official
libreoffice installed.  Pulling in official unoconv will interfere with
my working installation.

For your purposes, just soffice will do.


(defun org-table-import-ods (&optional file-name)
  (interactive "fFile: ")
  (let ((csv-file (org-odt-convert file-name "csv"))
	(pos (point)))
    (save-excursion
      (insert (with-temp-buffer
		(insert-file-contents csv-file)
		(org-table-convert-region (point-min) (point-max) '(4))
		(buffer-string))))))

| Simple | Table |
|      1 |    10 |
|      2 |    20 |
|      3 |    30 |


Remember to close all libreoffice applications.  THIS IS IMPORTANT.
Otherwise `org-odt-convert' will error out.

0. Visit scratch buffer
1. Install the above snippet
2. Go to end of the scratch buffer. 
3. M-x org-table-import-ods RET

You will see something like what I see above.

James Harkins <jamshark70@gmail.com> writes:

> At Thu, 18 Jul 2013 14:49:16 +0530,
> Jambunathan K wrote:
>> 1. Give me a sample worksheet.
>
> Attached.
>
>> 2. Give me the specific unoconv command that you used for converting the
>>    worksheet.
>
> unoconv -f csv -i 9,34,system,1,1/5/2/1/3/1/4/1 html-table.ods
>
> Maybe the filename needs a full path, not sure. I can supply that later.
>
>> If you do (1) and (2), I will post a recipe.
>
> Really appreciate that!
>
> hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[Jambunathan K]

Jambunathan K <kjambunathan@gmail.com> writes:

> I don't want to venture in to Babel. 
>
> I don't want to experiment with unoconv either.  I have a non-official
> libreoffice installed.  Pulling in official unoconv will interfere with
> my working installation.
>
> For your purposes, just soffice will do.
>
>
> (defun org-table-import-ods (&optional file-name)
>   (interactive "fFile: ")
>   (let ((csv-file (org-odt-convert file-name "csv"))
> 	(pos (point)))
>     (save-excursion
>       (insert (with-temp-buffer
> 		(insert-file-contents csv-file)
> 		(org-table-convert-region (point-min) (point-max) '(4))
> 		(buffer-string))))))
>
> | Simple | Table |
> |      1 |    10 |
> |      2 |    20 |
> |      3 |    30 |
>
>
> Remember to close all libreoffice applications.  THIS IS IMPORTANT.
> Otherwise `org-odt-convert' will error out.
>
> 0. Visit scratch buffer
> 1. Install the above snippet
> 2. Go to end of the scratch buffer. 
> 3. M-x org-table-import-ods RET
>
> You will see something like what I see above.



Here is a dump from my Messages buffer.


,----
| Executing libreoffice4.0 --headless --convert-to csv --outdir ~/ ~/html-table.ods
| convert ~/html-table.ods -> ~//html-table.csv using Text - txt - csv (StarCalc)
| 
| Exported to ~/html-table.csv
| Undo!
| Executing libreoffice4.0 --headless --convert-to csv --outdir ~/ ~/html-table.ods
| convert ~/html-table.ods -> ~//html-table.csv using Text - txt - csv (StarCalc)
| 
| Exported to ~/html-table.csv
`----




>
> James Harkins <jamshark70@gmail.com> writes:
>
>> At Thu, 18 Jul 2013 14:49:16 +0530,
>> Jambunathan K wrote:
>>> 1. Give me a sample worksheet.
>>
>> Attached.
>>
>>> 2. Give me the specific unoconv command that you used for converting the
>>>    worksheet.
>>
>> unoconv -f csv -i 9,34,system,1,1/5/2/1/3/1/4/1 html-table.ods
>>
>> Maybe the filename needs a full path, not sure. I can supply that later.
>>
>>> If you do (1) and (2), I will post a recipe.
>>
>> Really appreciate that!
>>
>> hjh

Re: Use babel to import table data from OpenDocument spreadsheets?

[James Harkins]

At Fri, 19 Jul 2013 00:32:44 +0530,
Jambunathan K wrote:
> I don't want to venture in to Babel. 
> 
> I don't want to experiment with unoconv either.  I have a non-official
> libreoffice installed.  Pulling in official unoconv will interfere with
> my working installation.

Fair enough.

> For your purposes, just soffice will do.

Except that I tried using soffice --headless, but I could never get it to write any output file.

> (defun org-table-import-ods (&optional file-name)
>   (interactive "fFile: ")
>   (let ((csv-file (org-odt-convert file-name "csv"))
> 	(pos (point)))
>     (save-excursion
>       (insert (with-temp-buffer
> 		(insert-file-contents csv-file)
> 		(org-table-convert-region (point-min) (point-max) '(4))
> 		(buffer-string))))))

Wow, thanks, this is great! I never would have guessed the stuff about with-temp-buffer.

This snippet didn't handle the part about deleting the old table contents, but I was actually able to figure that out on my own (below).

And... using it in a src block as below... works! I don't see the change in table contents in the original org buffer, but the new contents definitely show up in the HTML export. That's all I need, and I'd agree that this behavior -- protecting the org file from side effects -- is safer.

Next, parameterize the file name and table name.

So this is really pretty f'''... sweet. I'm thrilled! Thanks to Jambunathan for the crucial info that got me over the part I just couldn't figure out on my own.

hjh


#+name: gettable1
#+begin_src emacs-lisp :exports results
(defun org-table-import-ods (&optional file-name)
  (interactive "fFile: ")
  (let ((csv-path (concat (file-name-sans-extension file-name) ".csv"))
	(pos (point)))
    (shell-command (concat "unoconv -f csv -i 9,34,system,1,1/5/2/1/3/1/4/1 " file-name))
    (save-excursion
      (let ((table-begin
	     (funcall (lambda ()
			(search-forward "#+name: table1")
			(search-forward-regexp "^|")
			(beginning-of-line)
			(point))))
	    (table-end
	     (funcall (lambda ()
			(search-forward "# end table")
			(beginning-of-line)
			(point)))))
	(delete-region table-begin table-end)
	(goto-char table-begin)
	(insert (with-temp-buffer
		  (insert-file-contents csv-path)
		  (org-table-convert-region (point-min) (point-max) '(16))
		  (buffer-string)))
	(goto-char table-begin)
	(org-table-insert-hline)))))

(org-table-import-ods "html-table.ods")
#+end_src

Re: Use babel to import table data from OpenDocument spreadsheets?

[Andreas Leha]

Hi James,

James Harkins <jamshark70@gmail.com> writes:

> At Wed, 03 Jul 2013 10:35:51 +0800,
> James Harkins wrote:
>> Anyway, I'm not convinced that org-odt-convert would meet my needs
>> anyway. It will have to be a multi-worksheet file, and I'll have to
>> extract just one or two of the worksheets. The macro might be the
>> way to go.
>
> I've made a bit more progress with this. I found a utility,
> unoconv[1], which I can persuade to produce a tab-separated export of
> the first worksheet of an ODS document. That much should work for my
> needs -- I can reserve the first worksheet for the data to be
> published, and put all the calculations in other sheets.
>
> I'm not sure how to import this as an org table. I found
> org-table-import, but it seems that this must insert a table into a
> buffer and then convert to org-table format in the buffer. I don't
> know how to integrate that with babel. If I set ":exports results,"
> should I assume then that the code block should return a string
> consisting of the org-formatted table?
>
> - Or, do I have to say ":exports none" and do some save-excursion
> magic with moving the point to the right place before calling
> org-table-import? (That's probably okay for this small-scale usage,
> but it would be slicker to put a #+CALL in the right location.)
>
> - Or, do I have to write my own lisp function to format the table as a string?
>
> The goal is that I should be able to do C-c C-e h h from the org
> document, and babel will run a short emacs-lisp block to invoke
> unoconv (producing a CSV file on disk) and then insert the table under
> the right heading.
>
> Thanks,
> hjh
>
> [1] http://linux.die.net/man/1/unoconv

I did not follow the thread, but reading ods files into org is easy, if
you have access to R:

--8<---------------cut here---------------start------------->8---
#+name: firstsheet
#+begin_src R :colnames yes
  library("ROpenOffice")
  read.ods("~/tmp/not.ods")[[1]]
#+end_src

#+results: firstsheet
| first col | second col | third col |
|-----------+------------+-----------|
|         8 |          4 |         5 |
|      1541 |         79 |       628 |


#+name: secondsheet
#+begin_src R :colnames yes
  library("ROpenOffice")
  read.ods("~/tmp/not.ods")[[2]]
#+end_src

#+results: secondsheet
| dtr   | egd   | pdrn |
|-------+-------+------|
| ugn   | apggh | gpd  |
| ulgnd | pugn  | pdsg |
--8<---------------cut here---------------end--------------->8---

Regards,
Andreas