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]]

