7

Is it possible to convert convert an xls file into an org table? I found this article on the emacs wiki but wasn't able to get it to work.

  • @lawlist As I mention in the question I'm aware of UnXls but haven't been able to get it to work. – Brian Fitzpatrick Jun 20 '15 at 3:36
  • I think that's it -- i.e., the thread I linked and the Emacs wiki. I don't believe anything else exists. I would probably save the spreadsheet as something like a tab-delimited text file using Microsoft Excel, and then do whatever I wanted with the data afterwords. – lawlist Jun 20 '15 at 3:41
5

Save the file as a tab delimited file (using Excel or the localc command mentioned in @YoungFrog's answer). Then run org-table-import at the point where you want the table inserted.

  • I switched this to the accepted answer. All the answers work but this is the quickest. Thanks! – Brian Fitzpatrick Jun 20 '15 at 17:32
  • org-table-import is ok for simple csv files, but it will break if a cell contains a newline (which is the case for me). – YoungFrog Jun 20 '15 at 18:15
6

Since you are using Excel, you might as well be interested in other tools to work with statistical data. Here's how you could use R programming language to import an XSL file into Emacs Org mode:

* Import XLS File Using R
  Install =gdata= package (you need to only run it once unles you already
  have this package installed).  The code below is meant to get you started
  with just this file and nothing else, but generally, you'd simply run
  =install.packages("gdata")= from ESS session to install the package.
  The reason you can't do it here is that by default =install.packages= will
  try to install into location found in =.libPaths()=, which is likely to
  require super-user permissions.

  #+begin_src R :var tmpdir="/tmp"
    firstpath <- .libPaths()[1]
    .libPaths(c(firstpath, tmpdir))
    install.packages("gdata", lib = tmpdir, repos = "http://cran.rstudio.com/")
    library(gdata)
    read.xls("example.xls")
  #+end_src

  #+RESULTS:
  |         | Created with Microsoft Excel 2003 SP1 |
  |       X |                                     Y |
  | 0.42491 |                               0.15039 |
  | 0.03927 |                               0.54603 |
  |    some |                     rows were skipped |
  | 0.72372 |                               0.78759 |
  | 0.73772 |                               0.97298 |
  | 0.35374 |                               0.38789 |

  Or, open the ESS session by executing =M-x R=, then type into the
  R console (your input starts with `>' symbol, you don't need to type the
  symbol itself).  Answer the prompts by typing `y' or `n'.

  #+begin_example
  > install.packages("gdata")
  Installing package into ‘/usr/lib64/R/library’
  (as ‘lib’ is unspecified)
  Warning in install.packages("gdata") :
  'lib = "/usr/lib64/R/library"' is not writable
  Would you like to use a personal library instead?  (y/n) y
  Would you like to create a personal library
  ~/R/x86_64-redhat-linux-gnu-library/3.2
  to install packages into?  (y/n) y
  --- Please select a CRAN mirror for use in this session ---
  <install log skipped>
  ,** building package indices
  ,** installing vignettes
  ,** testing if installed package can be loaded
  ,* DONE (gdata)

  The downloaded source packages are in
  ‘/tmp/RtmpMiDPfR/downloaded_packages’
  #+end_example

  Load the XLS file and output an Org table:

  #+begin_src R
    library(gdata)
    read.xls("example.xls")
  #+end_src

  #+RESULTS:
  |         | Created with Microsoft Excel 2003 SP1 |
  |       X |                                     Y |
  | 0.42491 |                               0.15039 |
  | 0.03927 |                               0.54603 |
  |    some |                     rows were skipped |
  | 0.72372 |                               0.78759 |
  | 0.73772 |                               0.97298 |
  | 0.35374 |                               0.38789 |

This is the example XLS file I used http://berkeleycollege.edu/browser_check/samples/excel.xls

You will need to install ESS package to interact with R from Emacs, as well as R language itself. Look here: http://ess.r-project.org/Manual/ess.html#Installation for instructions (or just do M-xpackage-installRETESS). You will need to enable R in Org Babel code blocks by adding this to your Emacs init file:

(org-babel-do-load-languages
  'org-babel-load-languages '((R . t)))

In order to install R look here: http://cran.r-project.org/doc/manuals/r-release/R-admin.html#Installation, but these instructions are meant for those who want to build the language on their own. You could typically install it on Linux using your package manager, eg. apt-get install R or yum install R etc. There are also binaries for other platforms, for example: MS Windows binaries can be found here: http://cran.r-project.org/bin/windows/base/

4

Here's what I do. Not ideal, but kind of works. First, I use LibreOffice Calc to convert to CSV:

localc --convert-to csv --headless filename

Then I use pcsv.el (a CSV parser) to convert from CSV to Lisp, then insert the result as an Org mode table:

(defun yf/lisp-table-to-org-table (table &optional function)
  "Convert a lisp table to `org-mode' syntax, applying FUNCTION to each of its elements.
The elements should not have any more newlines in them after
applying FUNCTION ; the default converts them to spaces. Return
value is a string containg the unaligned `org-mode' table."
  (unless (functionp function)
    (setq function (lambda (x) (replace-regexp-in-string "\n" " " x))))
  (mapconcat (lambda (x)                ; x is a line.
               (concat "| " (mapconcat function x " | ") " |"))
             table "\n"))
(defun yf/csv-to-table (beg end &optional separator)
  "Convert from BEG to END (a region in csv format) to an
`org-mode' table."
  (interactive
   (list
    (region-beginning)
    (region-end)
    (when current-prefix-arg
      (string-to-char (read-from-minibuffer "Separator? ")))))
  (require 'pcsv)
  (insert
   (yf/lisp-table-to-org-table
    (let
        ((pcsv-separator (or separator pcsv-separator)))
      (pcsv-parse-region beg end))))
  (delete-region beg end)
  (org-table-align))
(defun yf/insert-csv-as-table (filename &optional separator)
  "Insert a csv file as a org-mode table."
  (interactive
   (list
    (read-file-name "CSV file: ")
    (when current-prefix-arg
      (string-to-char
       (read-from-minibuffer "Separator? ")))))
  (yf/csv-to-table (point)
                   (progn (forward-char
                           (cadr (insert-file-contents filename)))
                          (point))
                   separator))

It's a bit longish due to how I factored the functions. What we need here is yf/insert-csv-as-table.

1

I suppose you have to convert your XLS to CSV first.

I want to avoid importing several CSV's to Org-mode, everytime manually.

My suggestion is similar to @erikstokes with org-table-import, but within #+BEGIN_SRC emacs-lisp source block. It's a very efficient way dealing with C-c C-v C-b (org-babel-execute-buffer).

You can also include column names in CSV.

For example, here is `tmp.csv' in the same directory.

v1 id,v2 size,v3 width,v4,v5
1,2,3,4,5
2,2,3,4,5
3,2,3,4,5
4,2,3,4,5

Here is Org-mode Emacs-Lisp source code.

#+BEGIN_SRC emacs-lisp :results value :exports both
(with-temp-buffer
 (org-table-import "tmp.csv" nil) ;; MEMO on `nil' arg is in the footnotes.
 (setq LST (org-table-to-lisp))
 ;; comment out or cut below one line if you don't have column names in CSV file.
 (append (list (car LST)) '(hline) (cdr (org-table-to-lisp)))
 )
#+END_SRC

#+NAME: TABLENAME-HERE-FOR-FURTHER-REUSE
#+RESULTS:
| v1 id | v2 size | v3 width | v4 | v5 |
|-------+---------+----------+----+----|
|     1 |       2 |        3 |  4 |  5 |
|     2 |       2 |        3 |  4 |  5 |
|     3 |       2 |        3 |  4 |  5 |
|     4 |       2 |        3 |  4 |  5 |

There is a good simple contents on importing CSV with Org-mode Babel: titled "Reading and writing files"

http://orgmode.org/cgit.cgi/org-mode.git/plain/doc/library-of-babel.org

Footenotes on nil arg from `org-table.el':

  • SEPARATOR specifies the field separator in the lines. It can have the following values:
  • '(4) Use the comma as a field separator
  • '(16) Use a TAB as field separator
  • integer When a number, use that many spaces as field separator
  • nil When nil, the command tries to be smart and figure out the separator in the following way:
    • when each line contains a TAB, assume TAB-separated material
    • when each line contains a comma, assume CSV material
    • else, assume one or more SPACE characters as separator.

I thank you for the comments of @Sean Allred, for the simplification and on the efficiency of process.

  • How is this effectively different from the accepted answer? – Sean Allred Jun 25 '15 at 17:28
  • @Sean Allred I thank you for your comment. You are right, my former suggestion was complicated. – RUserPassingBy Jun 26 '15 at 3:35
  • Very nice (and no R! :) ). – undostres Apr 15 '17 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.