read xlsx file in r code example

Example 1: how to read excel file in r

library("readxl")
# xls files
my_data <- read_excel("my_file.xls")
# xlsx files
my_data <- read_excel("my_file.xlsx")

Example 2: read xlsx in r

require(xlsx)
read.xlsx("myfile.xlsx", sheetName = "Sheet1")
read.xlsx2("myfile.xlsx", sheetName = "Sheet1")

Example 3: read xlsx in r

require(RODBC)
conn = odbcConnectExcel("myfile.xlsx") # open a connection to the Excel file
sqlTables(conn)$TABLE_NAME # show all sheets
df = sqlFetch(conn, "Sheet1") # read a sheet
df = sqlQuery(conn, "select * from [Sheet1 $]") # read a sheet (alternative SQL sintax)
close(conn) # close the connection to the file

Example 4: read xlsx in r

require(xlsx)
coln = function(x) { # A function to see column numbers
  y = rbind(seq(1, ncol(x)))
  colnames(y) = colnames(x)
  rownames(y) = "col.number"
  return(y)
}
data = read.xlsx2("myfile.xlsx", 1) # open the file 
coln(data) # check the column numbers you want to have as factors
x = 3 # Say you want columns 1-3 as factors, the rest numeric
data = read.xlsx2("myfile.xlsx", 1, 
  colClasses = c(rep("character", x), rep("numeric", ncol(data)-x+1))
)