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

library(RSQLite)
con <- dbConnect("SQLite", dbname = "c:/test.db3")
dbWriteTable(con, "test","c:/test.txt",sep="\t",eol="\r\n",over=T,head=T)

dbGetQuery(con,"select * from test")

dat <- read.table("c:/test.txt",sep="\t",head=T)
dbWriteTable(con, "test2",dat,over=T)

dbGetQuery(con,"select * from test2")

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