Dear all, I have a problem with the function read.xls from the gdata package, error message see below. Two examples: First, I try to read my data, which does not work; Secondly, I tried the example code/data with the Iris data, which worked Any idea? Thanks, Lars> path<-"I:/subProjects/bh/HPGD/" > > setwd(path) > > xls <- "Platten_Liste_090421.xls" > > xlsfile <- file.path(path,xls) > > file.exists(xlsfile)[1] TRUE> > > > oxl <- comCreateObject("Excel.Application") > > comSetProperty(oxl, "Visible", TRUE)NULL> > owb <- comGetProperty(oxl, "Workbooks") > > ob <- comInvoke(owb, "Open", xlsfile) > > osheets <- comGetProperty(ob, "Worksheets") > > n <- comGetProperty(osheets, "Count") > > ithSheetName <- function(i) comGetProperty(comGetProperty(osheets, "Item", i), "Name") > > sheetNames <- sapply(1:n, ithSheetName) > > #> sheetNames > # [1] "Platten gesamt" "Platte 1-5" "Platte 6-10" "Platte 11-15" "Platte 16-20" "Platte 21-25" > # [7] "Platte 26-30" "Platte 31-35" "Platte 36-42" "Replikate Platte 1-10" "Replikate Platte 11-20" "Replikate Platte 21-30" > #[13] "Replikate Platte 31-42" > > > comInvoke(oxl, "Quit")NULL> > read.xls(xlsfile, sheetNames[1], na.strings = c("na"," ","."),verbose=TRUE)Converting xls file "I:/subProjects/bh/HPGD//Platten_Liste_090421.xls" to csv file "C:\DOKUME~1\beckmann\LOKALE~1\Temp\RtmpCU2zBu\file4db74d06.csv" ... Executing perl C:/Programme/R/R-2.8.1/library/gdata/perl/xls2csv.pl "I:/subProjects/bh/HPGD//Platten_Liste_090421.xls" "C:\DOKUME~1\beckmann\LOKALE~1\Temp\RtmpCU2zBu\file4db74d06.csv" Platten gesamt ... Sheetnumber must be an integer larger than 0. at C:/Programme/R/R-2.8.1/library/gdata/perl/xls2csv.pl line 57. Done. Fehler in xls2csv(xls, sheet, verbose = verbose, ..., perl = perl) : Unable to read translated csv file 'C:\DOKUME~1\beckmann\LOKALE~1\Temp\RtmpCU2zBu\file4db74d06.csv'. Fehler in file.exists(tfn) : ung?ltiges 'file' Argument>> xlsfile <- file.path(.path.package('gdata'),'xls','iris.xls') > xlsfile[1] "C:/Programme/R/R-2.8.1/library/gdata/xls/iris.xls"> > iris <- read.xls(xlsfile)Converting xls file to csv file... Done. Reading csv file... Done.> head(iris) # look at the top few rowsSepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa 3 4.7 3.2 1.3 0.2 setosa 4 4.6 3.1 1.5 0.2 setosa 5 5.0 3.6 1.4 0.2 setosa 6 5.4 3.9 1.7 0.4 setosa>-- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser
The error message seems pretty clear here. It says that you have to supply an integer to specify which sheet you want -- not a sheet name. Also see ?read.xls which describes the sheet argument as (emphasis added): sheet ***number*** of sheet within the Excel file from which data are to be read On Tue, Aug 18, 2009 at 4:35 AM, Lars Beckmann<Lars.Beckmann at gmx.net> wrote:> Dear all, > > I have a problem with the function read.xls from the gdata package, error message see below. Two examples: > > First, I try to read my data, which does not work; > Secondly, I tried the example code/data with the Iris data, which worked > > Any idea? > > Thanks, > Lars > >> path<-"I:/subProjects/bh/HPGD/" >> >> setwd(path) >> >> xls <- "Platten_Liste_090421.xls" >> >> xlsfile <- file.path(path,xls) >> >> file.exists(xlsfile) > [1] TRUE >> >> >> >> oxl <- comCreateObject("Excel.Application") >> >> comSetProperty(oxl, "Visible", TRUE) > NULL >> >> owb <- comGetProperty(oxl, "Workbooks") >> >> ob <- comInvoke(owb, "Open", xlsfile) >> >> osheets <- comGetProperty(ob, "Worksheets") >> >> n <- comGetProperty(osheets, "Count") >> >> ithSheetName <- function(i) comGetProperty(comGetProperty(osheets, "Item", i), "Name") >> >> sheetNames <- sapply(1:n, ithSheetName) >> >> #> sheetNames >> # [1] "Platten gesamt" ? ? ? ? "Platte 1-5" ? ? ? ? ? ? "Platte 6-10" ? ? ? ? ? ?"Platte 11-15" ? ? ? ? ? "Platte 16-20" ? ? ? ? ? "Platte 21-25" >> # [7] "Platte 26-30" ? ? ? ? ? "Platte 31-35" ? ? ? ? ? "Platte 36-42" ? ? ? ? ? "Replikate Platte 1-10" ?"Replikate Platte 11-20" "Replikate Platte 21-30" >> #[13] "Replikate Platte 31-42" >> >> >> comInvoke(oxl, "Quit") > NULL >> >> read.xls(xlsfile, sheetNames[1], na.strings = c("na"," ","."),verbose=TRUE) > > Converting xls file > ? ?"I:/subProjects/bh/HPGD//Platten_Liste_090421.xls" > to csv file > ? ?"C:\DOKUME~1\beckmann\LOKALE~1\Temp\RtmpCU2zBu\file4db74d06.csv" > ... > > Executing ?perl C:/Programme/R/R-2.8.1/library/gdata/perl/xls2csv.pl "I:/subProjects/bh/HPGD//Platten_Liste_090421.xls" "C:\DOKUME~1\beckmann\LOKALE~1\Temp\RtmpCU2zBu\file4db74d06.csv" Platten gesamt ... > > Sheetnumber must be an integer larger than 0. at C:/Programme/R/R-2.8.1/library/gdata/perl/xls2csv.pl line 57. > Done. > > Fehler in xls2csv(xls, sheet, verbose = verbose, ..., perl = perl) : > ?Unable to read translated csv file 'C:\DOKUME~1\beckmann\LOKALE~1\Temp\RtmpCU2zBu\file4db74d06.csv'. > Fehler in file.exists(tfn) : ung?ltiges 'file' Argument >> > >> ?xlsfile <- file.path(.path.package('gdata'),'xls','iris.xls') >> ? ?xlsfile > [1] "C:/Programme/R/R-2.8.1/library/gdata/xls/iris.xls" >> >> ? ?iris <- read.xls(xlsfile) > Converting xls file to csv file... Done. > Reading csv file... Done. >> ? ?head(iris) ?# look at the top few rows > ?Sepal.Length Sepal.Width Petal.Length Petal.Width Species > 1 ? ? ? ? ?5.1 ? ? ? ? 3.5 ? ? ? ? ?1.4 ? ? ? ? 0.2 setosa > 2 ? ? ? ? ?4.9 ? ? ? ? 3.0 ? ? ? ? ?1.4 ? ? ? ? 0.2 setosa > 3 ? ? ? ? ?4.7 ? ? ? ? 3.2 ? ? ? ? ?1.3 ? ? ? ? 0.2 setosa > 4 ? ? ? ? ?4.6 ? ? ? ? 3.1 ? ? ? ? ?1.5 ? ? ? ? 0.2 setosa > 5 ? ? ? ? ?5.0 ? ? ? ? 3.6 ? ? ? ? ?1.4 ? ? ? ? 0.2 setosa > 6 ? ? ? ? ?5.4 ? ? ? ? 3.9 ? ? ? ? ?1.7 ? ? ? ? 0.4 setosa >> > > > > > > -- > Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - > sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >
Hi r-help-bounces at r-project.org napsal dne 18.08.2009 10:35:11:> Dear all, > > I have a problem with the function read.xls from the gdata package,error> message see below. Two examples: > > First, I try to read my data, which does not work; > Secondly, I tried the example code/data with the Iris data, which worked > > Any idea?I did not see any response yet and do not have much knowledge of read.xls experience but from the messages you sent it seems to me that there is some mismatch during converting your xls to csv and reading from it.> > read.xls(xlsfile, sheetNames[1], na.strings = c("na","","."),verbose=TRUE)> Sheetnumber must be an integer larger than 0. at C:/Programme/R/R-2.8.1/ > library/gdata/perl/xls2csv.pl line 57.Is it possible that read.xls requires not names as in sheetNames but plain numbers? Regards Petr> > Thanks, > Lars > > > path<-"I:/subProjects/bh/HPGD/" > > > > setwd(path) > > > > xls <- "Platten_Liste_090421.xls" > > > > xlsfile <- file.path(path,xls) > > > > file.exists(xlsfile) > [1] TRUE > > > > > > > > oxl <- comCreateObject("Excel.Application") > > > > comSetProperty(oxl, "Visible", TRUE) > NULL > > > > owb <- comGetProperty(oxl, "Workbooks") > > > > ob <- comInvoke(owb, "Open", xlsfile) > > > > osheets <- comGetProperty(ob, "Worksheets") > > > > n <- comGetProperty(osheets, "Count") > > > > ithSheetName <- function(i) comGetProperty(comGetProperty(osheets,"Item", i), "Name")> > > > sheetNames <- sapply(1:n, ithSheetName) > > > > #> sheetNames > > # [1] "Platten gesamt" "Platte 1-5" "Platte 6-10"> "Platte 11-15" "Platte 16-20" "Platte 21-25" > > # [7] "Platte 26-30" "Platte 31-35" "Platte 36-42"> "Replikate Platte 1-10" "Replikate Platte 11-20" "Replikate Platte21-30"> > #[13] "Replikate Platte 31-42" > > > > > > comInvoke(oxl, "Quit") > NULL > > > > read.xls(xlsfile, sheetNames[1], na.strings = c("na","","."),verbose=TRUE)> > Converting xls file > "I:/subProjects/bh/HPGD//Platten_Liste_090421.xls" > to csv file > "C:\DOKUME~1\beckmann\LOKALE~1\Temp\RtmpCU2zBu\file4db74d06.csv" > ... > > Executing perl C:/Programme/R/R-2.8.1/library/gdata/perl/xls2csv.pl"I:/> subProjects/bh/HPGD//Platten_Liste_090421.xls""C:\DOKUME~1\beckmann\LOKALE~1> \Temp\RtmpCU2zBu\file4db74d06.csv" Platten gesamt ... > > Sheetnumber must be an integer larger than 0. at C:/Programme/R/R-2.8.1/ > library/gdata/perl/xls2csv.pl line 57. > Done. > > Fehler in xls2csv(xls, sheet, verbose = verbose, ..., perl = perl) : > Unable to read translated csv file 'C:\DOKUME~1\beckmann\LOKALE~1\Temp > \RtmpCU2zBu\file4db74d06.csv'. > Fehler in file.exists(tfn) : ung?ltiges 'file' Argument > > > > > xlsfile <- file.path(.path.package('gdata'),'xls','iris.xls') > > xlsfile > [1] "C:/Programme/R/R-2.8.1/library/gdata/xls/iris.xls" > > > > iris <- read.xls(xlsfile) > Converting xls file to csv file... Done. > Reading csv file... Done. > > head(iris) # look at the top few rows > Sepal.Length Sepal.Width Petal.Length Petal.Width Species > 1 5.1 3.5 1.4 0.2 setosa > 2 4.9 3.0 1.4 0.2 setosa > 3 4.7 3.2 1.3 0.2 setosa > 4 4.6 3.1 1.5 0.2 setosa > 5 5.0 3.6 1.4 0.2 setosa > 6 5.4 3.9 1.7 0.4 setosa > > > > > > > > -- > Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3-> sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guidehttp://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code.