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
dataanalytics at earthlink.net
www.dataanalyticscorp.com
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.snow at imail.org 801.408.8111> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r- > project.org] On Behalf Of Data Analytics Corp. > Sent: Thursday, September 24, 2009 10:23 AM > To: r-help at 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 > dataanalytics at earthlink.net > www.dataanalyticscorp.com > > ______________________________________________ > R-help at 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.
Walt I get the same message using R2.9.2 on Vista. Using sqlFetch(con,'Sheet1') seems to however. HTH Schalk Heunis On Thu, Sep 24, 2009 at 6:23 PM, Data Analytics Corp. <dataanalytics at earthlink.net> wrote:> 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 > dataanalytics at earthlink.net > www.dataanalyticscorp.com > > ______________________________________________ > R-help at 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. >