#+TITLE: Code which outputs code in another language
#+DATE: 2010-12-24
#+LANGUAGE: en_US
#+BABEL: :engine msosql :eval no
* Input data
#+results: input
| ID | val1 | val2 |
|-----+------+------|
| ABC | 13 | 34 |
| DEF | 55 | 28 |
* Code
The following code takes a table (or tab separated file) in input, and
generates the SQL =INSERT INTO= commands. This is to import some data directly
into an SQL database.
#+srcname: insert-values-into-table
#+begin_src sh :var table="mytable" :var data=input :results output :exports
both :colnames no
echo "$data" | awk -F "\t" -v table=$table -v quote="'" '
# column names
NR == 1 {
for ( i = 1; i <= NF; i++ ) {
sub(/ *$/, "", $i) # remove trailing spaces
listOfColumns = listOfColumns $i
if ( i < NF ) listOfColumns = listOfColumns ", "
}
}
# values
(2 <= NR) && ($0 !~ /^[\t]+$/) {
print "INSERT INTO " table " (" listOfColumns ")"
printf " VALUES ("
for ( i = 1; i <= NF; i++ ) {
sub(/ *$/, "", $i) # remove trailing spaces
switch ($i) {
case /^[[:digit:]]+$/: # only numbers
printf $i
break
case "NULL": # reserved keyword
printf $i
break
default:
printf quote $i quote
}
if ( i < NF ) printf ", "
}
print ")"
}
'
#+end_src
This can be put to Worg, if wished, as is, or with small adaptations
(depending on the [[*Extra%20questions][Extra questions]]).
* Output data
** What I got
... when using =output= results.
#+results: insert-values-into-table
#+begin_example
INSERT INTO mytable (ID, val1, val2)
VALUES ('ABC', 13, 34)
INSERT INTO mytable (ID, val1, val2)
VALUES ('DEF', 55, 28)
#+end_example
If using =pp= or =code=, things get worse (I get tables!?)...
** What I wish
I wish I could say that the results is some code in some language, in order to
obtain the following.
#+results: insert-values-into-table
#+begin_src sql
INSERT INTO mytable (ID, val1, val2)
VALUES ('ABC', 13, 34)
INSERT INTO mytable (ID, val1, val2)
VALUES ('DEF', 55, 28)
#+end_src
That way, both editing in Org and exported code would always be made in the
right fontification...
Is that something sensible, that'd be worth being added?
* Extra questions
1. It seems that =:colnames yes= has become the default, as, in the above
example, I must say =no= to make it work properly?
2. In the same spirit, when using the above code a couple of weeks ago,
everything was fine for the =hlines= handling: it was passed to the =awk=
program (and I ignored the line when =NR = 2=). Now, not anymore? Hence, I
slightly edited my above code, to be compliant with the new way it works.
Bug or feature? Temporarily or definitive?
Happy christmas!
Best regards,
Seb
PS- I will be completely out (normally) for one week, starting from tomorrow.
--
Sébastien Vauban
_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode@gnu.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode
