/org-clock-export

Export org-clock data to a CSV file

Primary LanguageEmacs LispGNU General Public License v3.0GPL-3.0

org-clock-export.el

Fully customizable export of org-clock data into a CSV file.

Usage

Install dependencies:

org-ql
https://github.com/alphapapa/org-ql

Installation

Clone this repository into your load path.

git clone https://github.com/legalnonsense/org-clock-export.git

Then:

(use-package org-clock-export)
;; or
(require 'org-clock-export)

Or, use this use-package declaration with all custom variables set to the default values:

(use-package org-clock-export
  :config
  (setq org-clock-export-org-ql-query nil
        org-clock-export-files nil
        org-clock-export-file-name (concat user-emacs-directory "clock-export.csv")
        org-clock-export-buffer "*ORG-CLOCK-EXPORT CSV*"
        org-clock-export-delimiter ","
        org-clock-export-data-format '("date" (concat start-month "/" start-day "/" start-year)
                                       "hours" total-hours
                                       "minutes" total-minutes
                                       "description" (org-entry-get (point) "ITEM")
                                       "hourly rate" (or (org-entry-get (point) "HOURLY-RATE") "325"))))

Usage

org-clock-export-data does the heavy lifting. You must set this variable yourself. The default value, shown here, is an example.

It is in the style of a plist:

(setq org-clock-export-data '( "date" (concat start-month "/" start-day "/" start-year)
                               "hours" total-hours
                               "minutes" total-minutes
                               "description" (org-entry-get (point) "ITEM")
                               "hourly rate" (or (org-entry-get (point) "HOURLY-RATE") "325")))

The rules are:

  1. The first element is a string to describe the category of data
  2. The following sexp is evaluated at each org heading containing a clock line
  3. Each sexp can use the following variables, which are bound to values based on each clock-line. The following should be self-explanatory:
    • start-year
    • start-month
    • start-day
    • start-dow [day of week]
    • start-hour
    • start-minute
    • end-year
    • end-month
    • end-day
    • end-dow
    • end-hour
    • end-minute
    • total-hours
    • total-minutes
  4. The sexp be any code that will be run at each heading containing a clock line. For example, you can use org-entry-get (or any other function) to grab data from the heading and put it into the CSV.
  5. The sexp can be a string if you want one field to be constant and not depend on the heading or the clock data.
  6. The only rules for these sexps are that they must return a string, and that they must not move the point (i.e., you should save any excursion).
  7. The order of the data in org-clock-export-data reflects the order it will appear in the CSV

If you call org-clock-export with a prefix (i.e., press C-u before calling the command), the csv file will be saved to the location specified by org-clock-export-file-name.

If you call it with two prefixes, you will be prompted to select the export file.

Example

Consider the following file org file:

* Checking if co-coworker did any work :Gus:
:PROPERTIES:
:HOURLY-RATE: 250
:END:
:LOGBOOK:
CLOCK: [2021-03-01 Mon 16:40]--[2021-03-01 Mon 16:50] =>  0:10
CLOCK: [2021-03-01 Mon 15:45]--[2021-03-01 Mon 15:50] =>  0:05
:END:
* Sending an email to a non-responsive co-worker :email:Gus:
:PROPERTIES:
:HOURLY-RATE: 125
:END:
:LOGBOOK:
CLOCK: [2021-03-01 Mon 16:05]--[2021-03-01 Mon 16:17] =>  0:12
:END:
* Doing the co-coworker’s work for them :Gus:
:LOGBOOK:
CLOCK: [2021-03-01 Mon 16:17]--[2021-03-01 Mon 16:30] =>  0:13
:END:
* Talking about mindless drivel with customer :Abby:
:LOGBOOK:
CLOCK: [2021-02-28 Sun 16:19]--[2021-02-28 Sun 17:19] =>  1:00
:END:

org-clock-export will produce the following:

date,hours,minutes,description,hourly rate
03/01/2021,0,10,Checking if co-coworker did any work,250
03/01/2021,0,05,Checking if co-coworker did any work,250
03/01/2021,0,12,Sending an email to a non-responsive co-worker,125
03/01/2021,0,13,Doing the co-coworker’s work for them,325
02/28/2021,1,00,Talking about mindless drivel with customer,325

A few notes:

  1. Note that for the hourly rate line, we ensure a string (and not nil) is returned. If a heading does not have an HOURLY-RATE property, org-entry-get will return nil. Hence the need to set a default of 325.
  2. If a heading has more than one clock line (here, Checking if co-coworker did any work), then the CSV file will contain an entry for each clock line.

Restricting exported data

What if you only want to export clock data for certain headings, or for a certain time? Then you use the variable org-clock-export-org-ql-query. This will require you to understand how to use org-ql. The variable must be a query acceptable to org-ql-select. For example, suppose you only wanted to export time entries from headings tagged with :Abby:. Then:

