[R] Fwd: Problem with ODBC connection

Duncan Mackay mackay at northnet.com.au
Tue Jun 11 08:03:21 CEST 2013


Try

library(RODBC)
Channel1 <- odbcConnectExcel2007("MyFile.xls", readOnly = TRUE)
  Channel1
RODBC Connection 1
Details:
   case=nochange
   DBQ=g:\1\MyFile.xls
   DefaultDir=g:\1
   Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
   DriverId=1046
   MaxBufferSize=2048
   PageTimeout=5

aaaa = sqlFetch(Channel1, "aaaa",
            colnames = F,
            rownames = F)
str(aaaa)
'data.frame':   7100 obs. of  21 variables:
  $ Criteria: chr  "a" "s" "d" "f" ...
  $ s       : num  NA NA NA NA NA NA NA NA NA 0 ...
  $ d       : num  NA 0 0 NA 0 NA NA 0 0 NA ...
  $ fd      : logi  NA NA NA NA NA NA ...
  $ f       : logi  NA NA NA NA NA NA ...
  $ fd1     : num  0.00 0.00 1.73e-18 NA 0.00 ...
  $ f1      : num  2.78e-17 0.00 6.94e-18 NA 0.00 ...
  $ fd2     : num  -4.03e-16 0.00 2.78e-17 NA 0.00 ...
  $ f2      : num  NA NA 5 -4.25 -1.53 0 NA NA NA NA ...
  $ fd3     : logi  NA NA NA NA NA NA ...
  $ f3      : logi  NA NA NA NA NA NA ...
  $ F12     : logi  NA NA NA NA NA NA ...
  $ F13     : logi  NA NA NA NA NA NA ...
  $ F14     : logi  NA NA NA NA NA NA ...
  $ F15     : logi  NA NA NA NA NA NA ...
  $ F16     : logi  NA NA NA NA NA NA ...
  $ F17     : logi  NA NA NA NA NA NA ...
  $ F18     : logi  NA NA NA NA NA NA ...
  $ F19     : logi  NA NA NA NA NA NA ...
  $ F20     : logi  NA NA NA NA NA NA ...
  $ F21     : logi  NA NA NA NA NA NA ...

Duncan


Duncan Mackay
Department of Agronomy and Soil Science
University of New England
Armidale NSW 2351
Email: home: mackay at northnet.com.au


At 06:34 11/06/2013, you wrote:
>Any response please? Was my question not clear to the list? Please let me
>know.
>
>Thanks and regards,
>
>---------- Forwarded message ----------
>From: Christofer Bogaso <bogaso.christofer at gmail.com>
>Date: Sat, Jun 8, 2013 at 9:39 PM
>Subject: Re: Problem with ODBC connection
>To: r-help <r-help at r-project.org>
>
>
>Hello All,
>
>My previous post remains unanswered probably because the attachment was not
>working properly.
>
>So I am re-posting it again.
>
>My problem is in reading an Excel-2003 file through ODBC connection using
>RODBC package. Let say I have this Excel file:
>
>http://www.2shared.com/document/HS3JeFyW/MyFile.html
>
>
>I saved it in my "F:" drive and tried reading the contents using RODBC
>connection:
>
> > library(RODBC)
> > MyData <- sqlFetch(odbcConnectExcel("f:/MyFile.xls"), "aaaa")
> > head(MyData, 30)
>
>
>However it looks that the second column (with header 's') is not read
>properly.
>
>Can somebody here explain this bizarre thing? Did I do something wrong in
>reading that?
>
>Really appreciate if someone could point out anything what might go wrong.
>
>Thanks and regards,
>
>
>On Fri, Jun 7, 2013 at 4:46 PM, Christofer Bogaso <
>bogaso.christofer at gmail.com> wrote:
>
> > Hello again,
> >
> > I am having problem with ODBC connection using the RODBC package.
> >
> > I am basically trying to read the attached Excel-2003 file using RODBC
> > package. Here is my code:
> >
> > > head(sqlFetch(odbcConnectExcel("d:/ssss1.xls"), "aaaa"), 30);
> > odbcCloseAll()
> >    Criteria  s  d fd  f                        fd1
> >     f1                        fd2    f2 fd3 f3 F12 F13 F14 F15 F16 F17
> > F18 F19 F20
> > 1         a NA NA NA NA 0.000000000000000000000000
> > 0.000000000000000027755576 -0.00000000000000040332321    NA  NA NA  NA
> >  NA  NA  NA  NA  NA  NA  NA  NA
> > 2         s NA  0 NA NA 0.000000000000000000000000
> > 0.000000000000000000000000  0.00000000000000000000000    NA  NA NA  NA
> >  NA  NA  NA  NA  NA  NA  NA  NA
> > 3         d NA  0 NA NA 0.000000000000000001734723
> > 0.000000000000000006938894  0.00000000000000002775558  5.00  NA NA  NA
> >  NA  NA  NA  NA  NA  NA  NA  NA
> > 4         f NA NA NA NA                         NA
> >     NA                         NA -4.25  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 5         f NA  0 NA NA 0.000000000000000000000000
> > 0.000000000000000000000000  0.00000000000000000000000 -1.53  NA NA  NA
> >  NA  NA  NA  NA  NA  NA  NA  NA
> > 6         f NA NA NA NA                         NA
> >     NA  0.00000000000000000000000  0.00  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 7         f NA NA NA NA                         NA
> >     NA  0.00000000000000000000000    NA  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 8         f NA  0 NA NA                         NA
> >     NA                         NA    NA  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 9         f NA  0 NA NA                         NA
> >     NA                         NA    NA  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 10        f NA NA NA NA                         NA
> >     NA                         NA    NA  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 11        f NA NA NA NA                         NA
> >     NA                         NA    NA  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 12        f NA NA NA NA                         NA
> >     NA                         NA    NA  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> > 13        f NA NA NA NA                         NA
> >     NA                         NA    NA  NA NA  NA  NA  NA  NA  NA  NA
> >  NA  NA  NA
> >
> > Here you see the data in second column could not read at all.
> >
> > Can somebody point me if I did something wrong?
> >
> > Thanks and regards,
> >
>
>         [[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org 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.



More information about the R-help mailing list