[R-pkgs] sqldf 0.2-0

Gabor Grothendieck ggrothendieck at gmail.com
Fri Dec 25 20:25:15 CET 2009

A new version of sqldf, version 0.2-0, has been uploaded to CRAN and
should be available on most mirrors by now.


- works with the new version of DBI package, DBI 0.2-5.   The default
action of this version of DBI quotes those column names in select
statements that are SQL reserved words (rather than appending __1 to
them which was the previous default action).  As a result it should no
longer be necessary to refer to Time as Time__1, etc. Thus this now
works where BOD is a 6 row data frame that comes with R and has the
indicated column names:

     sqldf("select Time, demand from BOD")

- if the libspatialite-1.dll sqlite loadable extension is found on
PATH then it will automatically be loaded into SQLite the first time
sqldf is called in a session.  This gives access to several dozen new
SQL functions.  For a complete list see:


  For example, stddev_pop and var_pop are functions provided in the
dll used in this example:

     sqldf("select avg(demand) mean, stddev_pop(demand) sd,
var_pop(demand) var from BOD")

  (If the loadable extension is not found then sqldf still works with
all the functionality it previously had but the new functions in the
loadable extension will, of course, not be available.  Note that this
loadable extension does not come with sqldf ; however, it is free
software distributed under the Mozilla public license and the user may
download it from http://www.gaia-gis.it/spatialite/binaries.html  and
place the dll on their PATH to get automatic access from sqldf).

- new filter argument on read.csv.sql

- read.csv2.sql added.  This is like read.csv.sql but uses a default
filter which translates all commas to dots.  On Windows it pipes the
input file through a custom vbscript that comes with sqldf and on
other systems it uses tr , .   (In both cases the result is the same.)
 read.csv2.sql is used to read data files originating in certain
European countries.


sqldf is an R package that allows one to manipulate R data frames and
input files using SQL from within R.  It is layered on top of RSQLite,
DBI and the sqlite database.  Typical usage involves a single line of
R code calling sqldf and containing a string argument which is the
select statement.  sqldf automatically sets up a database and table
definitions, runs the select statement, retrieves the result and
destroys the database.  For more information including many examples
of use see the home page at:


and the help files in the package.

More information about the R-packages mailing list