Hi Ivan,

Thanks for your further advice.

RODBC works without problem.  Following steps are correct.

> library(RODBC)
> data=odbcConnectExcel(file.choose())
> sqlTables(data)
                                              TABLE_CAT TABLE_SCHEM
1 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDev        <NA>
2 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDev        <NA>
3 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDev        <NA>
4 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDev        <NA>
5 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDev        <NA>
6 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDev        <NA>
      TABLE_NAME   TABLE_TYPE REMARKS
1      Graphics$ SYSTEM TABLE    <NA>
2   Interactive$ SYSTEM TABLE    <NA>
3   MyDataframe$ SYSTEM TABLE    <NA>
4            ggg        TABLE    <NA>
5        mypivot        TABLE    <NA>
6 'Pivot table$'        TABLE    <NA>

> mydata=sqlFetch(data, "MyDataframe")
> odbcClose(data)

> mydata
    Gender Weight Size SizeM SizeF F6 F7 1#
1        m     68  183   177   178 NA NA NA
2        m     80  184   160   176 NA NA NA
3        f     62  173   171   175 NA NA NA
4        f     55  165   164   185 NA NA  2
5        f     60  165   165   190 NA NA NA
6        m     70  185    NA   185 NA NA  3
7        m     81  179   165   175 NA NA NA
8        m     93  185   173   184 NA NA NA
9        f     70  178   172   184 NA NA  4
10       f     75  168   160   180 NA NA NA
.....
....


My finding is RODBC can't handle complicate .xls file nor .xlsx file (maybe 
odbcConnectExcel2007 can work on .xlsx.  I haven't tried), with multiple 
headers, merged cells etc.

My goal is to import data of .xls/xlsx file on R console, run R commands and 
export the result back to spreadsheet.  RExcel and "R and Calc" can do the job.  
RExcel, 32bit, runs on Windows.  "R and Calc", 32/64bit, runs on both Windows 
and Linux.  I have all of them running on VMs of this virtual machine.  I'm now 
moving there.

Sorry I haven't tried the packages/applications suggested.  R is new to me.  I 
started venturing R on about early last month.  I knew R for sometimes before.

There are some other alternative steps running RODBC tried by me which I'm not 
going listing all of them here otherwise the size of this email will be 
increased.

Thanks again for your help.

B.R.
Stephen L





________________________________
From: Ivan Calandra <ivan.calandra@uni-hamburg.de>

Cc: r-help@r-project.org
Sent: Wed, December 1, 2010 6:57:12 PM
Subject: Re: [R] Problem in reading Excel spreadsheets

Hi again,

There are also other packages to read and write xls files, including gdata
(read), dataframe2xls and writeXLS (write), which depend on Perl or Python if 
I'm not mistaken.
I have no idea whether they work on Windows 64 bits.
Visit Crantastic http://crantastic.org/

Ivan

