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
______________________________________________ [EMAIL PROTECTED] mailing list https://www.stat.math.ethz.ch/mailman/listinfo/r-devel
