我正在使用sqlSave()将数据帧上传到sql server中的表.此数据框中有一个时间戳,我想将时间戳col映射到sqlserver中的datetime col.
我遇到两个问题
它将数据框的时间戳映射到浮点数.
2.它创建一个表,但没有数据上传,我收到一个错误.
以下是数据框的示例,mdf：
mdf <- structure(list(run = structure(c(1L,1L,1L),.Label = c("run_00","run_01","run_02","run_03","run_04"),class = "factor"),slot = structure(c(1L,.Label = c("slot 3","slot 4","slot 5","slot 6"),timestamp = structure(c(1320774563,1320774624,1320774686,1320774747,1320774809,1320774871),class = c("POSIXct","POSIXt"),tzone = ""),channel = structure(c(1L,.Label = c("och01","och02","och09","och10"),variable = structure(c(2L,2L,2L),.Label = c("num_blocks","num_collection","num_corr_0","num_corr_1","num_uncorr_srow","post_fec_err_rate","pre_fec_err_rate"),value = c(1,62,124,185,247,309)),.Names = c("run","slot","timestamp","channel","variable","value"),row.names = c(NA,6L),class = "data.frame")
> mdf
run slot timestamp channel variable value
1 run_00 slot 3 2011-11-08 12:49:23 och01 num_collection 1
2 run_00 slot 3 2011-11-08 12:50:24 och01 num_collection 62
3 run_00 slot 3 2011-11-08 12:51:26 och01 num_collection 124
4 run_00 slot 3 2011-11-08 12:52:27 och01 num_collection 185
5 run_00 slot 3 2011-11-08 12:53:29 och01 num_collection 247
6 run_00 slot 3 2011-11-08 12:54:31 och01 num_collection 309
当我尝试sqlSave到sql server数据库时,会发生什么…
> sqlSave(dbandle,mdf,tablename="mdf") Error in sqlSave(dbandle,tablename = "mdf") : [RODBC] Failed exec in Update 22018 0 [Microsoft][ODBC sql Server Driver]Invalid character value for cast specification
另外,当我查看表的数据类型时,我没有获取时间戳的“datetime”.为什么RODBC会将POSIXct时间表映射到除日期时间以外的任何内容,这是没有意义的.
[rownames] [varchar](255) NULL,[run] [varchar](255) NULL,[slot] [varchar](255) NULL,[timestamp] [float] NULL,[channel] [varchar](255) NULL,[variable] [varchar](255) NULL,[value] [float] NULL
如何解决这个问题？
解决方法
两个选项：
1)Lazy one：发生错误,将创建表,并在数据库中手动将列更改为datetime.下次会工作.
2)正确：使用varTypes
请注意,您的问题可以通过删除不必要的东西而被删除.除此之外,我可能不会在sql服务器中使用列名称时间戳,因为我看到混乱,因为内部时间戳数据类型是完全不同的.
library(RODBC)
mdf = data.frame(timestamp=as.POSIXct(Sys.time()))
varTypes = c(timestamp="datetime")
channel = odbcConnect("test")
sqlSave(channel,rownames=FALSE,append=TRUE,varTypes=varTypes)
close(channel)