Le 12/1/2010 10:34, Stephen Liu a écrit :
> Hi Ivan,
> 
> Thanks for your advice.
> 
> I have problem running xlsReadWrite on 64 bit Win7.
> 
> > library(xlsReadWrite)
> xlsReadWrite version (cran shlib)
> Copyright (C) 2010 Hans-Peter Suter, Treetron, Switzerland.
> 
> !! Your installation contains the cran placeholder shlib (dll/so).
> Please get the regular shlib (420 KB) by executing the following command:
> 
>    xls.getshlib()
> 
> Info, forum, issue tracker and manual download at http://www.swissr.org.
> 
> BACKGROUND: Our own xlsReadWrite code is free, but we also use proprietary 
code
> (Flexcel, tmssoftware.com) which can only be distributed legally in 
>precompiled,
> i.e. binary form. As CRAN 'generally does not accept submissions of 
precompiled
> binaries due to security reasons' we only provide a placeholder and you can
> download the binary shlib separately. NO GUARANTEES: We have done thorough 
>tests
> initially and there are integrity checks, but we do _not_ give any guarantees.
> You can check/clone the source code at http://github.com/swissr/xlsreadwrite,
> in case of any issues we are happy to hear about them (bug 
>tracker/forum/email).
> 
> > xls.getshlib()
> Loading required package: tools
> --- xls.getshlib running... ---
> Error in xls.getshlib() : currently only windows (32 bit) supported
> 
> xls.getshlib only runs on 32 bit Windows.  I must go back to 32 bit Win 7.
> 
> 
> B.R.
> Stephen L
> 
> ------------------------------------------------------------------------
> *From:* Ivan Calandra <ivan.calandra@uni-hamburg.de>
> *To:* r-help@r-project.org
> *Sent:* Wed, December 1, 2010 5:05:40 PM
> *Subject:* Re: [R] Problem in reading Excel spreadsheets
> 
> Hi,
> 
> I don't know much about RODBC, but the package xlsReadWrite works pretty
> well for me for reading and writing xls files (and it doesn't need Perl
> or anything else to run).
> 
> Ivan
> 
> Le 12/1/2010 08:56, Stephen Liu a écrit :
> > Hi folks,
> >
> > Win 7 64bit
> > R 2.12.0 32bit
> >
> > Problem in reading Excel spreadsheets
> >
> > (the text file, research_databaseI.xls, was download on Internet)
> >
> >
> >> data=odbcConnectExcel(file.choose())
> >> sqlTables(data)
> >                                                  TABLE_CAT TABLE_SCHEM
> > 1  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 2  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 3  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 4  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 5  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 6  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 7  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 8  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 9  C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 10 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 11 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 12 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 13 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 14 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 15 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 16 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 17 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 18 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 19 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 20 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 21 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 22 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> > 23 C:\\Users\\satimiswin764\\Documents\\research_databaseI<NA>
> >                        TABLE_NAME  TABLE_TYPE REMARKS
> > 1                          AGR$ SYSTEM TABLE<NA>
> > 2                          BMC$ SYSTEM TABLE<NA>
> > 3                        Dairy$ SYSTEM TABLE<NA>
> > 4                          GDP$ SYSTEM TABLE<NA>
> > 5                          HIES$ SYSTEM TABLE<NA>
> > 6                        Manuf$ SYSTEM TABLE<NA>
> > 7                        Prices$ SYSTEM TABLE<NA>
> > 8                        Sheet1$ SYSTEM TABLE<NA>
> > 9                          WPI$ SYSTEM TABLE<NA>
> > 10            'Approved plans$'        TABLE<NA>
> > 11                  'Emp stats$'        TABLE<NA>
> > 12                  'Ex rates$'        TABLE<NA>
> > 13            'hotel occ rates$'        TABLE<NA>
> > 14              'Insurance co$'        TABLE<NA>
> > 15            'Interest rates$'        TABLE<NA>
> > 16                    'Motor V$'        TABLE<NA>
> > 17          'Ostrich Projects$'        TABLE<NA>
> > 18                  'Prop dev$'        TABLE<NA>
> > 19                'tourism est$'        TABLE<NA>
> > 20 'tourism est'$_FilterDatabase        TABLE<NA>
> > 21              'Travel agents$'        TABLE<NA>
> > 22              'Umempl stats$'        TABLE<NA>
> > 23                    'US CPI$'        TABLE<NA>
> >
> >> mydata=sqlFetch(data, "AGR")
> >> odbcClose(data)
> >> mydata
> > The printout doesn't look like the content of Excel spreadsheet.  Many data
> > disappear.
> >
> > I read ?RODBC and the pdf file started with "RShowDoc("RODBC",
> > package="RODBC").  I couldn't figure out the cause of problem.
> >
> >
> > Is "RODBC" not the way to read Excel spreadsheets on R?  TIA
> >
> > B.R.
> > Stephen L
> >
> >
> >     [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > R-help@r-project.org <mailto:R-help@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.
> >
> 
> -- Ivan CALANDRA
> PhD Student
> University of Hamburg
> Biozentrum Grindel und Zoologisches Museum
> Abt. Säugetiere
> Martin-Luther-King-Platz 3
> D-20146 Hamburg, GERMANY
> +49(0)40 42838 6231
> ivan.calandra@uni-hamburg.de <mailto:ivan.calandra@uni-hamburg.de>
> 
> **********
> http://www.for771.uni-bonn.de
> http://webapp5.rrz.uni-hamburg.de/mammals/eng/1525_8_1.php
> 
> ______________________________________________
> R-help@r-project.org <mailto:R-help@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.
> 

-- Ivan CALANDRA
PhD Student
University of Hamburg
Biozentrum Grindel und Zoologisches Museum
Abt. Säugetiere
Martin-Luther-King-Platz 3
D-20146 Hamburg, GERMANY
+49(0)40 42838 6231
ivan.calandra@uni-hamburg.de

**********
http://www.for771.uni-bonn.de
http://webapp5.rrz.uni-hamburg.de/mammals/eng/1525_8_1.php


	[[alternative HTML version deleted]]

