[R] using the RODBC for excel

[Uwe Ligges]

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.