[R] RODBC problem

[Data Analytics Corp.]

Schalk,

This worked.

Thanks for the hint.

Walt

Schalk Heunis wrote:
> 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.
>>
>>     
>
>   


-- 
________________________

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