[R] RODBC problem

[Greg Snow]

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.