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@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: [EMAIL PROTECTED]
tel.: +261 32 40 165 53 (cell)
+261 20 22 665 36 ext. 225 (work)
+261 20 22 494 37 (home)
______________________________________________
R-help@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
