How to import excel data pro R?

Question

I have a table in excel that I need to import to R. I could do it quietly with the function read.csv when the table in question was online, but I’m having difficulty importing the file directly from my computer.

As the code I’m making will be sent to someone else, I can’t put an absolute path, and I’m having trouble applying the function to a relative path.

Answer 1

There are a few ways to do this. I couldn’t understand the situation very well, but here are some options to read excel files:

With the package gdata:

require(gdata)

df = read.xls ("myfile.xlsx"), sheet = 1, header = TRUE)

With the RODBC package`` :

require(RODBC)

conn = odbcConnectExcel("myfile.xlsx") # Abre uma conexao com o arquivo excel

sqlTables(conn)$TABLE_NAME # mostra todas planilhas

df = sqlFetch(conn, "Sheet1") # le uma planilha

close(conn) # fecha a conexao com o arquivo

With the package xlsx:

require(xlsx)

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

If you don’t have a problem with English, even more other methods can be seen in: http://www.r-bloggers.com/read-excel-files-from-r/

Answer 2

Currently, the best way to import from excel is by using the package readxl. Unlike the package xlsx, it does not depend on the rJava, which makes it more portable and easy to install.

You can install the package using:

install.packages("readxl")

Examples of use:

library(readxl)

# Abre tanto arquivos xls como xlsx
read_excel("my-old-spreadsheet.xls")
read_excel("my-new-spreadsheet.xlsx")

# É possível especificar a aba tanto por um número quanto pelo nome
read_excel("my-spreadsheet.xls", sheet = "data")
read_excel("my-spreadsheet.xls", sheet = 2)

# Se valores omissos (NA's) forem representados por outro
# valor que não seja uma célula vazia.
read_excel("my-spreadsheet.xls", na = "NA")

Answer 3

Well, about the excerpt:

"As the code I’m making will be sent to someone else, I can’t put an absolute path, and I’m having trouble applying the function to a relative path."

We can know what to put in the way (path) through the use of two functions:

getwd()

and

setwd()

getwd is similar to the command pwd in the Linux system terminal and, returns the path of the current directory, in which you are. Exemplifying:

getwd()

returns something like:

/home/seunome/Documents/meu.xlsx

setwd is similar to the command cd on the Linux terminal and, changes the current directory to what you need. Let’s assume that the file you want to import is in the folder Downloads, you can do this:

setwd(dir = "/home/seunome/Downloads")

And change the working directory to the folder Downloads:

Now, you can import your file from this folder with this command:

library(rio)

lista_1 <- import_list(file = "home/seunome/Downloads/meuarquivo.xlsx")

Where "home/seunome/Downloads/meuarquivo.xlsx" is the path of your Excel file.

This package (rio) is very easy to understand, and the advantage of learning it compared to what was mentioned in the previous answers is to try to standardize the import of data in many other formats, not only in Excel, but also csv for example.

More details about this package you can find here.