From mboxrd@z Thu Jan 1 00:00:00 1970 From: Thierry Banel Subject: dates in orgtbl-sqlinsert Date: Sat, 04 Jan 2014 16:14:56 +0100 Message-ID: <52C82570.6080607@free.fr> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="------------000802050707080207040804" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:34801) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1VzSwM-0006Fr-5C for emacs-orgmode@gnu.org; Sat, 04 Jan 2014 10:15:07 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1VzSwK-0003DX-M3 for emacs-orgmode@gnu.org; Sat, 04 Jan 2014 10:15:06 -0500 Received: from smtp2-g21.free.fr ([2a01:e0c:1:1599::11]:33672) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1VzSwK-00030E-07 for emacs-orgmode@gnu.org; Sat, 04 Jan 2014 10:15:04 -0500 Received: from [IPv6:2a01:e35:2e21:def0:8883:f157:ad73:e47] (unknown [IPv6:2a01:e35:2e21:def0:8883:f157:ad73:e47]) by smtp2-g21.free.fr (Postfix) with ESMTP id 443C44B0039 for ; Sat, 4 Jan 2014 16:14:58 +0100 (CET) List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org This is a multi-part message in MIME format. --------------000802050707080207040804 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 --------------000802050707080207040804 Content-Type: text/x-patch; name="0001-convert-org-date-to-sql-date-in-orgtbl-sqlinsert.el.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0001-convert-org-date-to-sql-date-in-orgtbl-sqlinsert.el.pat"; filename*1="ch" >From 41013e13d10bf4be4790670105615b2900cb1944 Mon Sep 17 00:00:00 2001 From: Thierry Banel 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 +;; Author: Jason Riedy , +;; 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 --------------000802050707080207040804--