[R] exporting long character vectors to dbf

[Eduardo Leoni]

It all sort of works now. The remaining problem is how to pass "new
line" characters. If you let "\n" in the character field, the table is
not read correctly by MySQL. But this is really minor.

Thanks!

On 5/25/06, Prof Brian Ripley <ripley at stats.ox.ac.uk> wrote:
> On Thu, 25 May 2006, Eduardo Leoni wrote:
>
> > I am trying to use RODBC, but still run into some problems. If I
> > understood correctly, I have to change the default type for char
> > vectors to "text" (or varchar(3000)).
>
> You should do this in sqlSave for the specific table, via the 'typeinfo'
> argument.
>
> > But when I try something like
> >
> >> library(RODBC)
> >> channel <- odbcConnect("supremo", uid="R", case="tolower")
> >> setSqlTypeInfo("MySQL",
> >               list(double="double",
> >                    integer="integer",
> >                    character="varchar(3000)",
> >                    logical="varchar(5)"))
> >
> > Warning message:
> > number of items to replace is not a multiple of replacement length
> >
> > I think there is a pair of brackets missing in setSqlTypeInfo here
> >   typesR2DBMS[driver] <- value[c("double", "integer", "character",
> >       "logical")]
>
> Yes, thank you.
>
> > by the way, is there any easy way to debug packages that does not
> > involve reapeatedly modifying, compiling and installing them again??
>
> Not sure what you mean by `debug packages'.  For debugging R code, see the
> section in `Writing R Extension' in R >= 2.3.0
>
> >
> >
> >
> >
> >
> > On 5/24/06, Prof Brian Ripley <ripley at stats.ox.ac.uk> wrote:
> >> On Wed, 24 May 2006, Mulholland, Tom wrote:
> >>
> >> > I assume this is (or was) a specification issue. I think write.dbf uses
> >> > the shapefile library (C not R library) so it applies to the use of
> >> > shapefiles and just happens to have been included in the foreign package
> >> > because it has a generic usefullness. (Is that a word?)
> >>
> >> Actually, the width of the dbf field comes from the lines in write.dbf:
> >>
> >>          else if (is.character(x)) {
> >>              mf <- max(nchar(x[!is.na(x)]))
> >>              precision[i] <- min(max(nlen, mf), 254)
> >>              scale[i] <- 0
> >>
> >> so that's the limit (254, not 255).  This is stated as a limitation of the
> >> dbf format at
> >> http://www.clicketyclick.dk/databases/xbase/format/data_types.html
> >> so I don't think you can do what you want with .dbf.
> >>
> >> > Since I very rarely care about the elegance of my solutions, just that
> >> > they work, I would try saving the file in another format that you can
> >> > get open office to read and let it do the conversion rather than trying
> >> > to get R to do it. I'm sure OpenOffice can deal with straightforward
> >> > text files if that's a last resort.
> >>
> >> Indeed.  Using an ODBC driver (and RODBC) to write to the database might
> >> be a good option.
> >>
> >> >
> >> > Tom
> >> >
> >> >> -----Original Message-----
> >> >> From: r-help-bounces at stat.math.ethz.ch
> >> >> [mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of Eduardo Leoni
> >> >> Sent: Wednesday, 24 May 2006 7:13 AM
> >> >> To: r-help at stat.math.ethz.ch
> >> >> Subject: [R] exporting long character vectors to dbf
> >> >>
> >> >>
> >> >> Hi -
> >> >>
> >> >> I need to export data to openoffice base, where one of the elements is
> >> >> a long character vector (>255 characters.) write.dbf exports it as
> >> >> varchar, truncating the data. Any idea how to do this?
> >> >>
> >> >> thanks,
> >> >>
> >> >> -eduardo
> >> >>
> >> >> ______________________________________________
> >> >> R-help at 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 at 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
> >> >
> >>
> >> --
> >> Brian D. Ripley,                  ripley at 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 at 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
>