Message-ID: <38b9f0350701041855h484adfdfj6dbfbd87820a2077@mail.gmail.com>
Date: 2007-01-05T02:55:36Z
From: ronggui
Subject: SQLite: When reading a table, a "\r" is padded onto the last column. Why?
In-Reply-To: <17821.47505.846011.878093@basebud.nulle.part>
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 at 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
???
????????