R devel - Nov 2004 - RODBC bug or doc error in sqlFetch on xls files (PR#7354)

R 2.0.0 patched under win2000. MS Office Excel 2003. ODBC Drivers? 

This may not come as a surprise -- sqlFetch() (and perhaps other ?) appear
not to handle table/worksheet names with spaces in them in Excel tables. I
was not able to find documentation that specifically mentioned this,
although the Help pages vaguely hinted that there might be difficulty with
Excel's "peculiar handling" of table names. Anyway, here is a
sequence of
actions that illustrate the issue.
> library(RODBC)
## connect to Excel file using dialog box
> z<-odbcConnectExcel()
## List the tables: "Success Rates" is the only worksheet there, of
course
> sqlTables(z)
                                                      TABLE_CAT TABLE_SCHEM
1 C:\\Documents and Settings\\bgunter\\My Documents\\LRPdatacln       
<NA>
2 C:\\Documents and Settings\\bgunter\\My Documents\\LRPdatacln       
<NA>
3 C:\\Documents and Settings\\bgunter\\My Documents\\LRPdatacln       
<NA>
                    TABLE_NAME TABLE_TYPE REMARKS
1             'Success Rates$'      TABLE    <NA>
2   'Success Rates$'Print_Area      TABLE    <NA>
3 'Success Rates$'Print_Titles      TABLE    <NA>


## The following give errors, although
## according to p.16 of manual, this should work:
> dat<-sqlFetch(z,"Success Rates")
Error in odbcTableExists(channel, sqtable) : 
        Success Rates : table not found on channel

## This only seemed to work ...
> dat<-sqlFetch(z,"'Success Rates$'")
## But
> dat
[1] "[RODBC] ERROR: Could not SQLExecDirect"

[2] "37000 -1002 [Microsoft][ODBC Excel Driver] ''Success
Rates$'$' is not a
valid name. .. blah blah

 
When I removed the space from the sheet name -- "SuccessRates" -- all
worked
smoothly as documented.

Cheers,
Bert

-- Bert Gunter
Genentech Non-Clinical Statistics
South San Francisco, CA
 
"The business of the statistician is to catalyze the scientific learning
process."  - George E. P. Box

On Tue, 2004-11-09 at 10:23, gunter.berton@gene.com
wrote:
> R 2.0.0 patched under win2000. MS Office Excel 2003. ODBC Drivers? 
> 
> This may not come as a surprise -- sqlFetch() (and perhaps other ?) appear
> not to handle table/worksheet names with spaces in them in Excel tables. I
> was not able to find documentation that specifically mentioned this,
> although the Help pages vaguely hinted that there might be difficulty with
> Excel's "peculiar handling" of table names. Anyway, here is a
sequence of
> actions that illustrate the issue.
IIRC the SQL Language standard specifically requires table and database names to

a. start with a letter, 
b. contain nothing except letters and digits and the "underscore", and
c. be at most 31 characters long.

In other words, Excel is doing something that many other tools don't
support.

(and don't get me started on case (in)sensitivity.)