Re: [R] RODBC, sqlQuery with NA:s

From: Lauri Nikkinen <lauri.nikkinen_at_iki.fi>
Date: Tue 03 Apr 2007 - 08:52:40 GMT

Thanks again,

There is still going on something strange, because na.strings="" in sqlQuery clause didn't help. I'm helpless but thanks anyway...

-Lauri

2007/4/3, Prof Brian Ripley <ripley@stats.ox.ac.uk>:
>
> On Tue, 3 Apr 2007, Lauri Nikkinen wrote:
>
> > Thanks Prof Ripley for your answer. In fact, there is no NULL's in these
> > varchar variables, that is because one of the software's we use do not
> > accept NULL's (that is strange, isn't it?).
>
> It's not uncommon. (Be grateful you are not on MacOS, where it seems
> quite common to accept NULLs but not treat them as different from "".)
>
> > So, for instance, when I type
> >
> > select * from table1
> > where var1=''
> >
> > I get all those rows where the cells of var1 are empty. So, is there any
> > possibility to tell R that it should convert these empty cells to NA:s
> (when
> > importing)?
>
> Yes, via na.strings="".
>
> >
> > Thanks for your help
> > Lauri
> >
> >
> > 2007/4/3, Prof Brian Ripley <ripley@stats.ox.ac.uk>:
> >>
> >> In my attempts to reproduce this both approaches worked.
> >>
> >> There seems to be something going on we cannot see (and I don't believe
> >> NULL in the database is being mapped to " "). For example, might there
> be
> >> non-breaking spaces (character A0 in Windows 1252, your most likely
> >> character set) in the database?
> >>
> >> On Mon, 2 Apr 2007, Lauri Nikkinen wrote:
> >>
> >> > Hi R-users,
> >> >
> >> > I'm trying to retrieve data from MS SQL database with RODBC's
> >> > sqlQuery-function:
> >> >
> >> > temp <- sqlQuery(channel,"select *, (select text from table1 where
> >> > koodi='paa' and koodi2=paa) as tempor from table2")
> >> >
> >> > str(temp)
> >> >
> >> > ?
> >> > $ var0 : num NA NA 1.6 NA NA 1.4 NA 1 NA NA ...
> >> > $ var1 : Factor w/ 45 levels " ","01","01;07",..: 1 1 1 4 38
> 1
> >> 38 7
> >> > 1 1 ...
> >> > $ var2 : Factor w/ 95 levels " ","01","01;02;09",..: 1 1 1 9
> 72 1
> >> 78
> >> > 13 1 1 ...
> >> > $ var3 : Factor w/ 104 levels " ","01","01;02;09",..: 1 1 1 44
> 82 1
> >> 87
> >> > 14 1 1 ...
> >> > ?
> >> >
> >> > Data types of these variables in the MS SQL database are:
> >> >
> >> > var0: float, NULL
> >> > var1: varchar, NULL
> >> > var2: varchar, NULL
> >> > var3: varchar, NULL
> >> >
> >> > As you can see from above, I get variable var0 with NA:s because of
> the
> >> data
> >> > type (there is NULL in the database if there is no observation in the
> >> > specific cell). How can I get NA:s to these Factor variables also? I
> >> have
> >> > tried the following with no positive results:
> >> >
> >> > temp <- sqlQuery(channel,"select *, (select text from table1 where
> >> > koodi='paa' and koodi2=paa) as tempor from table2", na.strings=" ")
> >> >
> >> > and also afterwards
> >> >
> >> > temp$var1[temp$var1 == " "] <- "NA"
> >> >
> >> > Regards,
> >> > Lauri
> >> >
> >> > [[alternative HTML version deleted]]
> >> >
> >> >
> >>
> >> --
> >> Brian D. Ripley, ripley@stats.ox.ac.uk

> >> Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/
> >> University of Oxford, Tel: +44 1865 272861 (self)
> >> 1 South Parks Road, +44 1865 272866 (PA)
> >> Oxford OX1 3TG, UK Fax: +44 1865 272595
> >
>
> --
> Brian D. Ripley, ripley@stats.ox.ac.uk
> Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/
> University of Oxford, Tel: +44 1865 272861 (self)
> 1 South Parks Road, +44 1865 272866 (PA)
> Oxford OX1 3TG, UK Fax: +44 1865 272595
>

        [[alternative HTML version deleted]]



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 and provide commented, minimal, self-contained, reproducible code. Received on Tue Apr 03 18:57:49 2007

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Tue 03 Apr 2007 - 09:30:45 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.