R help - Aug 2009 - using the RODBC for excel

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$''" 
 
 
 
Thanks,
Regards,
Han Chin

__________________________________________________



	[[alternative HTML version deleted]]

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 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.