我需要从交易数据中取出我所需要的股票代码,日期,和收盘价,这个数据保存在RHome名叫price3.xlsx文件中。
library(RODBC)<br />
price <- odbcConnectExcel2007("price3.xlsx")<br />
price<br />
RODB Connection 2
Details:
case=nochange
DBQ=D:\Program Files\R\R-2.6.0\price3.xlsx
DefaultDir=D:\Program Files\R\R-2.6.0
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
DriverId=1046
MaxBufferSize=2048
PageTimeout=5
sqlTables(price)
TABLE_CAT TABLE_SCHEM TABLE_NAME
1 D:\\Program Files\\R\\R-2.6.0\\price3.xlsx <NA> price3$
TABLE_TYPE REMARKS
1 SYSTEM TABLE <NA>
sqlQuery(price, "select code, date,price from price3$ where code = 2 and date < '2006-6-23' order by date desc limit 0,150")
得到以下错误信息:
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "42000?N\xf2\xff\xff\003 -3506 [Microsoft][ODBC Excel Driver] FROM 子句语法错误。"
SQL语法应该是没有错的,我在RODBC连接MySQL下用这个语句可以得到正确结果,当然table name是不同的。
而
sqlFetch(price,"price3$",max = 2)
却能够取出结果,
code date priceb price volume value pricea turnover tvalue
1 2 2004-09-01 3.25 3.22 6081000 30322000 3.23 0.386 11299930042
2 2 2004-09-02 3.22 3.27 5096200 25480000 3.23 0.323 11504556897
tshare pb
1 2273627871 2.40
2 2273627871 2.45
不知道这是怎么回事,但是用sqlFetch我没有办法控制输出的结果。不知道有没有人也遇到过这种问题,在mail-list搜了以下,没得到答案。
sessionInfo()
R version 2.6.0 (2007-10-03)
i386-pc-mingw32
locale:
LC_COLLATE=Chinese_People's Republic of China.936;LC_CTYPE=Chinese_People's Republic of China.936;LC_MONETARY=Chinese_People's Republic of China.936;LC_NUMERIC=C;LC_TIME=Chinese_People's Republic of China.936
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RODBC_1.2-2
loaded via a namespace (and not attached):
[1] rcompgen_0.1-17 tools_2.6.0