Skip to content

Read *.xlsx data file into R

8 messages · Steven Stoline, Christopher W. Ryan, David Pell +4 more

Original
Steven Stoline
# 
Dear All:

I am having trouble reading an  *.xlsx data file into R.

I tried to install the packages XLSX and readx1, but still did not work.

Any helps will be appreciated.

with many thanks
sreve
Christopher W. Ryan
# 
I have just recently been struggling with this myself.  It seems a lot
depends on how the xlsx file was structured by its creator. As you know,
Excel does many things behind the scenes "for your convenience."

I had some success with the openxlsx package.

--Chris

Christopher W. Ryan, MD, MS
cryanatbinghamtondotedu
Medical Director, Broome County Health Department
https://www.linkedin.com/in/ryancw

Early success is a terrible teacher. You?re essentially being rewarded
for a lack of preparation, so when you find yourself in a situation
where you must prepare, you can?t do it. You don?t know how.
--Chris Hadfield, An Astronaut's Guide to Life on Earth
Steven Stoline wrote:
Randall Pruim
# 
Try readxl

I?ve found that that package works very well.
David Pell
# 
Difficult to know what's going on without an error message or an example.

Are you specifying which sheet within the workbook you are using?

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

Alternatively you could save the xslx as a csv then use the read.table

df = read.table("myfile.csv", header = TRUE)

David
_______________________________________________
R-sig-teaching at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-sig-teaching
________________________________


This email may have a protective marking please see http://www.mrc.ac.uk/about/information-standards/document-marking-policy/ <http://www.mrc.ac.uk/about/information-standards/document-marking-policy/>

This email and any attachments are intended for the named addressee(s) only. If you are not the intended recipient, please notify MRC HNR immediately and do not disclose, distribute, or retain this email or any part of it. While MRC HNR has taken every reasonable precaution to minimise risk of this e-mail or any attachments containing viruses, the recipient should carry out its own virus checks before opening the attachments. MRC HNR does not accept any liability for any losses or damages which the recipient may sustain due to presence of any viruses. MRC HNR reserves the right to monitor all e-mail communications through their networks for the purposes of preventing and detecting crime.
---------------------------------------------------------------------------------------
 This email has been scanned for email related threats and delivered safely by Mimecast.
 For more information please visit http://www.mimecast.com
---------------------------------------------------------------------------------------
Timothy Bates
# 
your question is vague.

For those who need to read xlsx files, I?ve been having great results with the ?gdata? package, and it?s cross platform.

# simple at its easiest
df <- gdata::read.xls(xls = ?~/Desktop/194.xlsx?)

# and powerful options also:

df <- gdata::read.xls(xls = ,sheet=,pattern=,na.strings=,method=,perl=)

tim
Randall Pruim
# 
But be warned that depending on the kinds of data you have, exporting to CSV and then reading in the CSV may or may not give you exactly what you are expecting.  So it is safer to use a tool designed to read excel files directly if (a) you can make it work and (b) the tool you are using is good at dealing with excel data issues.  (I?m not familiar with xslx, so I can?t comment on how well it works or what might be causing you problems in your case.)

Also, I would use either read.csv() or readr::read_csv() to read a csv file if you do go that route.
On Sep 23, 2015, at 10:15 AM, David Pell <David.Pell at mrc-hnr.cam.ac.uk<mailto:David.Pell at mrc-hnr.cam.ac.uk>> wrote:
Alternatively you could save the xslx as a csv then use the read.table

df = read.table("myfile.csv", header = TRUE)

David
Paula Grafton Young
# 
Can you please post the error message? Also, please indicate your platform.
I have had students using Macs who were unable to use the xlxs package.

As previously posted, saving the sheet (not a whole workbook) as a CSV file
is better--cross-platform, and about  one-tenth the file size of an *.xlxs
workbook with just one sheet.

MyData = read.table ("fileName.csv", header =T)
On Sep 23, 2015 10:03 AM, "Steven Stoline" <sstoline at gmail.com> wrote:

            

      
      
      
    

        

      
      
    

        

  
  


  


      

    

    

      
      

        


  

        

      Hadley Wickham
    

    

      
      #
    

  


  

      
Note that gdata::read.xls requires perl, which can be a hassle to install.
Hadley

        
On Wed, Sep 23, 2015 at 9:22 AM, Tim Bates <timothy.c.bates at gmail.com> wrote: