[R] How to read an excel data into R?

[Renaud Lancelot]

Ling Jin a écrit :
> Hi all,
> 
> Does anybody know the easiest way to import excel data into R? I copied 
> and pasted the excel data into a txt file, and tried read.table, but R 
> reported that
> 
> Error in read.table("data_support.txt", sep = " ", header = T) :
>          more columns than column names
> 
> Thanks!
> 
> Ling
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
> 

Here is a function from a not-yet-released package written by a 
colleague and I, based on package RODBC written by Pr Ripley. The idea 
is to wrap - in the same function, GUI (suite of pop-up windows) and 
command-line facilities. It allows the importation of MS Excel and MS 
Access sheet or tables (within databases), and dBase-like files.

It is a preliminary, unoptimized version. Suggestions for improvements 
and bug reports are welcome.

Let me know if you want the packaged version (with help file).

Best,

Renaud

####

query <- function(tab = NULL, db = NULL, query = "all"){
# load the RODBC package and stops the program if not available
   if(!require(RODBC))
     stop("This function requires the RODBC package.\n")
# close all databases in case of error
   on.exit(odbcCloseAll())
## name of the database is not provided
   if(is.null(db)){
     Databases <- matrix(c("MS Access database (*.mdb)", "*.mdb",
                           "MS Excel file (*.xls)",      "*.xls",
                           "dBase-like file (*.dbf)",    "*.dbf"), nrow 
= 3, byrow = TRUE)
     File <- choose.files(filters = Databases, multi = FALSE, caption = 
"Select a database")
     sop <- match(".", rev(strsplit(File, NULL)[[1]]))[1]
     ext <- tolower(substring(File, nchar(File) - sop + 2, nchar(File)))
     channel <- switch(EXPR = ext,
                       xls = odbcConnectExcel(File),
                       mdb = odbcConnectAccess(File),
                       dbf = odbcConnectDbase(File))
# For Excel and Access cases, need to select a particular sheet or table
     if(ext != "dbf"){
       # sheet or table name is not provided
       if(is.null(tab)){
         tabdat <- sqlTables(channel)
         names(tabdat) <- tolower(names(tabdat))
         if(ext == "mdb")
           tabdat <- tabdat[tabdat$table_type == "TABLE", 3]
         if(ext == "xls"){
           tabname <- tabdat$table_name
           namfil <- tabdat[substring(tabname, nchar(tabname), 
nchar(tabname)) == "$", 3]
           tabdat <- substring(namfil, 1, nchar(namfil) - 1)
           }
         fil <- select.list(sort(tabdat))
         if(length(fil) == 0)
           stop("No file was selected.")
         if(ext == "xls")
           fil <- paste("[", fil, "$]", sep = "")
         }
       else
       # sheet or table name is provided
         fil <- if(ext != "xls") tab else paste("[", tab, "$]", sep = "")
       }
     else{
# dBase file
       sop <- match(".", rev(strsplit(File, NULL)[[1]]))[1]
       root <- tolower(substring(File, 1, nchar(File) - sop))
       revstr <- rev(strsplit(root, NULL)[[1]])
       sop <- if(is.na(match(c("/", "\\"), revstr)[1])) length(revstr) 
else match(c("/", "\\"), revstr)[1] - 1
       toor <- revstr[seq(sop)]
       fil <- paste(rev(toor), collapse = "")
       }
     }

## name of the database is provided
   else{
     sop <- match(".", rev(strsplit(db, NULL)[[1]]))[1]
     if(is.na(sop))
       stop("You must provide the full path and the extension for the 
database.\n")
     else{
       ext <- tolower(substring(db, nchar(db) - sop + 2, nchar(db)))
       channel <- switch(EXPR = ext,
                         xls = odbcConnectExcel(db),
                         mdb = odbcConnectAccess(db),
                         dbf = odbcConnectDbase(db),
                         stop("query not yet implemented for databases 
of format .", ext, "\n"))
# dBase file
     if(ext == "dbf"){
       sop <- match(".", rev(strsplit(db, NULL)[[1]]))[1]
       root <- tolower(substring(db, 1, nchar(db) - sop))
       revstr <- rev(strsplit(root, NULL)[[1]])
       sop <- if(is.na(match(c("/", "\\"), revstr)[1])) length(revstr) 
else match(c("/", "\\"), revstr)[1] - 1
       toor <- revstr[seq(sop)]
       fil <- paste(rev(toor), collapse = "")
       }
     else{
# name of the table is not provided (Excel or Access)
       if(is.null(tab)){
         tabdat <- sqlTables(channel)
         names(tabdat) <- tolower(names(tabdat))
         if(ext == "mdb")
           tabdat <- tabdat[tabdat$table_type == "TABLE", 3]
         if(ext == "xls"){
           tabname <- tabdat$table_name
           namfil <- tabdat[substring(tabname, nchar(tabname), 
nchar(tabname)) == "$", 3]
           tabdat <- substring(namfil, 1, nchar(namfil) - 1)
           }
         fil <- select.list(sort(tabdat))
         if(length(fil) == 0)
           stop("No file was selected.")
         if(ext == "xls")
           fil <- paste("[", fil, "$]", sep = "")
         }
       else
         fil <- if(ext != "xls") tab else paste("[", tab, "$]", sep = "")
       }
     }
   }
# retrieve the data
   if(query == "all")
     dat <- sqlQuery(channel = channel, query = paste("select * from", fil))
   else
     dat <- sqlQuery(channel = channel, query = query)
   odbcCloseAll()
   dat
   }



-- 
Dr Renaud Lancelot, vétérinaire
Projet FSP régional épidémiologie vétérinaire
C/0 Ambassade de France - SCAC
BP 834 Antananarivo 101 - Madagascar

e-mail: renaud.lancelot at cirad.fr
tel.:   +261 32 40 165 53 (cell)
         +261 20 22 665 36 ext. 225 (work)
         +261 20 22 494 37 (home)