Creating org-tables from the results of a code block

3
votes

I am trying to create an org-table based on the content of a CSV file that uses ";" as a delimiter.

I thought it would be easy to have a source code block to "cat" the content of the file and then pass the result to a function that creates the table, but I got stuck: I can't find a way to use the "results" of the first source code block. The function I know (org-table-convert-region) expects a region to work on and I don't know how to pass the "cat"-ed text as a region.

#+NAME: csvraw
#+BEGIN_SRC sh :results raw
  cat afile.csv
#+END_SRC

I'd appreciate your help to generate a code block that produces an org-table out of my csv file, which contains lines like the following:

ID;Region;SubRegion;Area;No
1234;Asia;India;45;2
24251;Europe;Romania;456;67
2
emacsorg-modeorg-babel

2 Answers

6
votes

There is org-table-convert-region (bound to C-c |) which can do the transformation fairly simply. The only trick is to specify ; as the separator. You can do that by invoking it with the proper prefix argument - the doc string says:

(org-table-convert-region BEG0 END0 &optional SEPARATOR)

Convert region to a table.

The region goes from BEG0 to END0, but these borders will be moved
slightly, to make sure a beginning of line in the first line is included.

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
(64)    Prompt for a regular expression as field separator
integer  When a number, use that many spaces, or a TAB, as field separator
regexp   When a regular expression, use it to match the 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.

The (64) value is just three C-u in a row, so the process is as follows:

  • insert the CSV file with C-x i.
  • C-x C-x to mark the inserted contents as the active region.
  • C-u C-u C-u C-c | ; RET

What's even cooler, leaving an empty line in the CSV file between the first line and the rest of the lines, will make the first line a header in the table automatically.

And you can wrap it up in a code block as well:

#+begin_src elisp :var file="/tmp/foo.csv" :results raw
  (defun csv-to-table (file)
    (with-temp-buffer
      (erase-buffer)
      (insert-file file)
      (org-table-convert-region (point-min) (point-max) ";")
      (buffer-string)))

  (csv-to-table file)
#+end_src

#+RESULTS:
| a | b | c |
|---+---+---|
| d | e | f |
| g | h | i |
2
votes
(defun jea-convert-csv-to-org-table (fname)
  (interactive "fCSV to convert: ")
  (let ((result '("|-\n")))
    (with-temp-buffer
      (save-excursion (insert-file-contents-literally fname))
      (while (and (not (eobp)) (re-search-forward "^\\(.+\\)$" nil t nil))
        (push (concat "|" (replace-regexp-in-string ";" "|" (match-string 1)) "|\n")
              result))
      (push '"|-\n" result))
    (concat (seq-mapcat #'identity (reverse result)))))

install the elisp code in your ~/.emacs file and restart emacs. Or, better yet, eval it into existence (CTRL+x, CTRL+e or ALT+x eval-last-sexp).

#+NAME: csvraw
#+BEGIN_SRC elisp :results raw
  (jea-convert-csv-to-org-table "/Users/jamesanderson/Downloads/test1.csv")
#+END_SRC

note the change to elisp from sh in the above. here is a gif of it in action: emacs converting csv to org table

The code is not optimized for large files, and, to be frank, quite quickly thrown together. But, after a tiny bit of testing, seems to work.