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

[Uwe Ligges]

michael.hecht at geocities.com wrote:

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

If you are not sure whether it is a bug, don't send a bug report, but 
ask on R-help, please.

If there is a bug in a contributed package, please report it to the 
package maintainer (Prof. Brian Ripley, in this case), not to r-bugs, 
because it's not a bug in R itself (most package maintainers cannot 
access the bug repository).

Answer to your question: It comes from Excel, which is not the perfect 
Database engine.


> 2. How can I access such tables with spaces in the name ??
> 

   sqlQuery(channel, 'select * from "Scan 1$"')

Uwe Ligges