Problem with ODBC connection

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,
An embedded and charset-unspecified text was scrubbed...
Name: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20130608/9593d955/attachment.pl>
An embedded and charset-unspecified text was scrubbed...
Name: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20130611/5960f498/attachment.pl>
Given the resounding silence, I would venture to guess that no-one here is interested in troubleshooting ODBC connections to Excel. The problem is most likely in the ODBC driver for Excel (not in R or RODBC), and Excel is NOT a database (so any data format problem is unlikely to be detected).
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

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.
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On Behalf Of Jeff Newmiller
Sent: Monday, June 10, 2013 9:45 PM
To: Christofer Bogaso; r-help
Subject: Re: [R] Fwd: Problem with ODBC connection

Given the resounding silence, I would venture to guess that no-one here is
interested in troubleshooting ODBC connections to Excel. The problem is
most likely in the ODBC driver for Excel (not in R or RODBC), and Excel is
NOT a database (so any data format problem is unlikely to be detected).
--------------------------------------------------------------------------
-
Jeff Newmiller                        The     .....       .....  Go
Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live
Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.
rocks...1k
--------------------------------------------------------------------------
-
Sent from my phone. Please excuse my brevity.

Christofer Bogaso <bogaso.christofer at gmail.com> wrote:

I tried reading your workbook using your code, i.e.

library(RODBC)
MyData <- sqlFetch(odbcConnectExcel('mypath/Myfile.xls'), "aaaa")
head(MyData, 30)

and got an error message saying that odbcConnectExcel is only usable with 32-bit Windows and I have a 64-bit system, so I can't help you there.  But there are many other options in R for reading Excel workbooks.  I was able to read your data using the read.xls function from the gdata package.  I am not endorsing that package, it just happened to be the first package on my system that I tried.  

So if you can't read the data one way, try another.  You could install and load the sos package and runthe following function

findFn('xls')

and you will get all sorts of suggestions.

Hope this is helpful,

Dan

Daniel Nordlund
Bothell, WA USA
On Tue, 11 Jun 2013 02:19:14 +0545
Any real answer would be contingent on a reader being provided a
reproducible example. Since you don't provide that, there's not a lot
of point to an answer. However, to tilt at a windmill, depending on the
size and complexity of your data file, it might be easier to simply
export the data from Excel as a csv file and use read.table to bring it
in to R.

JWDougherty
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
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.