Re: [R] SQLite: When reading a table, a "\r" is padded onto the last column. Why?
On 1/4/07, Seth Falcon <[EMAIL PROTECTED]> wrote: Prof Brian Ripley <[EMAIL PROTECTED]> writes: > [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?] For the record, I will be (have been) taking on the maintainer role for RSQLite. The Maintainer field will be updated in the next version. As to the '\r' problem, a release candidate RSQLite 0.4-17 is available here: http://bioconductor.org/packages/misc/ This version uses prepared queries to implement dbWriteTable. This should resolve the '\r' issue on Windows and should also be considerably more efficient. Soren, can you give this one a try and let me know if it works for you? When write a data frame to db table, the problem of "\r" is fixed. But for importing data frome file, the problem is still there. When if the final line lacks the eol sign "\n", "\001x\001(" comes up. dbWriteTable(con,"test","c:/test.txt",sep="\t",head=T,over=T,eol="\n") [1] TRUE Warning message: incomplete final line found by readTableHeader on 'c://test.txt' dbReadTable(con,"test") a b 1 1 2\r 2 3 \r 3 1 3\r 4 0 5\001x\001( dbWriteTable(con,"test","c:/test.txt",sep="\t",head=T,over=T,eol="\n") [1] TRUE dbReadTable(con,"test") a b 1 1 2\r 2 3 \r 3 1 3\r 4 0 5\r data(USArrests) dbWriteTable(con, "USArrests", USArrests, overwrite = T) [1] TRUE dbReadTable(con, "USArrests") Murder Assault UrbanPop Rape Alabama 13.2 236 58 21.2 Alaska 10.0 263 48 44.5 Arizona 8.1 294 80 31.0 Arkansas 8.8 190 50 19.5 California9.0 276 91 40.6 Colorado 7.9 204 78 38.7 sessionInfo() R version 2.4.0 Patched (2006-11-21 r39949) i386-pc-mingw32 locale: LC_COLLATE=Chinese_People's Republic of China.936;LC_CTYPE=Chinese_People's Republic of China.936;LC_MONETARY=Chinese_People's Republic of China.936;LC_NUMERIC=C;LC_TIME=Chinese_People's Republic of China.936 attached base packages: [1] "stats" "graphics" "grDevices" "utils" "datasets" "methods" [7] "base" other attached packages: RSQLite DBI "0.4-17" "0.1-11" Recent work on RSQLite has focused on integrating SQLite3's type system into the interface. We now rely on the column type in the DB when retrieving results. Previously, type.convert was used. I'm fairly certain these changes will result in changes in behavior -- in most cases, I think the changes are for the better. + seth __ 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. -- Ronggui Huang Department of Sociology Fudan University, Shanghai, China 黄荣贵 复旦大学社会学系 __ 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.
Re: [R] SQLite: When reading a table, a "\r" is padded onto the last column. Why?
Prof Brian Ripley <[EMAIL PROTECTED]> writes: > [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?] For the record, I will be (have been) taking on the maintainer role for RSQLite. The Maintainer field will be updated in the next version. As to the '\r' problem, a release candidate RSQLite 0.4-17 is available here: http://bioconductor.org/packages/misc/ This version uses prepared queries to implement dbWriteTable. This should resolve the '\r' issue on Windows and should also be considerably more efficient. Soren, can you give this one a try and let me know if it works for you? Recent work on RSQLite has focused on integrating SQLite3's type system into the interface. We now rely on the column type in the DB when retrieving results. Previously, type.convert was used. I'm fairly certain these changes will result in changes in behavior -- in most cases, I think the changes are for the better. + seth __ 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.
Re: [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 <[EMAIL PROTECTED]> 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@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, [EMAIL PROTECTED] > 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, UKFax: +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. > > > -- Ronggui Huang Department of Sociology Fudan University, Shanghai, China 黄荣贵 复旦大学社会学系 __ 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.
Re: [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@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, [EMAIL PROTECTED] 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, UKFax: +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.
Re: [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 __ 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.