The openxlsx package uses Rcpp and, as it doesn’t depend on JAVA, it is an interesting alternative to to the readxl package to read an Excel file in R. Nonetheless, if you know the file extension you are going to read you can use the corresponding function of the following to avoid guessing: # If you know the extension of your Excel file Recall that the read_excel function guesses the file extension. If you want the output to be of class ame you will need to use the as.ame function as follows: data <- read_excel(file_path, skip = 1) However, you may have noticed that the output is of class tibble (a modern type of data frame). In addition, if you want to avoid reading the column names, you can set the col_names argument to FALSE: read_excel(file_path, col_names = FALSE) New names: read_excel(file_path, range = 'B1:B5') # A tibble: 4 x 1 In this case, the skip argument won’t be taken into account if you specify it. Note that you could also specify a range of cells to be selected with the range argument. Read_excel(file_path, skip = 1) # A tibble: 3 x 2 You can also skip rows with the skip argument of the function: # Skip first row Read_excel(file_path, sheet = 2) # Equivalent # A tibble: 2 x 4ġ (at birth) (office supply type) (date is approximate) (in grams) Read_excel(file_path, sheet = 'two-row-header') # Selecting the other sheet of the Excel file Note that, by default, the function loads the first Excel sheet. The sheet argument allows you to specify the sheet you want to load, passing its name or the corresponding number of the tab. # Get the path of a sample XLSX dataset of the package Once loaded, or once you have the path of your own Excel file, you can use the excel_sheets function to check the Excel file sheet names, if needed. The input arguments for these functions are same as that for read.xlsx() function.In order to load the path of the sample Excel file you can make use of the readxl_example function.

Gdata has several other functions to convert the Excel file into various other formats. >sample.data <- read.xlsx(“Sample_Sheet.xlsx, To avoid this error, another argument for the function can be given to search for the Perl executable file.

If it doesn’t find perl.exe, then R will return an error. While executing read.xls function, R will search for a path to the excel file and looks out for Perl on its way. This function converts the Sample_Sheet.xls file into a temporary. >sample_data = read.xls(“Sample_Sheet.xls”)//Read data from the sheet Take for example a sample Excel spreadsheet, named ‘Sample_Sheet.xls’ and to use this method, you would require Perl runtime in your system. The read.xls function in particular can read data from an Excel spreadsheet and gives data frame as output. gdata provides a cross platform solution for importing data from Excel files into R. Utlis package is one of the core packages which contains bunch of basic utility functions and the following commands are part of this package.Īpart from the xlsx package, we have gdata package, which has functions that can read from data in the Excel format. >getwd()//To get the working directory at the momentīefore we look into the packages available to extract data from Excel spreadsheet, we will show you simple R commands that can do the job. All missing data points in the Excel spreadsheet should be indicated with ‘NA’.īefore you import the Excel data in R, you would need to set the console in R to working directory.The names and header of the data scheme should usually avoid symbols.Concatenating words in the cells should be done using ‘.’.Similarly the first row is reserved for header, describing the scheme of the data. The first column of the spreadsheet is used to identify the sample dataset, therefore it should be a unique key id.At the end of this section, we have written about some common problems encountered while loading Excel files and spreadsheet data.īefore we import the data from Excel spreadsheet into R, there are some standard practices to tone your data, to avoid any unnecessary error. We will learn about various R packages and extensions to read and import Excel files. This tutorial will give a brief of reading, writing and manipulating the data in Excel files using R.

Excel is a spreadsheet application, which is widely used by many institutions to store data.

Rcode for importing excel file