Archive

Archive for the ‘R’ Category

Connect to SQL Server in R without ODBC DNS connection

September 25, 2015 3 comments

In the previous post, I have explained the steps to connect to SQL Server with ODBC DSN.

In this blog post, I am going to connect to SQL Server without ODBC DSN. But, will be using driver name instead.

Step 1: RODBC must be installed in R. Please read (Connect to SQL Server in R for detail)

Step 2: Load the RODBC library

library(RODBC)

Step 3:Connecting to SQL Server with Driver name

MSConnection <- odbcDriverConnect(“DRIVER=SQL SERVER;SERVER=.;Trusted_Connection=Yes”)

This connection point to the default database initially. But, we can run USE statement to change the database.

sqlQuery(MSConnection, “USE test”)

Step 4: Read the table data to a variable

mydata <- sqlQuery(MSConnection, “Select * from dates”)

Step 5: Display data by just typing the variable name in R command environment

mydata

Step 6: Close the connection

close(MSConnection)

RODBC documentation

Categories: R Tags: ,

Connect to SQL SERVER in R

September 20, 2015 1 comment

R is an OPEN SOURCE software environment for statistical computing and graphics. It is easy to use and create valuable data visualization in a minute. Learn more about R.

This blog post is to explain the steps to connect to SQL Server database in R and display simple graphics.

Over all steps

  1. Create ODBC Data Source
  2. Setup R environment to connect to SQL Server
  3. Query the SQL table, display data and graphics

Step 1: Create ODBC Data Source:

Goto, Control Panel -> Administrative Tools -> Data Sources (ODBC)

DSN1

Click Add button

dsn2

select SQL Server Native Client and click Finish

dsn3

Give data source name and SQL Server name to connect and Click Next

dsn4

Select an appropriate SQL Server authentication option and give valid credential. In this example, we are using Integrated Windows Authentication. Click next

dsn5

Select the database to work with and modify the application intent option to Read-only. These settings are for this exercise only. We may use default setting and click next.

dsn6

Click finish and TEST connection

dsn7

Step 2: Setup R environment to connect to SQL Server.  Please install R software.

Install RODBC package to R:

  • install.packages(“RODBC”)

Load the RODBC library to current environment to use:

  • library(RODBC)

Connect to SQL Server using ODBC connection:

  • wrpt <-odbcConnect(“weatherdata”)

We have just connected to SQL Server and the R connection variable name is wrpt. We can use this variable to query a table and more.

Step 3: Query the SQL table to display data and graphics

This statement loads the entire table “tblTemp” in to the variable weather.

weather <- sqlFetch(wrpt, “tblTemp”)  

This statement displays the entire table

print(weather)

This statement displays the summary of the data-set

summary(weather)

rdata

This statement displays a graph with daily max temperature for a  month

plot(weather$maxtemp, type=”l”)

rgraph

R manuals

Thanks for reading this post.

Categories: R Tags:
%d bloggers like this: