[Rd] RODBC access to MS-Excel (PR#4266)

[michael.hecht at geocities.com]

Full_Name: Michael Hecht
Version: 1.7.1
OS: WinNT
Submission from: (NULL) (193.158.76.205)


I've got a problem with the RODBC library while trying to access to an MS-Excel
file. The Excel file was originally exportet by a commercial software, so I
cannot influence it. The problem is, that the names of the tables include
spaces, e.g. "Scan 1","Scan 2" etc. If I use RODBC, I get back something like
this
 
> channel<-odbcConnectExcel(fileName)
> tables<-sqlTables(channel)
> scanTables<-tables[c(grep("Scan",tables[,"TABLE_NAME"])),]
> scanTables
   TABLE_SCHEM TABLE_NAME   TABLE_TYPE REMARKS
16        <NA>  'Scan 1$'        TABLE    <NA>
4         <NA> 'Scan 10$'        TABLE    <NA>
5         <NA> 'Scan 11$'        TABLE    <NA>
6         <NA> 'Scan 12$'        TABLE    <NA>
...
 
If I try to fetch a table I get as result
 
> data<-sqlFetch(channel,"'Scan 2$'",rownames=T)
> data
[1] "S1000 -3003 [Microsoft][ODBC Excel Driver] Syntax error in query. 
Incomplete query clause."
[2] "[RODBC] ERROR: Could not SQLPrepare"   
 
If I now change by hand the Excel table, e.g. to "Scan 1" -> "Scan1" I get
   TABLE_SCHEM TABLE_NAME   TABLE_TYPE REMARKS
2         <NA>     Scan1$ SYSTEM TABLE    <NA>
4         <NA> 'Scan 10$'        TABLE    <NA>
5         <NA> 'Scan 11$'        TABLE    <NA>
6         <NA> 'Scan 12$'        TABLE    <NA>

but the following does'nt work
 
> data<-sqlFetch(channel,scanTables[1,"TABLE_NAME"],rownames=T)
Error in odbcTableExists(channel, sqtable) : 
        Scan1$ : table not found on channel

whereas this works well:
> data<-sqlFetch(channel,"Scan1",rownames=T)
> data
  X-Coord Y-Coord     C  a        C2        Si       Mn         P          S    
   Cr       Cr1
1     1648  117896 298.9131  5.558300  4.323123 167.3666 16.674902  0.6175889 
74.72826 0.6175889
2     1657  116857 374.2589 10.499012 11.116601 382.9052 16.674902 11.7341900
172.92490 0.6175889
3     1667  119522 453.9279  3.705534 11.116601 295.8251 15.439723  5.5583005
134.63438 0.6175889
4     1678  119281 468.7500 14.204546 12.351779 369.3182 22.233202  8.6462450
173.54250 0.6175889
...
 
Therefore I've two questions/problems:
1. where comes the $ from, Scan1$ or 'Scan 2$', is this a bug ??
2. How can I access such tables with spaces in the name ??