Emacs: import a CSV into org-mode

Emacs has a very nice extension by the name of org-mode.

I would like to be able to easily load CSV files into org-mode without significant grief. All I've been able to find is table-import or table-capture, which, simply put, don't work even approximately well.

Note that part of my issue is text strings with a comma within them. 1,2,3,4 is different than 1,2,"3,4".

Is there a function out there or a perl script that one could run to transform a csv file into org-mode format?

Thanks!

From the org-mode manual:

> C-c | Convert the active region to > table. If every line contains at least > one TAB character, the function > assumes that the material is tab > separated. If every line contains a > comma, comma-separated values (CSV) > are assumed. If not, lines are split > at whitespace into fields. You can use > a prefix argument to force a specific > separator: C-u forces CSV, C-u C-u > forces TAB, and a numeric argument N > indicates that at least N consecutive > spaces, or alternatively a TAB will be > the separator. If there is no active > region, this command creates an empty > Org table.

So just paste the data into an org file, select it, and do C-u C-c | .

I'm assuming you want to convert your CSV specifically into org-mode tables. If that's not the case, you may want to be more explicit about output format in your question.

Something like this should do it, or at least get you a starting point you can hack on:

  #!/usr/bin/perl
  
  use strict;
  use warnings;
  
  use Text::CSV;
  
  my $csv = Text::CSV->new();
  
  while ( my $line = <DATA> ) {
    if ( $csv->parse( $line )) {
      my $str = join '|' , $csv->fields();
      print "|$str|\n";
    }
  }
  
  
  __DATA__
  1,2,3,4
  1,2,"3,4"

Have a look at:

C-h f org-table-convert-region

I'm always converting csv so I added this to my .emacs.

(defun org-convert-csv-table (beg end)
  (interactive (list (mark) (point)))
  (org-table-convert-region beg end ",")
  )

(add-hook 'org-mode-hook
      (lambda ()
    (define-key org-mode-map (kbd "<f6>") 'org-convert-csv-table)))

update

Here is another function that considers the quoted commas as well :

 a,"12,12",b --> a | 12,12 |b

feel free to improve it :-).

(defun org-convert-csv-table (beg end)
  ; convert csv to org-table considering "12,12"
  (interactive (list (point) (mark)))
  (replace-regexp "\\(^\\)\\|\\(\".*?\"\\)\\|," (quote (replace-eval-replacement
						    replace-quote (cond ((equal "^" (match-string 1)) "|")
											       ((equal "," (match-string 0)) "|")
											       ((match-string 2))) ))  nil  beg end)

Try this:

;; Insert a file and convert it to an org table
(defun aleblanc/insert-file-as-org-table (filename)
  "Insert a file into the current buffer at point, and convert it to an org table."
  (interactive (list (ido-read-file-name "csv file: ")))
  (let* ((start (point))
	(end (+ start (nth 1 (insert-file-contents filename)))))
    (org-table-convert-region start end)
    ))

Here is a bit of a hack-job, but it works.

when you export the CSV file, force quotes around each entry, then replace all "," as a vertical bar.

Finally, I make a macro that does something like this:

C-a    ; Beginning-of-line
|      ; Self-insert-char
C-e    ; end-of-line
|      ; Self-insert-char
<down> ; Down one line

(I am not 100% sure if | is a self-insert-char or not, so its best to record your own macro)

Hit tab somewhere in the middle of the table, and org-mode formats it properly. I find this easier to do in a narrowed region.

Caveat: If you have a vertical bar in your input.. it probably won't work quite right. Situations like that should be easy to spot, and relatively easy to fix.

Generally, when importing something text-like into org-mode, I have found a combination of some smart macro writing, and search-replace to be the easiest way

I hope that helps.