RODBC Windows for Data base connection

it is a fantastic tool!

Install the Postgres-ODBC driver (or the odbc driver for mysql or or or ...)(you can find the windows installer package here) and the RODBC-package within R (install.packages(RODBC)). If you are behind a proxy do not forget to set the proxy BEFORE you try to install. It have to be set before any download code is used, otherwise you have to restart R. (Sys.setenv())


On Windows there is also the possibility to use an odbc connection to excel:

After installing the package RODBC, and loading the package (library(RODBC))

You can establish a connection to an Excel file with the following command:

> con<-odbcConnectExcel2007("path\to\file\exceltest.xlsx") # Excel2007

> con<-odbcConnectExcel("path\to\file\exceltest.xls") # Excel2003

If the connection was established successfully con[1]>0

Get informations about the connection

> odbcGetInfo(con)

# get an overview of the WorkSheets

> sqlTables(con)

# overview columns of a table

> sqlColumns(con2, "table1")
# "table1" is the name of the WorkSheet

# get whole sheets

> sheet1<-sqlFetch(con, "table") # the same like

> sheet1<-sqlQuery(con, "select * from [table$]")

# one column

> my.colsqlQuery(con, "select Var1 from [table1$]") # "Var1" is the name of the column

# closing the connection

> odbcClose(con)

# close all existing connections

> odbcCloseALL()

# The ODBC interface to Excel spreadsheets is read-only;

# you cannot alter the spreadsheet data

# But you can use all kinds of SQL-Query - and this is really great, it is a pity that the commands do not exist on Linux-OS