## Tuesday, January 4, 2011

### 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:

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

> 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()