* dates in orgtbl-sqlinsert
@ 2014-01-04 15:14 Thierry Banel
0 siblings, 0 replies; only message in thread
From: Thierry Banel @ 2014-01-04 15:14 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 1419 bytes --]
Hi all
I have slightly modified the Jason Riedy's orgtbl-sqlinsert.el package
to take into account Org dates.
The output format is configurable through the :datefmt parameter.
The default format is suitable for Sqlite.
(git patch attached)
An example:
Here is the source table:
#+ORGTBL: SEND faraway orgtbl-to-sqlinsert :splice nil :skipcols (5)
:datefmt "'%Y-%m-%d %H:%M:%S'" :sqlname "thetable"
| date | quty | color | status | useless |
|-------------------------+-------+--------+-------------+---------|
| [2013-04-17 wed. 11:34] | 123.4 | red | yes ok nogo | a |
| [2014-04-18 fri.] | 75.3 | yellow | no ko go | bb |
| <2015-04-19 sun. 11:35> | 355.1 | blue | no ok go | ccc |
| <2016-04-20 wed.> | 233.7 | white | yes ko go | dddd |
And here is the result:
-- BEGIN RECEIVE ORGTBL faraway
BEGIN TRANSACTION;
INSERT INTO thetable( date, quty, color, status ) VALUES ( '2013-04-17
11:34:00' , '123.4' , 'red' , 'yes ok nogo' );
INSERT INTO thetable( date, quty, color, status ) VALUES ( '2014-04-18
00:00:00' , '75.3' , 'yellow' , 'no ko go' );
INSERT INTO thetable( date, quty, color, status ) VALUES ( '2015-04-19
11:35:00' , '355.1' , 'blue' , 'no ok go' );
INSERT INTO thetable( date, quty, color, status ) VALUES ( '2016-04-20
00:00:00' , '233.7' , 'white' , 'yes ko go' );
COMMIT;
-- END RECEIVE ORGTBL faraway
Have fun
Thierry
[-- Attachment #2: 0001-convert-org-date-to-sql-date-in-orgtbl-sqlinsert.el.patch --]
[-- Type: text/x-patch, Size: 3676 bytes --]
From 41013e13d10bf4be4790670105615b2900cb1944 Mon Sep 17 00:00:00 2001
From: Thierry Banel <tbanelwebmin@free.fr>
Date: Sat, 4 Jan 2014 15:45:39 +0100
Subject: [PATCH] convert org-date to sql-date in orgtbl-sqlinsert.el
---
contrib/lisp/orgtbl-sqlinsert.el | 37 +++++++++++++++++++++++++++++++++++--
1 file changed, 35 insertions(+), 2 deletions(-)
diff --git a/contrib/lisp/orgtbl-sqlinsert.el b/contrib/lisp/orgtbl-sqlinsert.el
index b00c93d..82e9e6a 100644
--- a/contrib/lisp/orgtbl-sqlinsert.el
+++ b/contrib/lisp/orgtbl-sqlinsert.el
@@ -2,7 +2,8 @@
;; Copyright (C) 2008-2013 Free Software Foundation
-;; Author: Jason Riedy <jason@acm.org>
+;; Author: Jason Riedy <jason@acm.org>,
+;; modified by Thierry Banel tbanelwebmin at free dot fr
;; Keywords: org, tables, sql
;; This file is not part of GNU Emacs.
@@ -25,6 +26,32 @@
;; Converts an orgtbl to a sequence of SQL insertion commands.
;; Table cells are quoted and escaped very conservatively.
+;; Example:
+;; Using this table as source,
+;; adding somewhere in the same file a block like this:
+;; -- BEGIN RECEIVE ORGTBL faraway
+;; -- END RECEIVE ORGTBL faraway
+;; and typing C-c C-c on the first "pipe" of the table,
+;; fills the BEGIN RECEIVE ORGTBL block
+;;
+;; #+ORGTBL: SEND faraway orgtbl-to-sqlinsert :splice nil :skipcols (5) :datefmt "'%Y-%m-%d %H:%M:%S'" :sqlname "thetable"
+;; | date | quty | color | status | useless |
+;; |-------------------------+-------+--------+-------------+---------|
+;; | [2013-04-17 wed. 11:34] | 123.4 | red | yes ok nogo | a |
+;; | [2014-04-18 fri.] | 75.3 | yellow | no ko go | bb |
+;; | <2015-04-19 sun. 11:35> | 355.1 | blue | no ok go | ccc |
+;; | <2016-04-20 wed.> | 233.7 | white | yes ko go | dddd |
+;;
+;;
+;; -- BEGIN RECEIVE ORGTBL faraway
+;; BEGIN TRANSACTION;
+;; INSERT INTO thetable( date, quty, color, status ) VALUES ( '2013-04-17 11:34:00' , '123.4' , 'red' , 'yes ok nogo' );
+;; INSERT INTO thetable( date, quty, color, status ) VALUES ( '2014-04-18 00:00:00' , '75.3' , 'yellow' , 'no ko go' );
+;; INSERT INTO thetable( date, quty, color, status ) VALUES ( '2015-04-19 11:35:00' , '355.1' , 'blue' , 'no ok go' );
+;; INSERT INTO thetable( date, quty, color, status ) VALUES ( '2016-04-20 00:00:00' , '233.7' , 'white' , 'yes ko go' );
+;; COMMIT;
+;; -- END RECEIVE ORGTBL faraway
+
;;; Code:
(defun orgtbl-to-sqlinsert (table params)
@@ -47,6 +74,9 @@ are:
:nowebname If not nil, used as a wrapping noweb fragment name.
+:datefmt The format used for converting Ord dates
+ by defaut it is '%Y-%m-%d %H:%M:%S', suitable for Sqlite
+
The most important parameters of ORGTBL-TO-GENERIC for SQL are:
:splice When set to t, return only insert statements, don't wrap
@@ -87,6 +117,7 @@ this function is called."
:lend " );"
:sep " , "
:hline nil
+ :datefmt "'%Y-%m-%d %H:%M:%S'"
:remove-nil-lines t))
(params (org-combine-plists params2 params))
(sqlname (plist-get params :sqlname)))
@@ -108,7 +139,9 @@ this function is called."
(defun orgtbl-sql-strip-and-quote (str)
"Apply ORGBTL-SQL-QUOTE and ORGTBL-SQL-STRIP-DOLLARS-ESCAPES-TILDES
to sanitize STR for use in SQL statements."
- (cond ((stringp str)
+ (cond ((string-match org-ts-regexp0 str)
+ (format-time-string (plist-get params :datefmt) (org-time-string-to-time str)))
+ ((stringp str)
(orgtbl-sql-quote (orgtbl-sql-strip-dollars-escapes-tildes str)))
((sequencep str) (mapcar 'orgtbl-sql-strip-and-quote str))
(t nil)))
--
1.7.9.5
^ permalink raw reply related [flat|nested] only message in thread
only message in thread, other threads:[~2014-01-04 15:15 UTC | newest]
Thread overview: (only message) (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2014-01-04 15:14 dates in orgtbl-sqlinsert Thierry Banel
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).