- Read Data from Files
- R can import data using many ways.
- Packages exists that handles import from software systems like
- Download and add package to R
- Open the R GUI;
- Click on the 'packages' tab;
- If it ask country(Select any country)
- Choose the package to install;
- Load the package into R with the library() function.
- library(gdata)
- To read data ,the function read.table() is useful ,observe help
- Some of the important arguments are
- header: Is the first line variable names or not?
- sep: What character is used to separate the columns?
- dec: What character is used as decimal separator?
- nrows: How many rows do we want to read?
- na.strings: What string represent a missing value?
- skip: How many lines to skip before start reading?
- Comment Char: What char in the beginning of a line should indicate that the line should be skipped?
- Exercise 1: Read data of .dat file and variables are separated with white space
- From labdata open File_white_space.dat and observe the data
- Read data by using read.table() function
- Exercise 2: Read data of .txt file , variables are separated with white space and skip first 5 rows then read only 2 rows
- From labdata open file file_skip_rows.txt and observe data
- Exercise 3:Read data of txt file with comment lines ,empty values and decimals available as ,
- From labdata open file file_comment.txt and observe data
- Read.table() is the main function .however Other functions which are useful for reading data frames from files quickly are
- read.csv() (defaults are header=TRUE,sep=”,”,dec=”.”)
- read.delim() for tab-delimited files((defaults are header =TRUE,sep="\t")
- read.fwf() fixed width format
- read.csv2() (defaults are header =TRUE,sep=";" and dec=",")
- read.delim2()
- Additional arguments are similar to those of read.table()
- The utils package, which is automatically loaded in your R session on startup, can import CSV files with the read.csv() function
- scan() can be a little tricky to use, but is very flexible.
- Its simplest use is
- Observe scan.txt file
- readlines() function
- observe readlines.txt file
- File connections
- File connections can open a file for reading different sections in different ways
- Read from excel
#read excel files
#download and install perl
library(gdata)
emp1 <- read.xls("scott_emp_data.xlsx")
emp1
|
- Reading Data from SQL Databases
- R can connect most of the available relational databases
- R users have a few choices of how to connect to their Oracle Database. The most commonly seen include: RJDBC,RODBC and ROracle
- Let us see how we can connect to oracle database
- To connect to oracle database ,mainly we have four steps
Connecting using RJDBC
- Step 1 of 4: Install RJDBC package
- Step 2 of 4:Download ojdbc6.jar file and point to r
- Download ojdbc6.jar file
- Copy and paste into R installation directory
- Point to jdbcdriver in r
- Step 3 of 4: Create connection to oracle database
- Step 4 of 4: Check SQL Commands
- dbReadTable: Read a table into a data frame
- dbGetQuery: read the result from a SQL statement to a data frame
- dbSendUpdate: execute SQL command
- dbWriteTable: write a data frame to the schema. It is typically very slow with large tables.
Connecting using RODBC
- Step 1 of 2: Create ODBC Connection
- Go to run → type odbc →open Microsoft ODBC Administrator
- Click on system DSN → add →provide as shown below
- Test connection → close it
- Step 2 of 2: access oracle database using RODBC
- Add library
- Connect database
- Query tables
- Query columns and properties
- Close connection
Connecting using Roracle
- Download and install ROracle_1.2-1.zip
install.packages('C:\\Program Files\\R\\ROracle_1.2-1.zip', repos = NULL)
library(ROracle)
|
- Execute all commands and observe results
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "scott", "tiger", dbname='demo.us.oracle.com:1521/orcl')
dbListTables(con)
dbReadTable(con, 'DEPT')
dbGetQuery(con,'select * from dept')
dbDisconnect(con)
|
