R

Reading excel sheets into R (under windows)

Posted by

I love R, but couldn’t live without excel — in part as it’s the form in which so much data is shared.

Initially, I was happy saving data in .csv or .txt form, however as i use R more, that’s gotten tedious.

It turns out that reading data directly into R (on windows) isn’t all that hard – once you’ve installed the package RODBC.

Here’s how I do it:


require(RODBC)

conexcel <- odbcConnectExcel(xls.file="C:/folder/data/myfile.xls")
s1 <- "Sheet1"
s2 <- "Sheet2"

dd <- sqlFetch(conexcel, s1)
ddd <- sqlFetch(conexcel, s2)
odbcClose(conexcel)

If you are using excel 2007, you will need to call the odbcConnectExcel2007 function:


require(RODBC)

conexcel07 <- odbcConnectExcel2007(xls.file="C:/folder/data/myfile.xlsx")
s1 <- "Sheet1"
s2 <- "Sheet2"

dd07 <- sqlFetch(conexcel, s1)
ddd07 <- sqlFetch(conexcel, s1)
odbcClose(conexcel)

5 comments

    1. i’ve not explored using python to download and manage excel files – is it difficult? can you link to a few ‘how to’ posts — or post one on your very good blog?

      1. Post me your email address and I will happily send you my python code. It is a lot more complex than your appoach – but it also has all of the data in one place that I can mix and match – and it deals with NAs and the like pretty well. But given the complexity, not sure that it is better – or indeed good enough to blog on.

  2. For smaller things, and on an ad-hoc basis, you can use:
    write.table(object_to_paste_to_clipboard, file="clipboard", sep="\t")

    This isn’t really documented anywhere.

Comments are closed.