emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Thierry Banel <tbanelwebmin@free.fr>
To: emacs-orgmode@gnu.org
Subject: dates in orgtbl-sqlinsert
Date: Sat, 04 Jan 2014 16:14:56 +0100	[thread overview]
Message-ID: <52C82570.6080607@free.fr> (raw)

[-- 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


                 reply	other threads:[~2014-01-04 15:15 UTC|newest]

Thread overview: [no followups] expand[flat|nested]  mbox.gz  Atom feed

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=52C82570.6080607@free.fr \
    --to=tbanelwebmin@free.fr \
    --cc=emacs-orgmode@gnu.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
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).