Fully customizable export of org-clock data into a CSV file.
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-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"))))
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:
- The first element is a string to describe the category of data
- The following sexp is evaluated at each org heading containing a clock line
- 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
- 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. - The sexp can be a string if you want one field to be constant and not depend on the heading or the clock data.
- 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).
- 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-export-file-name
.
If you call it with two prefixes, you will be prompted to select the export file.
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:
- 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 of325
. - 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.
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
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
Name | Description | Default value |
---|---|---|
org-clock-export-buffer | Buffer used to export CSV data | *ORG-CLOCK-EXPORT CSV* |
org-clock-export-export-file-name | File to export data to | (concat user-emacs-directory "clock-export.csv") |
org-clock-export-delimiter | Delimiter (a string) used in the CSV output | , |
org-clock-export-org-ql-query | See above | nil |
org-clock-export-files | If nil, use `org-agenda-files’. Otherwise, specify a file or list of files | nil |
org-clock-export-data | See above |
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 nil
:csv-data-format '("matter" (org-entry-get (point) "SOME-PROPERTY" t)
"date" (concat start-month "/" start-day "/" start-year)
"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"
"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 ",")
Since there is no standard for CSV files, you will need to ensure the strings returned by org-clock-export-data
are appropriately escaped.
- 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.