Re: [R] SQLite: When reading a table, a "\r" is padded onto the last column. Why?

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Wed 03 Jan 2007 - 07:43:05 GMT

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@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@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@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.

Received on Wed Jan 03 19:28:11 2007

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Wed 03 Jan 2007 - 18:30:30 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.