(setq org-clock-export-org-ql-query '(tags "Abby"))

And now the output is:

date,hours,minutes,description,hourly rate
02/28/2021,1,00,Talking about mindless drivel with customer,325

You can use org-clock-export-org-ql-query to restrict to certain tags, dates, times, and otherwise harness the full power of org-ql. For example, if you only want to export entries for a given date with the tag “Gus”, use:

(setq org-clock-export-org-ql-query '(and (clocked :on today) (tags "Gus")))

And you’ll get:

date,hours,minutes,description,hourly rate
03/01/2021,0,10,Checking if co-coworker did any work,250
03/01/2021,0,05,Checking if co-coworker did any work,250
03/01/2021,0,12,Sending an email to a non-responsive co-worker,125
03/01/2021,0,13,Doing the co-coworker’s work for them,325

Final example

This should all be pretty easy to follow. If not, here’s a final arbitrary example:

  (setq org-clock-export-org-ql-query nil)
  (setq org-clock-export-data '( "name" "Jack Jackson"
                                 "date" (concat start-month "/" start-day "/" start-year)
                                 "start time" (concat start-hour ":" start-minute)
                                 "end time" (concat end-hour ":" end-minute)
                                 "total time" (concat total-hours ":" total-minutes)
                                 ;; The headline enclosed in quotes (in case there are commas)
                                 "description" (concat "\"" (org-entry-get (point) "ITEM") "\"")
                                 "file name" (buffer-file-name)
                                 "hourly rate" (or (org-entry-get (point) "HOURLY-RATE") "325")))
(org-clock-export)

Results:

name,date,start time,end time,total time,description,file name,hourly rate
Jack Jackson,03/01/2021,16:40,16:50,0:10,"Checking if co-coworker did any work",/home/jeff/.emacs.d/lisp/org-clock-export/test.org,250
Jack Jackson,03/01/2021,15:45,15:50,0:05,"Checking if co-coworker did any work",/home/jeff/.emacs.d/lisp/org-clock-export/test.org,250
Jack Jackson,03/01/2021,16:05,16:17,0:12,"Sending an email to a non-responsive co-worker",/home/jeff/.emacs.d/lisp/org-clock-export/test.org,125
Jack Jackson,03/01/2021,16:17,16:30,0:13,"Doing the co-coworker’s work for them",/home/jeff/.emacs.d/lisp/org-clock-export/test.org,325
Jack Jackson,02/28/2021,16:19,17:19,1:00,"Talking about mindless drivel with customer",/home/jeff/.emacs.d/lisp/org-clock-export/test.org,325

Other custom variables

NameDescriptionDefault value
org-clock-export-bufferBuffer used to export CSV data*ORG-CLOCK-EXPORT CSV*
org-clock-export-file-nameFile to export data to(concat user-emacs-directory "clock-export.csv")
org-clock-export-delimiterDelimiter (a string) used in the CSV output,
org-clock-export-org-ql-querySee abovenil
org-clock-export-filesIf nil, use `org-agenda-files’. Otherwise, specify a file or list of filesnil
org-clock-export-dataSee above

Overriding default values

You can override any of the default values by using keywords in the call to org-clock-export. The keywords are:

org-files
org-ql-query
csv-data-format
output-file
delimiter
output-buffer

If you omit any of the keywords, the default value is used. If you set any of the keywords to nil, then nil is used instead of the default value. Example:

(org-clock-export

 ;; Only export anything with a category of "Jones"
 :org-ql-query '(category "Jones")
 :csv-data-format '("matter" (org-entry-get (point) "SOME-PROPERTY" t)
                    ;; I need the date in MM/DD/YYYY format 
                    "date" (concat start-month "/" start-day "/" start-year)
                    ;; Here, I want to remove anything in brackets from the headline text
                    ;; And I need the headline enclosed in quotes in case there is a comma in the text
                    "activity_description" (concat "\"" (s-trim (replace-regexp-in-string "\\[.+\\]" "" (org-entry-get (point) "ITEM"))) "\"")
                    "note" (concat "\"" (s-trim (replace-regexp-in-string "\\[.+\\]" "" (org-entry-get (point) "ITEM"))) "\"")
                    "price" "425"
                    ;; I need the quantity to the number of hours in decimal form
                    "quantity" (concat total-hours "." (substring (cadr (split-string (format "%f" (/ (string-to-number total-minutes) 60.0)) "\\.")) 0 2))
                    "type" "TimeEntry")
 :output-file "~/path/to/file.csv"
 :delimiter ",")

A note about escaping characters

Since there is no standard for CSV files, you will need to ensure the strings returned by org-clock-export-data are appropriately escaped.

Other efforts

org-clock-csv
https://github.com/atheriel/org-clock-csv. This did not allow me to export my time data in the way I needed and so I wrote this.