[R] importing excel-file

Alberto Monteiro albmont at centroin.com.br
Wed Apr 18 19:07:51 CEST 2007

Corinna Schmitt wrote:
> It is a quite stupid question but please help me. I am very 
> confuced. I am able to import normal txt ant mat-files to R but 
> unable to import .xls-file
I've tried two ways to import excel files, but none of them
seems perfect.

Method 1:
This method uses library RODBC. The way to import excel files is this:

  channel <- odbcConnectExcel("myfile.xls")
  tables <- sqlTables(channel)  # list the names of the spreadsheets
  name1 <- tables[1, "TABLE_NAME"]  # get the name of the 1st spreadsheet

  plan1 <- sqlFetch(channel, name1)  # this _should_ work, but it doesn't
# The reason is that somehow the names of the sheets are altered

  plan1 <- sqlFetch(channel, "sheet name") # this works 
# but you must type the exact name of the sheet

# the next line works, no matter what is name1 (taken from tables)
  plan1 <- sqlQuery(channel, sprintf("select * from [%s]", name1))

  odbcClose(channel)  # close it

This is not perfect. Some (most?) of the numerical fields in the 
spreadsheet are translated to NA and become meaningless.

Method 2:
This method uses library xlsReadWrite. You must know the index
of the spreadsheet that you want to load:

plan6 <- read.xls(filename, sheet = 6, colClasses="double")

This works in most cases.

> I do not understand the online help. Can please anyone send me the
> corresponding command lines?
help(help) # :-)

> The .xls-file is attached.
No, it's not.

> In my file we use commas for the decimal format (example: 0,712),
> changes might be needed.
I *think* this is an internal flag. If the numbers are numbers, then
this should be no problem. An excel spreadsheet in any language is
portable to other languages, even when the evil geniuses of M$ 
decided to localize function names so that, in Portuguese, we have
SENO instead of SIN and RAIZ instead of SQRT.

Alberto Monteiro

More information about the R-help mailing list