database 如何将 Microsoft Access .accdb 数据库文件中的数据读入 R？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7109844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read data from Microsoft Access .accdb database files into R?
提问by Egon Willighagen
The RODBCdocumentation suggests it is possible, but I am not sure how to read data from a Microsoft Access (the new .accdbformat) file with this package into R (on Debian GNU/Linux). The vignette talks about drivers, but I do not quite understand how I can see which drivers are installed, and in particular, if I have a driver installed for me to access those .accdbfiles.
该RODBC文件表明，它是可能的，但我不知道如何从Microsoft Access（新读取数据.accdb格式）文件，该封装成R（在Debian GNU / Linux的）。小插图讨论了驱动程序，但我不太明白如何查看安装了哪些驱动程序，特别是如果我安装了驱动程序来访问这些.accdb文件。
What code do you use to read data from .accdbfiles? And please indicate what platform you are on and if you had to install a special driver.
您使用什么代码从.accdb文件中读取数据？并请说明您在哪个平台上以及是否必须安装特殊驱动程序。
采纳答案by HansUp
The title of the page you linked, RODBC: ODBC Database Access, may be misleading. Access doesn't mean MS Access; in that title access means connectivity. RODBC is an ODBC manager for R. It serves as the mediator to provide communication between R and the ODBC driver for your target database. So for GNU/Linux, you would still need an ODBC driver for MS Access database files ... RODBC doesn't provide one.
您链接的页面的标题RODBC: ODBC Database Access可能具有误导性。Access 并不意味着 MS Access；在该标题中，访问意味着连接。RODBC 是 R 的 ODBC 管理器。它充当中介，在 R 和目标数据库的 ODBC 驱动程序之间提供通信。因此，对于 GNU/Linux，您仍然需要一个用于 MS Access 数据库文件的 ODBC 驱动程序……RODBC 没有提供。
However, I don't know of any free (as in freedom and/or beer) MS Access ODBC drivers for Linux. Easysoft sells one, but it's not cheap. There may be offerings from other vendors, too; I haven't looked.
但是，我不知道有任何免费的（例如在自由和/或啤酒中）用于 Linux 的 MS Access ODBC 驱动程序。Easysoft 有卖一个，但并不便宜。也可能有来自其他供应商的产品；我没看过
It might be easier to use a Windows machine to export your ACCDB to a format R can use. Or run R on Windowsinstead of Linux.
使用 Windows 机器将 ACCDB 导出为 R 可以使用的格式可能更容易。或者在 Windows而不是 Linux上运行R。
回答by coip
To import a post-2007 Microsoft Access file (.accdb) into R, you can use the RODBCpackage.
要将 2007 年后的 Microsoft Access 文件 (.accdb) 导入 R，您可以使用该RODBC包。
For an .accdb file called "foo.accdb" with the following tables, "bar" and "bin", stored on the desktop of John Doe's computer:
对于存储在 John Doe 计算机桌面上的名为“foo.accdb”的 .accdb 文件，该文件包含下表“bar”和“bin”：
library(RODBC) #loads the RODBC package
dta <- odbcConnectAccess2007("C:/Users/JohnDoe/Desktop/foo.accdb") #specifies the file path
df1 <- sqlFetch(dta, "bar") #loads the table called 'bar' in the original Access file
df2 <- sqlFetch(dta, "bin") #loads the table called 'bin' in the original Access file
回答by Patrick
You'll need the drivers to connect Access to the ODBC interface. These should be on your system if you have Access installed. If not, download the Access Database Enginefrom Microsoft. Then create your data connection in ODBC (You may need to run the 32-bit c:\windows\sysWOW64\odbcad32.exeif running 64-bit Windows). Note that this method doesn't work on GNU/Linux. The runtimes are Windows only, as mentioned by @HansUp below.
您将需要驱动程序将 Access 连接到 ODBC 接口。如果您安装了 Access，这些应该在您的系统上。如果没有，请从 Microsoft下载Access 数据库引擎。然后在 ODBC 中创建您的数据连接（c:\windows\sysWOW64\odbcad32.exe如果运行 64 位 Windows，您可能需要运行 32位）。请注意，此方法不适用于 GNU/Linux。运行时仅适用于 Windows，如下面的 @HansUp 所述。
As for code, you'll probably start with odbcConnect(dsn, uid = "", pwd = "", ...), and the documentation can help with the details.
至于代码，您可能会从 开始odbcConnect(dsn, uid = "", pwd = "", ...)，文档可以提供详细信息。
回答by Dirk Eddelbuettel
ODBC is a bit of 'plug and pray' system connecting different bricks.
ODBC 是一种连接不同砖块的“即插即用”系统。
RODBC allow you to get something from an ODBC provider into R. What you still need is the (for lack of a better word) ODBC-exporting driver of the database system in question. Which you need on your OS --- so I think with the Access-into-Linux combination you are without luck. Windows-only.
RODBC 允许您从 ODBC 提供程序获取某些内容到 R 中。您仍然需要的是（因为缺少更好的词）所讨论的数据库系统的 ODBC 导出驱动程序。您的操作系统需要什么 --- 所以我认为使用 Access-into-Linux 组合您很不走运。仅限 Windows。
People have managed to access SQL Server using FreeTDS drivers (for the TDS protocol underlying Sybase and via an early license also MS-SQL) but it is usualluy a fight to get it going.
人们已经设法使用 FreeTDS 驱动程序访问 SQL Server（用于 Sybase 底层的 TDS 协议，并且通过早期的许可证也可以使用 MS-SQL），但要实现它通常是一场斗争。
回答by Rijin
The best method that worked for me
对我有用的最好方法
#Package
library(RODBC)
#Defining the path
datab<-file.path("Main_File.accdb")
channel<-odbcConnectAccess2007(datab)
#reading the individual files inside the Main
table<-sqlFetch(Channel,"File_1")
This will fetch data from the "File_1" inside the Main_File.
这将从 Main_File 中的“File_1”中获取数据。
But the above code did not support the UTF encoding.
但是上面的代码不支持UTF编码。
回答by user3251747
library(RODBC)
db<-file.path("student.accdb")
channel<-odbcConnectAccess2007(db)
data<-sqlFetch(channel,"stud")
data
ID Name M1 M2 M3 M4 M5 Result
1 7 Radha 85 65 92 50 62 Pass
2 8 Reka 75 85 96 75 85 Pass
回答by Henning Piezunka
An alternative to directly accessing it might be to facilitate the data export from MS Access. At least the most recent MS Access allows to save the various export steps. One can then simply run the export of various queries / tables fairly quickly.
直接访问它的替代方法可能是促进从 MS Access 导出数据。至少最近的 MS Access 允许保存各种导出步骤。然后，您可以相当快速地运行各种查询/表的导出。
I know this does not answer the question, but might be a workaround if you do not get RODBC to run.
我知道这不能回答问题，但如果您没有让 RODBC 运行，这可能是一种解决方法。
回答by user627661
My solution (the most simple that I found):
我的解决方案（我发现的最简单的）：
- install "Access Database Engine" from Micosoft
- configure the connection to the Access data base (mdb or accdb) in Windows Administrative Tools, using the ODBC 32b tool. I's also possible to use c:\windows\sysWOW64\odbcad32.exe
- run RStudio in 32b mode ; it can be fixed in RStudio settings (relaunch RStudio after any change)
- finally, the RODBC functions work successfully.
- 从 Micosoft 安装“Access 数据库引擎”
- 使用 ODBC 32b 工具在 Windows 管理工具中配置到 Access 数据库（mdb 或 accdb）的连接。我也可以使用 c:\windows\sysWOW64\odbcad32.exe
- 以 32b 模式运行 RStudio；它可以在 RStudio 设置中修复（在任何更改后重新启动 RStudio）
- 最后，RODBC 函数成功运行。
CAUTION: it works only in Windows, not in linux. Personnally I use Windows as a Virtual Box guest within Xubuntu.
注意：它仅适用于 Windows，不适用于 linux。我个人使用 Windows 作为 Xubuntu 中的 Virtual Box 来宾。
