Try it without the '$' in the table name, that has worked for me in the past.
-- Gregory (Greg) L. Snow Ph.D. Statistical Data Center Intermountain Healthcare greg.s...@imail.org 801.408.8111 > -----Original Message----- > From: r-help-boun...@r-project.org [mailto:r-help-boun...@r- > project.org] On Behalf Of Data Analytics Corp. > Sent: Thursday, September 24, 2009 10:23 AM > To: r-help@r-project.org > Subject: [R] RODBC problem > > Hi, > > I'm attempting to use the RODBC package on Windows Vista to import an > excel spreadsheet. The spreadsheet has three worksheets the last of > which is blank. Following an example in Phil Spector's book (p. 34), > after creating a connection named con I did the following: > > > con > RODBC Connection 3 > Details: > case=nochange > DBQ=c:\temp\test.xls > DefaultDir=c:\temp > Driver={Microsoft Excel Driver (*.xls)} > DriverId=790 > MaxBufferSize=2048 > PageTimeout=5 > > tbls <- sqlTables(con) > > tbls > TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS > 1 c:\\temp\\test <NA> Sheet1$ SYSTEM TABLE <NA> > 2 c:\\temp\\test <NA> Sheet2$ SYSTEM TABLE <NA> > 3 c:\\temp\\test <NA> Sheet3$ SYSTEM TABLE <NA> > > Everything seems to be fine. Then I did > > > qry <- paste("SELECT * FROM", tbls$TABLE_NAME[1], sep = ' ') > > qry > [1] "SELECT * FROM Sheet1$" > > sqlQuery(con, qry) > > and got the error message > > [1] "42000 -3506 [Microsoft][ODBC Excel Driver] Syntax error in FROM > clause." "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM > Sheet1$'" > > Any advise as to why and how to fix it? What's the syntax error that > I'm just not seeing? > > Thanks, > > Walt > > > > -- > ________________________ > > Walter R. Paczkowski, Ph.D. > Data Analytics Corp. > 44 Hamilton Lane > Plainsboro, NJ 08536 > ________________________ > (V) 609-936-8999 > (F) 609-936-3733 > dataanalyt...@earthlink.net > www.dataanalyticscorp.com > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code. ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.