Hello everybody, here is the code I use to read an excel file containing two
rows, one of date, the other of prices:
library(RODBC)
z <- odbcConnectExcel("SPX_HistoricalData.xls")
datas <- sqlFetch(z,"Sheet1")
close(z)
It works pretty well but the only thing is that the datas stop at row 7530
and I don?t know why datas is a data frame that contains 7531 rows with the
last two ones = NA...
-----
Anna Lippel
new in R so be careful I should be asking a loooooooot of questions!:teeth:
--
View this message in context:
http://old.nabble.com/Error-on-reading-an-excel-file-tp26371750p26371750.html
Sent from the R help mailing list archive at Nabble.com.
anna_l wrote:> > Hello everybody, here is the code I use to read an excel file containing > two rows, one of date, the other of prices: > library(RODBC) > z <- odbcConnectExcel("SPX_HistoricalData.xls") > datas <- sqlFetch(z,"Sheet1") > close(z) > It works pretty well but the only thing is that the datas stop at row 7530 > and I don?t know why datas is a data frame that contains 7531 rows with > the last two ones = NA... >I find this occurs sometimes when I export an Excel worksheet to CSV. Excel will include one or more rows of blank cells after the data stops. I would imagine the behavior you are seeing with RODBC is due to the same issue. I don't know if there is anything that can be done about it other than to trim your dataset back to the appropriate length once it gets into R. Good luck! -Charlie ----- Charlie Sharpsteen Undergraduate Environmental Resources Engineering Humboldt State University -- View this message in context: http://old.nabble.com/Error-on-reading-an-excel-file-tp26371750p26376554.html Sent from the R help mailing list archive at Nabble.com.
You could try one of the other methods of reading Excel files and see if they are affected: http://wiki.r-project.org/rwiki/doku.php?id=tips:data-io:ms_windows On Mon, Nov 16, 2009 at 8:19 AM, anna_l <lippelanna21 at hotmail.com> wrote:> > Hello everybody, here is the code I use to read an excel file containing two > rows, one of date, the other of prices: > library(RODBC) > ? ? ? ?z <- odbcConnectExcel("SPX_HistoricalData.xls") > ? ? ? ?datas <- sqlFetch(z,"Sheet1") > ? ? ? ?close(z) > It works pretty well but the only thing is that the datas stop at row 7530 > and I don?t know why datas is a data frame that contains 7531 rows with the > last two ones = NA... > > ----- > Anna Lippel > new in R so be careful I should be asking a loooooooot of questions!:teeth: > -- > View this message in context: http://old.nabble.com/Error-on-reading-an-excel-file-tp26371750p26371750.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >
Gabor Grothendieck wrote:> > You could try one of the other methods of reading Excel files and see > if they are affected: >I would guess that since Excel includes the blank rows when exporting to CSV, then blank cells are being stored by Excel in the data files-- therefore any method of extracting data from those files will also pick up the empty cells. I think the crux of this issue lies with Excel and you will probably have to look for a fix there. -Charlie ----- Charlie Sharpsteen Undergraduate Environmental Resources Engineering Humboldt State University -- View this message in context: http://old.nabble.com/Error-on-reading-an-excel-file-tp26371750p26376915.html Sent from the R help mailing list archive at Nabble.com.