[R] RODBC vs gdata

[Christof Kluß]

Hi Enrico,

thank you very much, so it is a known problem with the Microsoft Excel
ODBC drivers :(

"7 Excel Drivers
... There are at least two known problems with reading columns that do
not have a format set before data entry, and so start with format
`General'. First, the driver uses the first few rows to determined the
column type, and is over-fond of declaring `Numeric' even when there are
non-numeric entries. ... Second, if a column is declared as `Text',
numeric entries will be read as SQL nulls and hence R NAs.
Unfortunately, in neither case does reformatting the column help."

So I think I have to use "gdata" to be sure to read all datas.

regards
Christof


Am 09-01-2012 19:29, schrieb Enrico Schumann:
> 
> Hi Christof,
> 
> have a look at the manual of RODBC, and in particular the section on
> Excel drivers.
> 
> RShowDoc("RODBC", package="RODBC")
> 
> Regards,
> Enrico
> 
> 
> Am 09.01.2012 19:02, schrieb Christof Kluß:
>> Hi
>>
>> one col in my Excel file contains many numbers. But on line 3000 and
>> some other lines are strings like "FG 1". "RODBS" seems to omit this
>> lines. "gdata" works, but is much slower.
>>
>> Is this a bug of RODBC or do I apply it wrong?
>>
>> Example with the same "file.xlsx"
>>
>>
>> library(RODBC); excel<- odbcConnectExcel2007("file.xlsx")
>> tab<- sqlQuery(excel, 'select * from "Table 1$"'); str(tab)
>>
>> col1: num  1 2 3 4 5 6 7 8 9 10 ...
>>
>> library(gdata); tab<- read.xls("file.xlsx", sheet=1); str(tab)
>>
>> col1: Factor w/ 51 levels "1","10","11",..: 1 12 23 34 41 42 43...
>>
>>
>> greeting
>> Christof
>>
>> ______________________________________________
>> 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.
>>
>