Re: "Missing value representation in Excel before

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: "Missing value representation in Excel before

Leif Kirschenbaum-4
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
[hidden email]

> Message: 21
> Date: Mon, 9 Jan 2006 18:06:49 +0100
> From: "Fredrik Lundgren" <[hidden email]>
> Subject: Re: [R] "Missing value representation in Excel before
> extraction to R with RODBC"
> To: "Prof Brian Ripley" <[hidden email]>, "Petr Pikal"
> <[hidden email]>
> Cc: R-help <[hidden email]>
> 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" <[hidden email]>
> To: "Petr Pikal" <[hidden email]>
> Cc: "Fredrik Lundgren" <[hidden email]>; "R-help"
> <[hidden email]>
> 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.
> >

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Reply | Threaded
Open this post in threaded view
|

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

Fredrik Lundgren
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" <[hidden email]>
To: <[hidden email]>
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
> [hidden email]
>
>> Message: 21
>> Date: Mon, 9 Jan 2006 18:06:49 +0100
>> From: "Fredrik Lundgren" <[hidden email]>
>> Subject: Re: [R] "Missing value representation in Excel before
>> extraction to R with RODBC"
>> To: "Prof Brian Ripley" <[hidden email]>, "Petr Pikal"
>> <[hidden email]>
>> Cc: R-help <[hidden email]>
>> 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" <[hidden email]>
>> To: "Petr Pikal" <[hidden email]>
>> Cc: "Fredrik Lundgren" <[hidden email]>; "R-help"
>> <[hidden email]>
>> 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.
>> >
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html