所以我目前正在处理一个连接到Access数据库的问题。我可以连接到位于本地系统上的Access DB。这实际上已连接到SharePoint列表。我很想通过R和Access组合来自动化处理此SharePoint列表的过程!我想做的实际上是非常基本的,我想通过.csv引入新数据,对相关内容进行处理,然后与当前的Access DB进行比较,最后将新信息从r上传到Access。
我了解到,您需要将Windows操作系统的bit版本、Office版本和R版本配对。所以我是x64以上所有。这使我能够连接到Access DB。您还需要"Microsoft Access Database Engine 2016 Redistributable",它本质上是连接的驱动程序。
到目前为止,我所拥有的是:
library(odbc)
library(DBI)
file_path <- "C:/user/Documents/R Projects/...pathtofile.../filename.accdb"
accdb_con <- dbConnect(drv = odbc(), .connection_string = paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",file_path,";"))
access.db <- dbReadTable(accdb_con, "sNPS Deep Dives")
现在连接起来了!然后我在.csv中读取了新信息
new.df <- read.csv("C:/user/Documents/R projects/...pathtofile.csv", header=T, stringsAsFactors=FALSE, na.strings=c("","NA"))
数据集的一个例子可能看起来像这样:
date <- c("15/10/2018","15/10/2018", "16/10/2018", "12/11/2018", "07/09/2018")
score <- c("6", "10", "7", "10", "9")
group <- c("a","b", "b", "a", "b")
CaseID <- c("301", "302", "303", "304", "305")
new.df <- data.frame(date,score,group,CaseID)
new.df$date <- as.character(new.df$date)
new.df$score <- as.numeric(new.df$score)
new.df$group <- as.character(new.df$group)
new.df$CaseID <- as.numeric(new.df$CaseID)
值得注意的是,Access DB中有更多的列,人们将手动填写这些列以获得更多信息。
并且我处理它以准备进入Access DB。
probably not that interesting...
然后我将新数据与Access DB进行比较,如下所示:
library(dplyr)
new <- anti_join(new.df, access.db, by= "Case.ID")
现在我尝试了:
dbWriteTable(access.db.copy, new, append = TRUE)
dbAppendTable(access.db.copy, new)
我似乎无法让这个去任何地方
我得到一个错误:
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"ACCESS", "data.frame", "missing"’
我看到过很多人在连接Access数据库时遇到问题的帖子,但我没有看到任何关于将新数据写入该数据库的内容。
我知道这不是一个完全可复制的例子,但这似乎是一个很难重新创建的问题,因为这是不同工具之间的连接问题。我很乐意提供可能使更容易实现的示例集
如果你们能提供任何指导,我将不胜感激。
谢谢!
编辑:
看来孙是对的,我错过了一场争论。因此,我们似乎需要更像的东西
dbWriteTable(access.db.copy, "Name of table",new, append = TRUE)
哪个产生错误:
Error in result_insert_dataframe(rs@ptr, values) :
nanodbc/nanodbc.cpp:1944: HY104: [Microsoft][ODBC Microsoft Access Driver]Invalid precision value
我想知道这是否是Access关于文件类型的错误?
现在,如果我使用append,我不会得到错误,我会得到输出的0
dbAppendTable(access.db.copy, "Name of table", new, append= TRUE)
带输出:
[1] 0
但当我检查Access文件时,没有看到任何新值。
我知道这是几年后的事了,但希望这能帮助其他人解决这个问题,因为你是对的。
我在处理R和MS Access时多次遇到这个问题。我提出的解决方案非常"完美";"黑客";但它完成了我们要做的事情。。。只是不是很有说服力。
我这样做的方法是使用ROBC和DBI包的组合。
首先,我用ROBC打开一个到数据库的连接,并使用该连接将我的数据作为中间表写入数据库:
chan <- RODBC::odbcDriverConnection(connection = "/path/to/database.accdb")
RODBC::sqlSave(channel = chan,
dat = df,
tablename = "tbl_intermediary",
rownames = FALSE,
append = FALSE)
RODBC::odbcClose(chan)
rm(chan)
确保关闭ROBC连接,我也会很好地销毁它,因为为什么不呢?我将ROBC用于中间表,因为它支持批处理插入语句。我知道,理论上,用DBI::dbAppendTable()
的DBI也可以做同样的事情(但如果我们希望的那样,我们就不会出现在这个帖子上(。我在之前的SO问题中尝试过这个,但它没有解决我的问题。我也不知道我的中介桌子将来会有多大。希望当它们变得太大时,我们将处于不同的DBMS中。
接下来,我重新打开连接,这次是使用DBI,并向DB发送一条语句,将这些数据从中间表写入这些数据的最终存放位置,然后删除中间表。
con <- DBI::dbConnect(odbc::odbc(), .connection_string = "/path/to/database.accdb")
DBI::dbSendStatement(
conn = con,
statement = 'UPDATE
tbl_intermediary INNER JOIN final_tbl ON tbl_intermediary.SampleID = final_tbl.sampleNumber
SET
final_tbl.field1 = [tbl_intermediary].[field1],
final_tbl.notes = IIf(Nz([tbl_intermediary].[Notes],"")="",[final_tbl].[notes],[final_tbl].[notes] & "; Newest Notes: " & [tbl_intermediary].[Notes]);'
)
DBI::dbSendStatement(
conn = con,
statement = 'DROP TABLE tbl_intermediary;'
DBI::dbDisconnect(con)
rm(con)
)
我选择这种方法的主要原因是,我在Access中使用的一些SQL中也包含一些VBA。当我使用ROBC发送SQL-VBA混合字符串时,我会在IIF((和Nz((函数中收到各种错误(见上面的示例(。在ROBCCRAN文档中,sqlQuery()
函数的query
参数被严格假设为有效的SQL语句。因此,ROBC不知道如何解释IIf((和Nz((MS Access函数。我认为这也与ODBC驱动程序如何处理通信有关(如果我错了,请有人纠正我(。
然而,据我所知,DBI::dbSendStatment()
允许您使用的数据库引擎解释如何使用您提供的statement
参数。在上述情况下,VBA的执行方式与直接在Access中运行时的预期完全相同。根据DBI文档,对于交互式使用,您通常希望使用dbGetQuery
或dbExecute
。