Importing and exporting Data

This blog originally appeared in http://gastrodatascience.com

There are a large number of file types that are able to store data. R is usually able to import most of them but there are some caveats. Below is a summary of methods I use for data imports using the most common file types.

It is worth saying that most datasets will come from excel or csv files. It is unusual to gain direct access to the database and these are the normal export types from most data storage systems.

Import csv or text

read.table("mydata.txt",header=T,stringsAsFActors=F) 

#or, and using tab as a delimiter:

read_delim("SomeText.txt", "\t",trim_ws = TRUE)

#Maybe get a csv off the internet:
tbl <- read.csv("http://www.example.com/download/data.csv")

To prevent strings being imported as factors, add the parameter stringsAsFActors=F

Import from excel

library(XLConnect)
wk = loadWorkbook("~Mydata.xlsx")
dfw = readWorksheet(wk, sheet="Sheet3",header=TRUE)

#Alternative and super friendly way
#For excel imports using readxl package:
library(readxl)
read_excel("~Mydata.xlsx")

Import from database

library(RODBC)
channel <- odbcConnect("MyDatabase", believeNRows=FALSE)
#Get one of the tables
tbl_PatientDetails<-sqlFetch(channel, "tblPtDetails")

Export to excel

library(XLConnect)
exc <- loadWorkbook("~Mydata.xls", create = TRUE)
createSheet(exc,'Input')
saveWorkbook(exc)
XLConnect::writeWorksheet(exc,mydata,sheet = "Input", startRow = 1, startCol = 2)

#Another way is:
library(xlsx)
write.xlsx(mydata, "c:/mydata.xlsx")

Export to csv or a tab delimited file

 write.csv(mydata, file="filename", row.names=FALSE)
 write.table(mydata, "c:/mydata.txt", sep="\t")

There are also many other file types that can be imported and exported but these are the most common so the most practical.