michael.hecht@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