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

[ronggui]

dbWriteTable from a data fram with NA works fine.
But importing data frame txt file has problem.
## txt file begins
a	b
1	2
3	
1	3
 	5
1	2
2	
1	2
## txt file ends

> library(RSQLite)
> con <- dbConnect("SQLite", dbname = "c:/test.db3")
> dbWriteTable(con, "test","c:/test.txt",sep="\t",eol="\r\n",over=T,head=T)
[1] TRUE
> dbGetQuery(con,"select * from test")
  a b
1 1 2
2 3 0
3 1 3
4 0 5
5 1 2
6 2 0
7 1 2
> dat <- read.table("c:/test.txt",sep="\t",head=T)
> dbWriteTable(con, "test2",dat,over=T)
[1] TRUE
> dbGetQuery(con,"select * from test2")
  row_names  a  b
1         1  1  2
2         2  3 NA
3         3  1  3
4         4 NA  5
5         5  1  2
6         6  2 NA
7         7  1  2
>


2007/1/5, Dirk Eddelbuettel <edd using debian.org>:
>
> On 5 January 2007 at 10:02, ronggui wrote:
> | I think there is still one more thins need to do. RSQLite does not
> | take care of the "NA" (my case: na.strings is  Blank fields in the
> | test.txt file ) when import from a file to db table.
> |
> | > library(RSQLite)
> | Loading required package: DBI
> | Warning message:
> | package 'RSQLite' was built under R version 2.4.1
> | > con <- dbConnect("SQLite", dbname = "c:/test.db3")
> | > dbWriteTable(con, "test2","c:/test.txt",sep="\t",eol="\r\n",over=T,head=T)
> | [1] TRUE
> | > rs <- dbSendQuery(con, "select * from test2")
> | > fetch(rs)
> |   a b
> | 1 1 2
> | 2 3 0
> | 3 1 3
> | 4 0 5
> | 5 1 2
> | 6 2 0
> | 7 1 2
> | > read.table("c:/test.txt",sep="\t",head=T)
> |    a  b
> | 1  1  2
> | 2  3 NA
> | 3  1  3
> | 4 NA  5
> | 5  1  2
> | 6  2 NA
> | 7  1  2
> | > dat=read.table("c:/test.txt",sep="\t",head=T)
> | > dat
> |    a  b
> | 1  1  2
> | 2  3 NA
> | 3  1  3
> | 4 NA  5
> | 5  1  2
> | 6  2 NA
> | 7  1  2
>
> I can't reproduce that. Works for me even with earlier RSQLite versions:
>
> > library(RSQLite)
> Loading required package: DBI
> > con <- dbConnect("SQLite", dbname = "/tmp/test.sqlite")
> > test2<-data.frame(a=runif(10)*10, b=runif(10)*10)
> > test2[c(2,4,7),2] <- NA
> > test2
>            a        b
> 1  2.4896019 2.028301
> 2  8.6978219       NA
> 3  0.4284225 8.272148
> 4  7.3035881       NA
> 5  4.8419792 9.690563
> 6  1.2404994 8.112080
> 7  7.6554241       NA
> 8  6.6188917 1.084093
> 9  5.9969942 4.659705
> 10 0.2409261 6.286225
> > dbWriteTable(con, "test2", test2)
> [1] TRUE
> > rs <- dbSendQuery(con, "select * from test2")
> > fetch(rs)
>    row_names         a        b
> 1          1 2.4896019 2.028301
> 2          2 8.6978219       NA
> 3          3 0.4284225 8.272148
> 4          4 7.3035881       NA
> 5          5 4.8419792 9.690563
> 6          6 1.2404994 8.112080
> 7          7 7.6554241       NA
> 8          8 6.6188917 1.084093
> 9          9 5.9969942 4.659705
> 10        10 0.2409261 6.286225
> >
>
> Dirk
>
> --
> Hell, there are no rules here - we're trying to accomplish something.
>                                                   -- Thomas A. Edison
>


-- 
Ronggui Huang
Department of Sociology
Fudan University, Shanghai, China
黄荣贵
复旦大学社会学系