[R] Problem with RODBC

[David Scott]

  On 20/07/11 18:56, Dieter Menne wrote:
> David Scott-6 wrote:
>> I have been trying to read some data from an Excel workbook without
>> success.
>> ...
>>   >  faults<- sqlFetch(channel, sqtable = 'Data',
>> +                    colnames = FALSE, as.is = TRUE)
>>   >  faults
>> [1] "HY001 -1040 [Microsoft][ODBC Excel Driver] Too many fields defined."
>> [2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM [Data$]'"
>>
>>
> I have given up using odbc/Excel without named ranges, but I know it works
> sometimes. xlsReadWrite works well for whole sheets, while the gdata/Perl
> solutions can be terribly slow (minutes instead of seconds) with large
> files.
>
> I had seen the message above before, and it had to do with some invisible
> characters in the fields. I managed to get it to work by exporting value of
> the sheet, which seems to do a cleanup. Alternatively, a Copy/PasteValue.
> After that, my curiosity was satisfied, and I returned to named ranges or
> xlsReadWrite.
>
> Dieter
>
>
>
Thanks Dieter. Your reply prompted me to carry out some experimentation 
which confirmed to me the validity of your conclusions. I was unable to 
read the data satisfactorily using RODBC without creating a named range. 
Once I created a named range all was fine.

I did some searching for unusual characters in the data set, but 
couldn't find anything untoward. I tried removing the 1st row which had 
drop down lists but to no avail.

Another approach which worked was to copy the data from the existing 
sheet to a new sheet, retaining values and number formats.

Finally, I decided to save the workbook in .xlsx format, and use 
odbcConnectExcel2007. I was then able to read the data successfully, 
with one problem being that 255 columns were read, when only 20 actually 
contained data. The read also seemed a bit slow.

So, a few workarounds for anyone facing this problem in the future: 
named range; copy the data values to a new sheet; or use .xlsx format.

David Scott

-- 
_________________________________________________________________
David Scott	Department of Statistics
		The University of Auckland, PB 92019
		Auckland 1142,    NEW ZEALAND
Phone: +64 9 923 5055, or +64 9 373 7599 ext 85055
Email:	d.scott at auckland.ac.nz,  Fax: +64 9 373 7018