Converting an xls file to org-mode?

Question

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.

Answer 1

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.

Answer 2

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/

Answer 3

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.

Answer 4

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.