[R] odbcConnectExcel() fails to fetch all columns

andrija djurovic djandrija at gmail.com
Sat Apr 21 00:58:27 CEST 2012

I use RODBC for importing Excel files quiet often and never got the
similar problem.

Have you tried with sqlQuery?

>>z <- odbcConnectExcel("./BBaselinePtQaires_apr2011.xls")

BQ <- sqlQuery(z, "select * from [BBaselinePtQaires$]")


On Fri, Apr 20, 2012 at 11:57 PM, Jeff Newmiller
<jdnewmil at dcn.davis.ca.us> wrote:
> Excel is not a database, and the Excel ODBC driver is extremely limited. Put your data in a CSV file or a SQL database (even a Jet database is a step up from Excel).
>  http://www.stata.com/support/faqs/data/odbc_excel.html
> ---------------------------------------------------------------------------
> 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.
> Andrew Roberts <andrew at thinkingbone.org> wrote:
>>Is there a parameter somewhere in RODBC that enables more columns to be
>>retrieved from an Excel worksheet?
>># This next bit uses an undocumented call in RODBC
>>z <- odbcConnectExcel("./BBaselinePtQaires_apr2011.xls")
>>BQ <- sqlFetch(z, "BBaselinePtQaires")
>>Gives me:
>>z RODBC[1]
>>BQ 134 obs. of 59 variables
>>I have all the rows in the worksheet but only the first 59 out of a
>>total of 70 columns. I’m in RStudio 0.95.263 using RODBC 1.3-3 and R
>>version 2.12.2 (2011-02-25).
>>I'm puzzled - the worksheet seems ok. If the worst comes to the worst I
>>will have to split the worksheet and cbind to put it back together but
>>that seems inelegant. The worksheet contains 134 rows, 70 columns and
>>in a spreadsheet that weighs in at 154 KB in total.
>>Can you help unbaffle me?
>>R-help at r-project.org mailing list
>>PLEASE do read the posting guide
>>and provide commented, minimal, self-contained, reproducible code.
> ______________________________________________
> 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