Skip to content

How to import an excel data file

7 messages · David Winsemius, Maria Lathouri, Richard O'Keefe +4 more

David Winsemius
# 
I know I should save it as a .csv file, which I have done.
I?m told I should use the read_excel() function from the readxl package.
My question is, how do I express the location of the file.  The file is named KurtzData.csv.
Its location in my Mac files is DFPfiles/ae/FriendsMonroe/KurtzData.csv
How exactly---What ?, etc.---do I type with its name in the read_excel() function?
It?s been a long time since I?ve used R.
Thanks for any help.
Maria Lathouri
# 
Dear David,?

I also use excel files for R. This is what I do
install.packages("readxl")

library(readxl)
mydata=read_excel('mydata.xlsx')

Hope this helps.?

Best,
Maria




???? ??????? 23 ??????????? 2023 ???? 09:23:43 ?.?. GMT+1, ? ??????? Parkhurst, David <parkhurs at indiana.edu> ??????: 





I know I should save it as a .csv file, which I have done.
I?m told I should use the read_excel() function from the readxl package.
My question is, how do I express the location of the file.? The file is named KurtzData.csv.
Its location in my Mac files is DFPfiles/ae/FriendsMonroe/KurtzData.csv
How exactly---What ?, etc.---do I type with its name in the read_excel() function?
It?s been a long time since I?ve used R.
Thanks for any help.






??? [[alternative HTML version deleted]]

______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Richard O'Keefe
# 
Are you sure that read.csv() can't read your data?
[If you depend on extra packages you might want to consider using
 the 'groundhog' package so that you can be more confident of
 reproducing your work in a year or two.]

You said that your question is how you should write the name of
the file in your call to read.<whatever>.
...
file      the name of the file which the data are to be read from.
          ... If it
          does not contain an _absolute_ path, the file name is
          _relative_ to the current working directory, 'getwd()'.
          Tilde-expansion is performed where supported.

"Tilde-expansion" refers to a common UNIX convention of replacing
~/ at the beginning of a file name by $HOME/ where $HOME is your
home directory, and ~bongiwe/ by $HERS/ where $HERS holds the
absolute name of user bongiwe's home directory.  This is actually
a shell convention, not an operating system kernel convention, but
R is telling us "I can do that too".  So for example to refer to
one of my data files I might do
   jan17 <- read.csv("~/Data/Weather/2017-Jan.dat")
"DFPfiles/ae/FriendsMonroe/KurtzData.csv" is NOT the location of
the file on your system.  It is PART of the location.  We can be
sure of that because it does not begin with a slash.  So it is not
an absolute file name.  There is some value $DIR such that
$DIR is an absolute file name and it names a directory and
"$DIR/DFPfiles/ae/FriendsMonroe/KurtzData.csv" really IS the location of
the file on your system.

If the current working directory is
(1) $DIR/DFPfiles/ae/FriendsMonroe/   use "KurtzData.csv"
(2) $DIR/DFPfiles/ae/     use "FriendsMonroe/KurtzData.csv"
(3) $DIR/DFPfiles    use "ae/FriendsMonroe/KurtzData.csv"
(4) $DIR   use "DFPfiles/ae/FriendsMonroe/KurtzData.csv"
(5) $DIR/DFPfiles/ae/FriendsMonroe/SubDir use "../KurtzData.csv"
I suspect that $DIR may be your home directory, in which case
you can substitute ~ for $DIR.

Of course the file argument can be anything that *evaluates* to
a character string containing a file name (or it can be a URL or
it can be a 'text connection').

Following the advice in read.csv, you might want to
read the 'R Data Import/Export' manual.


On Sat, 23 Sept 2023 at 20:23, Parkhurst, David <parkhurs at indiana.edu>
wrote:

  
  


  


      

    

    

      
      

        


  

        

      Stevie Pederson
    

    

      
      #
    

  


  

      
Hi David,

If you're using RStudio, I'd encourage the use of R Projects, with a new
project for each analysis, even if you only do one every few years. That
would also correspond to a new directory for each analysis, which many also
find helpful. The package 'here' [install.packages("here")] then uses the
.Rproj file to set file paths within each project. You can place your data
somewhere within the parent directory of the project and use
here::here("relative/path/to/my_file.csv") to tell R where the file is. It
sounds like a trivial step, but it really can make referring to files on
disk like this much easier.

I'm not sure what the best parent folder would be for your analysis, but
assuming it's FriendsMonroe, you would start a new R Project in that
directory, keep all of your scripts in that directory, and refer to the
file using here::here("KurtzData.csv")

I do hope that helps,

Stevie

On Sat, 23 Sept 2023 at 17:53, Parkhurst, David <parkhurs at indiana.edu>
wrote:

            

      
      
      
    

        

      
      
    

        

  
  


  


      

    

    

      
      

        


  

        

      Ivan Krylov
    

    

      
      #
    

  


  

      
? Fri, 22 Sep 2023 23:10:58 +0000
"Parkhurst, David" <parkhurs at indiana.edu> ?????:

            

      
      
      
    

        
In RGui on Windows, file.choose() opens a dialog window letting the
user choose a file interactively. Do you get a similar prompt if you run
file.choose() in R.app? Choose the file, and it will return a string
that would be most certainly suitable for read.csv(), read_excel(), and
other functions that accept file paths.

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Duncan Murdoch
    

    

      
      #
    

  


  

      
On 23/09/2023 3:35 p.m., Ivan Krylov wrote:

            

      
      
      
    

        
file.choose() exists on all platforms.  choose.files() only exists on 
Windows.

Duncan Murdoch

  
  


  


      

    

    

      
      

        


  

        

      Rainer Hurling
    

    

      
      #
    

  


  

      
Dear David,

Am 23.09.23 um 01:10 schrieb Parkhurst, David:

            

      
      
      
    

        
Even though there are already some answers, I would like to comment on 
the question.

The advice to export the excel data to a csv file to import it 
afterwards from the readxl package into R using read_excel() is a bit 
around the corner. Instead, the readxl package can better read the Excel 
format directly, both the one as .xls format and the new .xlsx format.

I am basically a proponent for reading original data (here Excel) 
directly and not via an intermediate format like csv. This avoids, among 
other things, that subsequently supplemented or corrected original Excel 
data are not converted to csv by mistake and are thus not available the 
next time they are imported into R.

In order to take the path to the data into account when reading it with 
read_excel(), you can proceed as follows (analogous if your path is 
relativ to your R working directory), for example:

Kurtz <- readxl::read_excel(path = 
'/DFPfiles/ae/FriendsMonroe/KurtzData.xlsx', sheet = 
'name-or-number-of-the-sheet')


Additional parameters may be necessary, e.g. to specify whether to skip 
headers when reading the Excel data. The help page for the function 
gives valuable hints here:

?readxl::read_excel


Besides the R package readxl I have also had very good experiences 
reading and writing Excel files with the package openxlsx ;)

HTH,
Rainer Hurling