Reading excel sheets into R (under windows)

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)

Advertisements
This entry was posted in R. Bookmark the permalink.

5 Responses to Reading excel sheets into R (under windows)

  1. I use python to massage and load into a mysql database. Then manipulate from there in R.

    • Ricardo says:

      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?

      • 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. Manny C says:

    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.

please comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s