[R] odbcConnectExcelpel() fails to fetch all columns

[Andrew Roberts]

Thanks for your help,

I have exported the worksheet to .csv and it imports fine. I then 
reloaded the .csv back into Excel and  ... (red face) all the columns 
came across. I have no idea as to what was in the Excel worksheet that 
tripped up RODBC. I very much take your point Jeff but I have to work 
with what I'm given.

Kind regards to you both

Andrew

On 20/04/2012 23:58, andrija djurovic wrote:
> Hi.
> 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$]")
>
> Andrija
>
> 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:
>>
>>> Folks,
>>>
>>> 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]
>>>
>>> And
>>>
>>> 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
>>> is
>>> in a spreadsheet that weighs in at 154 KB in total.
>>>
>>> Can you help unbaffle me?
>>>
>>> Andrew
>>>
>>> ______________________________________________
>>> 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.
>> ______________________________________________
>> 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.