Søren Højsgaard
2007-Jan-03 01:23 UTC
[R] SQLite: When reading a table, a "\r" is padded onto the last column. Why?
Hi, I put the iris data into a SQLite database with dbWriteTable(con, "iris", iris, row.names=F, overwrite = T) Then I retrieve data from the database with rs <- dbSendQuery(con, "select * from iris") d1 <- fetch(rs) dbClearResult(rs) Then I get> head(d1)Sepal_Length Sepal_Width Petal_Length Petal_Width Species 1 5.1 3.5 1.4 0.2 setosa\r 2 4.9 3.0 1.4 0.2 setosa\r 3 4.7 3.2 1.3 0.2 setosa\r 4 4.6 3.1 1.5 0.2 setosa\r 5 5.0 3.6 1.4 0.2 setosa\r 6 5.4 3.9 1.7 0.4 setosa\r Can anyone explain the extra "\r" at the end? I am on Windows XP using R 2.4.1 Thanks in advance S?ren
Prof Brian Ripley
2007-Jan-03 07:43 UTC
[R] SQLite: When reading a table, a "\r" is padded onto the last column. Why?
I guess you are using package RSQLite without telling us (or telling us
the version), and that your example is incomplete?
Using RSiteSearch("RSQLite Windows") quickly shows that this is a
previously reported problem with the package, e.g.:
http://finzi.psych.upenn.edu/R/Rhelp02a/archive/72515.html
I believe the issue is that RSQLite actually writes out a CRLF-terminated
text file and imports that into SQLite. (I checked version 0.4-15.) It
seems function safe.write() needs to be modified to write to a binary-mode
connection since SQLite appears to require LF-terminated files.
Using RODBC to work with SQLite databases works correctly even under
Windows (and is much more efficient at writing to the database).
[I am not sure who is actually maintaining RSQLite, so am Cc: both the
stated maintainer and the person who prepared the package for
distribution. The posting guide asked you to contact the maintainer: what
response did _you_ get?]
On Wed, 3 Jan 2007, S?ren H?jsgaard wrote:
> Hi,
>
> I put the iris data into a SQLite database with
>
> dbWriteTable(con, "iris", iris, row.names=F, overwrite = T)
>
> Then I retrieve data from the database with
>
> rs <- dbSendQuery(con, "select * from iris")
> d1 <- fetch(rs)
> dbClearResult(rs)
>
> Then I get
>> head(d1)
> Sepal_Length Sepal_Width Petal_Length Petal_Width Species
> 1 5.1 3.5 1.4 0.2 setosa\r
> 2 4.9 3.0 1.4 0.2 setosa\r
> 3 4.7 3.2 1.3 0.2 setosa\r
> 4 4.6 3.1 1.5 0.2 setosa\r
> 5 5.0 3.6 1.4 0.2 setosa\r
> 6 5.4 3.9 1.7 0.4 setosa\r
>
> Can anyone explain the extra "\r" at the end? I am on Windows XP
using R 2.4.1
> Thanks in advance
> S?ren
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
--
Brian D. Ripley, ripley at stats.ox.ac.uk
Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/
University of Oxford, Tel: +44 1865 272861 (self)
1 South Parks Road, +44 1865 272866 (PA)
Oxford OX1 3TG, UK Fax: +44 1865 272595
ronggui
2007-Jan-03 16:22 UTC
[R] SQLite: When reading a table, a "\r" is padded onto the last column. Why?
RSQLite can import data from a large file directly (via "dbWriteTable"). This future is quite appealing. On 1/3/07, Prof Brian Ripley <ripley at stats.ox.ac.uk> wrote:> I guess you are using package RSQLite without telling us (or telling us > the version), and that your example is incomplete? > > Using RSiteSearch("RSQLite Windows") quickly shows that this is a > previously reported problem with the package, e.g.: > > http://finzi.psych.upenn.edu/R/Rhelp02a/archive/72515.html > > I believe the issue is that RSQLite actually writes out a CRLF-terminated > text file and imports that into SQLite. (I checked version 0.4-15.) It > seems function safe.write() needs to be modified to write to a binary-mode > connection since SQLite appears to require LF-terminated files. > > Using RODBC to work with SQLite databases works correctly even under > Windows (and is much more efficient at writing to the database). > > [I am not sure who is actually maintaining RSQLite, so am Cc: both the > stated maintainer and the person who prepared the package for > distribution. The posting guide asked you to contact the maintainer: what > response did _you_ get?] > > > On Wed, 3 Jan 2007, S?ren H?jsgaard wrote: > > > Hi, > > > > I put the iris data into a SQLite database with > > > > dbWriteTable(con, "iris", iris, row.names=F, overwrite = T) > > > > Then I retrieve data from the database with > > > > rs <- dbSendQuery(con, "select * from iris") > > d1 <- fetch(rs) > > dbClearResult(rs) > > > > Then I get > >> head(d1) > > Sepal_Length Sepal_Width Petal_Length Petal_Width Species > > 1 5.1 3.5 1.4 0.2 setosa\r > > 2 4.9 3.0 1.4 0.2 setosa\r > > 3 4.7 3.2 1.3 0.2 setosa\r > > 4 4.6 3.1 1.5 0.2 setosa\r > > 5 5.0 3.6 1.4 0.2 setosa\r > > 6 5.4 3.9 1.7 0.4 setosa\r > > > > Can anyone explain the extra "\r" at the end? I am on Windows XP using R 2.4.1 > > Thanks in advance > > S?ren > > > > ______________________________________________ > > R-help at stat.math.ethz.ch mailing list > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > > and provide commented, minimal, self-contained, reproducible code. > > > > -- > Brian D. Ripley, ripley at stats.ox.ac.uk > Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ > University of Oxford, Tel: +44 1865 272861 (self) > 1 South Parks Road, +44 1865 272866 (PA) > Oxford OX1 3TG, UK Fax: +44 1865 272595 > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > > >-- Ronggui Huang Department of Sociology Fudan University, Shanghai, China ??? ????????