Han Chin Chan wrote:
Hi,
I am trying to import data directly from an excel spreadsheet using the RODBC package. I am getting the following error messages, wondering if anyone can help me with it?
connection = odbcConnectExcel('D:\\R files\\TestData.xls')
tables = sqlTables(connection)
tables
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 D:\\R files\\TestData <NA> '30092007$' TABLE <NA>
2 D:\\R files\\TestData <NA> '30092008$' TABLE <NA>
3 D:\\R files\\TestData <NA> '31032007$' TABLE <NA>
4 D:\\R files\\TestData <NA> '31032008$' TABLE <NA>
5 D:\\R files\\TestData <NA> '31032009$' TABLE <NA>
query = paste("SELECT * FROM", tables$TABLE_NAME[1])
sqlQuery(connection,query)
[1] "HY000 -3003 [Microsoft][ODBC Excel Driver] Syntax error in query. Incomplete
query clause."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM '30092007$''"
Looks like nobody answered so far:
You need double quotes around the table name with its special character
"$" rather than single quotes, hence
sqlQuery(channel, gsub("'", '"', query))
should work.
Best,
Uwe Ligges
Thanks,
Regards,
Han Chin
__________________________________________________
[[alternative HTML version deleted]]
------------------------------------------------------------------------
______________________________________________
R-help@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.
______________________________________________
R-help@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.
