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