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_databaseINA 2 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 3 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 4 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 5 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 6 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 7 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 8 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 9 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 10 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 11 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 12 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 13 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 14 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 15 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 16 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 17 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 18 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 19 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 20 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 21 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 22 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 23 C:\\Users\\satimiswin764\\Documents\\research_databaseINA TABLE_NAME TABLE_TYPE REMARKS 1 AGR$ SYSTEM TABLENA 2 BMC$ SYSTEM TABLENA 3 Dairy$ SYSTEM TABLENA 4 GDP$ SYSTEM TABLENA 5 HIES$ SYSTEM TABLENA 6 Manuf$ SYSTEM TABLENA 7Prices$ SYSTEM TABLENA 8Sheet1$ SYSTEM TABLENA 9 WPI$ SYSTEM TABLENA 10 'Approved plans$'TABLENA 11 'Emp stats$'TABLENA 12 'Ex rates$'TABLENA 13'hotel occ rates$'TABLENA 14 'Insurance co$'TABLENA 15 'Interest rates$'TABLENA 16'Motor V$'TABLENA 17 'Ostrich Projects$'TABLENA 18 'Prop dev$'TABLENA 19'tourism est$'TABLENA 20 'tourism est'$_FilterDatabaseTABLENA 21 'Travel agents$'TABLENA 22 'Umempl stats$'TABLENA 23 'US CPI$'TABLENA 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 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.calan...@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 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.
Re: [R] Problem in reading Excel spreadsheets
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.calan...@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_databaseINA 2 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 3 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 4 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 5 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 6 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 7 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 8 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 9 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 10 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 11 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 12 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 13 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 14 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 15 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 16 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 17 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 18 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 19 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 20 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 21 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 22 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 23 C:\\Users\\satimiswin764\\Documents\\research_databaseINA TABLE_NAME TABLE_TYPE REMARKS 1 AGR$ SYSTEM TABLENA 2 BMC$ SYSTEM TABLENA 3 Dairy$ SYSTEM TABLENA 4 GDP$ SYSTEM TABLENA 5 HIES$ SYSTEM TABLENA 6 Manuf$ SYSTEM TABLENA 7Prices$ SYSTEM TABLENA 8Sheet1$ SYSTEM TABLENA 9 WPI$ SYSTEM TABLENA 10 'Approved plans$'TABLENA 11 'Emp stats$'TABLENA 12 'Ex rates$'TABLENA 13'hotel occ rates$'TABLENA 14 'Insurance co$'TABLENA 15 'Interest rates$'TABLENA 16'Motor V$'TABLENA 17 'Ostrich Projects$'TABLENA 18 'Prop dev$'TABLENA 19'tourism est$'TABLENA 20 'tourism est'$_FilterDatabaseTABLENA 21 'Travel agents$'TABLENA 22 'Umempl stats$'TABLENA 23 'US CPI$'TABLENA 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
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.calan...@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_databaseINA 2 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 3 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 4 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 5 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 6 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 7 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 8 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 9 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 10 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 11 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 12 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 13 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 14 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 15 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 16 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 17 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 18 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 19 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 20 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 21 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 22 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 23 C:\\Users\\satimiswin764\\Documents\\research_databaseINA TABLE_NAME TABLE_TYPE REMARKS 1 AGR$ SYSTEM TABLENA 2 BMC$ SYSTEM TABLENA 3Dairy$ SYSTEM TABLENA 4 GDP$ SYSTEM TABLENA 5 HIES$ SYSTEM TABLENA 6Manuf$ SYSTEM TABLENA 7Prices$ SYSTEM TABLENA 8Sheet1$ SYSTEM TABLENA 9 WPI$ SYSTEM TABLENA 10'Approved plans$'TABLENA 11 'Emp stats$'TABLENA 12 'Ex rates$'TABLENA 13'hotel occ rates$'TABLENA 14 'Insurance co$'TABLENA 15'Interest rates$'TABLENA 16'Motor V$'TABLENA 17 'Ostrich Projects$'TABLENA 18 'Prop dev$'TABLENA 19'tourism est$'TABLENA 20 'tourism est'$_FilterDatabaseTABLENA 21 'Travel agents$'TABLENA 22 'Umempl stats$'TABLENA 23'US CPI
Re: [R] Problem in reading Excel spreadsheets
On Wed, Dec 1, 2010 at 2:56 AM, Stephen Liu sati...@yahoo.com wrote: Hi folks, Win 7 64bit R 2.12.0 32bit Problem in reading Excel spreadsheets There are quite a few alternatives for reading Excel spreadsheets. They are listed here: http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com __ 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.
Re: [R] Problem in reading Excel spreadsheets
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_RDemoDevNA 2 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDevNA 3 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDevNA 4 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDevNA 5 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDevNA 6 C:\\Users\\satimiswin764\\Documents\\copy_of_RDemoDevNA TABLE_NAME TABLE_TYPE REMARKS 1 Graphics$ SYSTEM TABLENA 2 Interactive$ SYSTEM TABLENA 3 MyDataframe$ SYSTEM TABLENA 4gggTABLENA 5mypivotTABLENA 6 'Pivot table$'TABLENA mydata=sqlFetch(data, MyDataframe) odbcClose(data) mydata Gender Weight Size SizeM SizeF F6 F7 1# 1m 68 183 177 178 NA NA NA 2m 80 184 160 176 NA NA NA 3f 62 173 171 175 NA NA NA 4f 55 165 164 185 NA NA 2 5f 60 165 165 190 NA NA NA 6m 70 185NA 185 NA NA 3 7m 81 179 165 175 NA NA NA 8m 93 185 173 184 NA NA NA 9f 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.calan...@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.calan...@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
[R] Problem in reading Excel spreadsheets
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_databaseINA 2 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 3 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 4 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 5 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 6 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 7 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 8 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 9 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 10 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 11 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 12 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 13 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 14 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 15 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 16 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 17 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 18 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 19 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 20 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 21 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 22 C:\\Users\\satimiswin764\\Documents\\research_databaseINA 23 C:\\Users\\satimiswin764\\Documents\\research_databaseINA TABLE_NAME TABLE_TYPE REMARKS 1 AGR$ SYSTEM TABLENA 2 BMC$ SYSTEM TABLENA 3 Dairy$ SYSTEM TABLENA 4 GDP$ SYSTEM TABLENA 5 HIES$ SYSTEM TABLENA 6 Manuf$ SYSTEM TABLENA 7Prices$ SYSTEM TABLENA 8Sheet1$ SYSTEM TABLENA 9 WPI$ SYSTEM TABLENA 10 'Approved plans$'TABLENA 11 'Emp stats$'TABLENA 12 'Ex rates$'TABLENA 13'hotel occ rates$'TABLENA 14 'Insurance co$'TABLENA 15 'Interest rates$'TABLENA 16'Motor V$'TABLENA 17 'Ostrich Projects$'TABLENA 18 'Prop dev$'TABLENA 19'tourism est$'TABLENA 20 'tourism est'$_FilterDatabaseTABLENA 21 'Travel agents$'TABLENA 22 'Umempl stats$'TABLENA 23 'US CPI$'TABLENA 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 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.
