Re: [R] "Missing value representation in Excel before - solved in a way- summary"

From: Fredrik Lundgren <fredrik.bg.lundgren_at_bredband.net>
Date: Wed 11 Jan 2006 - 09:01:43 EST

Thanks to Leif Kirschenbaum, Brian Ripley and Petr Pikal,

who helped with this problem. Unfortunately it appears as if the problem with columns beginning with NAs is deeply connected to Microsoft ODBC DLL's automatic determination of column type based on the contents of the first N rows of each column and further that this behaviour can't be reliably tweaked so that RODBC can function with every file.xls. Until the Microsoft ODBC DLL is fixed I will have to use read.table from 'textfile' or 'clipboard' or Gregory Warnes read.xls in package 'gdata'. All three methods have worked OK (inspite of some minor formatting aspects of dateformats) even with this tricky file.

Fredrik
----- Original Message -----
From: "Leif Kirschenbaum" <leif@reflectivity.com> To: <r-help@stat.math.ethz.ch>
Sent: Tuesday, January 10, 2006 9:50 PM
Subject: Re: [R] "Missing value representation in Excel before

>I reproduce from memory my exhaustive look into this issue.
> RODBC uses the Microsoft ODBC DLL's developed by Microsoft.
> These DLL's perform an automatic determination of column type based
> on the contents of the first N rows of cells in each column, where N
> [0,16]. N may be set in the Windows system registry, and there are a
> few other things that may be set in the system registry which control
> how the DLL parses an Excel spreadsheet. Unfortunately, the Microsoft
> DLL's do not always pay attention to the registry settings and do not
> always interpret them in the same manner.
> The end result is that no matter what you do with RODBC, and no
> matter how the authors of RODBC re-write it, some Excel spreadsheets
> will always be unreadable via RODBC given particular insidious
> combinations of data in some columns of your spreadsheet. (until such
> time as Microsoft fixes their DLL bugs, I mean features) I have some
> faint recollection that the Microsoft DLL incorrectly parses a column
> with non-empty rows due to some formatting issue of those particular
> columns, which I was unable to cure by re-formatting the source
> worksheet.
> I have had to resort to using the gdata package which runs a Perl
> script "xls2csv.pl", which converts an Excel spreadsheet to CSV, for a
> few Excel spreadsheets which exhibit the particular anomalies
> preventing use of RODBC.
>
> Leif Kirschenbaum
> Senior Yield Engineer
> Reflectivity, Inc.
> (408) 737-8100 x307
> leif@reflectivity.com
>
>> Message: 21
>> Date: Mon, 9 Jan 2006 18:06:49 +0100
>> From: "Fredrik Lundgren" <fredrik.bg.lundgren@bredband.net>
>> Subject: Re: [R] "Missing value representation in Excel before
>> extraction to R with RODBC"
>> To: "Prof Brian Ripley" <ripley@stats.ox.ac.uk>, "Petr Pikal"
>> <petr.pikal@precheza.cz>
>> Cc: R-help <r-help@stat.math.ethz.ch>
>> Message-ID: <000801c6153f$14ab6e60$4a9d72d5@Larissa>
>> Content-Type: text/plain; format=flowed; charset="iso-8859-1";
>> reply-type=response
>>
>> Dear list,
>>
>> Well, those columns in Excel that starts with NA (actually 8
>> NA's in my
>> case) is imported as all NA in R but if the columns starts
>> with at least
>> 3 cells with values (i.e not NA) the are imported correctly
>> to R. When
>> as.is=TRUE is used a simular conversion takes place but now
>> as all <NA>
>> and dates are represented as date-and-time.
>> Is there any way to get this correct even when the Excel
>> columns start
>> with several NA's?
>>
>> Sincerely
>> Fredrik
>>
>>
>> ----- Original Message -----
>> From: "Prof Brian Ripley" <ripley@stats.ox.ac.uk>
>> To: "Petr Pikal" <petr.pikal@precheza.cz>
>> Cc: "Fredrik Lundgren" <fredrik.bg.lundgren@bredband.net>; "R-help"
>> <r-help@stat.math.ethz.ch>
>> Sent: Monday, January 09, 2006 9:36 AM
>> Subject: Re: [R] "Missing value representation in Excel before
>> extraction to R with RODBC"
>>
>>
>> > On Mon, 9 Jan 2006, Petr Pikal wrote:
>> >
>> >> Hi
>> >>
>> >> I believe it has something to do with the column identification
>> >> decision. When R decides what is in a column it uses only
>> some values
>> >> from the beginning of a file.
>> >
>> > Not R, Excel. Excel tells ODBC what the column types are.
>> >
>
> ______________________________________________
> R-help@stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>



R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html Received on Wed Jan 11 09:10:44 2006

This archive was generated by hypermail 2.1.8 : Wed 11 Jan 2006 - 14:28:55 EST