Re: [R-SIG-Mac] RODBC
On Mar 6, 2010, at 12:17 PM, Ivan Calandra wrote: Dear Mac users, Up to now, I've run R on Windows and I have some problems using my scripts on Mac OS X 10.6, R.app GUI 1.31 (5537 Leopard build 64-bit). Here is the code I use to export a data.frame to an *.xls file: xlsFile - odbcConnectExcel(test.xls, readOnly=F) sqlSave(xlsFile, test, tablename=test, rownames=F, addPK=T, append=F) odbcCloseAll() However, the function odbcConnectExcel() doesn't work on Mac. Have you installed the necessary ODBC drivers and registered them? Cheers, Simon Could you help me transposing this code? I don't think some sample data would be necessary, but let me know if you would like to. Thanks in advance Regards, Ivan -- 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/mitarbeiter.php [[alternative HTML version deleted]] ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac
Re: [R-SIG-Mac] RODBC
Hi Ivan, Do you have an Excel ODBC driver? My understanding is that Microsoft does not provide one of OS X, so you may need to buy a driver to get it to work. I know a company called Opensoft makes one. Dan On Sat, 2010-03-06 at 09:17 -0800, Ivan Calandra wrote: Dear Mac users, Up to now, I've run R on Windows and I have some problems using my scripts on Mac OS X 10.6, R.app GUI 1.31 (5537 Leopard build 64-bit). Here is the code I use to export a data.frame to an *.xls file: xlsFile - odbcConnectExcel(test.xls, readOnly=F) sqlSave(xlsFile, test, tablename=test, rownames=F, addPK=T, append=F) odbcCloseAll() However, the function odbcConnectExcel() doesn't work on Mac. Could you help me transposing this code? I don't think some sample data would be necessary, but let me know if you would like to. Thanks in advance Regards, Ivan -- 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/mitarbeiter.php [[alternative HTML version deleted]] -- Dan Putler Sauder School of Business University of British Columbia ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac
Re: [R-SIG-Mac] RODBC
On Mar 6, 2010, at 11:17 AM, Ivan Calandra wrote: Dear Mac users, Up to now, I've run R on Windows and I have some problems using my scripts on Mac OS X 10.6, R.app GUI 1.31 (5537 Leopard build 64-bit). Here is the code I use to export a data.frame to an *.xls file: xlsFile - odbcConnectExcel(test.xls, readOnly=F) sqlSave(xlsFile, test, tablename=test, rownames=F, addPK=T, append=F) odbcCloseAll() However, the function odbcConnectExcel() doesn't work on Mac. Could you help me transposing this code? I don't think some sample data would be necessary, but let me know if you would like to. Thanks in advance Regards, Ivan odbcConnectExcel() is Windows only. The function is conditionally available when the package is installed based upon the OS or in the pre-built binaries. Presuming that you have an ODBC driver installed for Excel on your Mac (either MS with an Office installation, or from Actual Technologies) and a DSN defined, you can use odbcConnect(). If you do not, then you cannot use an ODBC connection. That being said, I am pretty sure that using ODBC you cannot create a new .XLS file, you can only read/write to/from an existing one, which includes appending new worksheets to an existing file. You could use the WriteXLS package on CRAN to write one or more data frames to a new Excel file, where each data frame would be written to a new worksheet. The package requires Perl and Apple's default Perl installation is missing at least one required Perl package (Text::CSV_XS) which can be installed from the command line using CPAN. See the INSTALL file for the package here: http://cran.r-project.org/web/packages/WriteXLS/index.html HTH, Marc Schwartz ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac
Re: [R-SIG-Mac] RODBC
How am I supposed to do that? Marc Schwarz advised me to use the package WriteXLS instead. Thanks for your help already regards, Ivan Le 6 mars 2010 à 18:37, Simon Urbanek a écrit : On Mar 6, 2010, at 12:17 PM, Ivan Calandra wrote: Dear Mac users, Up to now, I've run R on Windows and I have some problems using my scripts on Mac OS X 10.6, R.app GUI 1.31 (5537 Leopard build 64-bit). Here is the code I use to export a data.frame to an *.xls file: xlsFile - odbcConnectExcel(test.xls, readOnly=F) sqlSave(xlsFile, test, tablename=test, rownames=F, addPK=T, append=F) odbcCloseAll() However, the function odbcConnectExcel() doesn't work on Mac. Have you installed the necessary ODBC drivers and registered them? Cheers, Simon Could you help me transposing this code? I don't think some sample data would be necessary, but let me know if you would like to. Thanks in advance Regards, Ivan -- 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/mitarbeiter.php [[alternative HTML version deleted]] ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac -- Ivan CALANDRA PhD Student University of Hamburg Biozentrum Grindel und Zoologisches Institut und Museum 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/mitarbeiter.php ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac
Re: [R-SIG-Mac] RODBC
Ivan, A quick heads up. I had a brief offlist exchange with Prof. Ripley, during which he noted two things: 1. His MS Office 2008 installation on OSX does not have an ODBC driver. So it would seem that it is not available from MS, which would make sense with Actual Tech selling one. 2. The Actual Tech driver is read only, meaning that you can read from an Excel file, but not write to one. This is also noted in Section 7 of vignette(RODBC). After going back to the Actual Tech web site on the page I listed, they do mention this in the text on the page, where it indicates the for Excel, only 'select' statements are supported and for Access, only 'select' and 'join' statements. 'insert' and 'update' queries are not supported. Thus, the Actual Tech product and indeed, [R]ODBC will in fact, not do any of what you want here. Marc On Mar 6, 2010, at 2:01 PM, Ivan Calandra wrote: Thanks a lot Marc for your detailed answer. With this information, I'll find what I can do with my data. Thanks again to all of you Have a nice weekend Ivan Le 6 mars 2010 à 20:14, Marc Schwartz a écrit : Ivan, If you wanted to evaluate the ODBC route, the Actual Tech driver is available here: http://www.actualtech.com/product_access.php It is $29.95 U.S. In hindsight, as I don't use MS Office, I don't know if their OSX offering in fact has an ODBC driver included. Someone else here may know that. FWIW, I use Actual's Oracle ODBC driver and have been pleased with it. I should note that WriteXLS does not facilitate appending new worksheets to an existing file or new data to an existing worksheet. It creates a new .XLS file when used, overwriting an existing file of the same if it already exists. The intent is to provide the means to easily dump one or more data frames to an Excel file as a means to make data from R portable to others where an .XLS file makes sense as the vehicle, as an alternative to CSV files, etc. So the intent would be to have all of your data frames created and then use WriteXLS() to export them at once. I had this need when I was using Linux (to send data to clients on Windows) and so needed to find the way to accomplish this when Excel itself would not be available. Thus Perl was used, which makes this common approach available on Linux, OSX and Windows. Regards, Marc On Mar 6, 2010, at 12:34 PM, Ivan Calandra wrote: How am I supposed to do that? Marc Schwarz advised me to use the package WriteXLS instead. Thanks for your help already regards, Ivan Le 6 mars 2010 à 18:37, Simon Urbanek a écrit : On Mar 6, 2010, at 12:17 PM, Ivan Calandra wrote: Dear Mac users, Up to now, I've run R on Windows and I have some problems using my scripts on Mac OS X 10.6, R.app GUI 1.31 (5537 Leopard build 64-bit). Here is the code I use to export a data.frame to an *.xls file: xlsFile - odbcConnectExcel(test.xls, readOnly=F) sqlSave(xlsFile, test, tablename=test, rownames=F, addPK=T, append=F) odbcCloseAll() However, the function odbcConnectExcel() doesn't work on Mac. Have you installed the necessary ODBC drivers and registered them? Cheers, Simon Could you help me transposing this code? I don't think some sample data would be necessary, but let me know if you would like to. Thanks in advance Regards, Ivan -- 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/mitarbeiter.php [[alternative HTML version deleted]] ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac -- Ivan CALANDRA PhD Student University of Hamburg Biozentrum Grindel und Zoologisches Institut und Museum 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/mitarbeiter.php ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac -- Ivan CALANDRA PhD Student University of Hamburg Biozentrum Grindel und Zoologisches Institut und Museum 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/mitarbeiter.php ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac
Re: [R-SIG-Mac] RODBC
I might suggest looking into the possibility of using SQLite, which is cross platform and free (in fact, it's public domain rather than GNU etc.). You can then set up an Excel spreadsheet to populate itself from the SQLite db. http://www.sqlite.org. It's already available on the Mac - open a terminal window and type sqlite3, and you should enter the environment. Check the website for details of how to set up the database connections. Good luck - Kent On Mar 6, 2010, at 4:18 PM, Erich Neuwirth wrote: Here is another option: On rcom.univie.ac.at We have ROOo, R for OpenOffice. This is an addin for OpenOffice, and it works on OSX. It allows to transfer matrices and dataframes from R to OO. In OO, you then could save your data as Excel files. It cannot be done directly from R, you need to start OO and perhaps write some macros in OO. On Mar 6, 2010, at 9:51 PM, Marc Schwartz wrote: Ivan, A quick heads up. I had a brief offlist exchange with Prof. Ripley, during which he noted two things: 1. His MS Office 2008 installation on OSX does not have an ODBC driver. So it would seem that it is not available from MS, which would make sense with Actual Tech selling one. 2. The Actual Tech driver is read only, meaning that you can read from an Excel file, but not write to one. This is also noted in Section 7 of vignette(RODBC). After going back to the Actual Tech web site on the page I listed, they do mention this in the text on the page, where it indicates the for Excel, only 'select' statements are supported and for Access, only 'select' and 'join' statements. 'insert' and 'update' queries are not supported. Thus, the Actual Tech product and indeed, [R]ODBC will in fact, not do any of what you want here. Marc On Mar 6, 2010, at 2:01 PM, Ivan Calandra wrote: Thanks a lot Marc for your detailed answer. With this information, I'll find what I can do with my data. Thanks again to all of you Have a nice weekend Ivan Le 6 mars 2010 à 20:14, Marc Schwartz a écrit : Ivan, If you wanted to evaluate the ODBC route, the Actual Tech driver is available here: http://www.actualtech.com/product_access.php It is $29.95 U.S. In hindsight, as I don't use MS Office, I don't know if their OSX offering in fact has an ODBC driver included. Someone else here may know that. FWIW, I use Actual's Oracle ODBC driver and have been pleased with it. I should note that WriteXLS does not facilitate appending new worksheets to an existing file or new data to an existing worksheet. It creates a new .XLS file when used, overwriting an existing file of the same if it already exists. The intent is to provide the means to easily dump one or more data frames to an Excel file as a means to make data from R portable to others where an .XLS file makes sense as the vehicle, as an alternative to CSV files, etc. So the intent would be to have all of your data frames created and then use WriteXLS() to export them at once. I had this need when I was using Linux (to send data to clients on Windows) and so needed to find the way to accomplish this when Excel itself would not be available. Thus Perl was used, which makes this common approach available on Linux, OSX and Windows. Regards, Marc On Mar 6, 2010, at 12:34 PM, Ivan Calandra wrote: How am I supposed to do that? Marc Schwarz advised me to use the package WriteXLS instead. Thanks for your help already regards, Ivan Le 6 mars 2010 à 18:37, Simon Urbanek a écrit : On Mar 6, 2010, at 12:17 PM, Ivan Calandra wrote: Dear Mac users, Up to now, I've run R on Windows and I have some problems using my scripts on Mac OS X 10.6, R.app GUI 1.31 (5537 Leopard build 64-bit). Here is the code I use to export a data.frame to an *.xls file: xlsFile - odbcConnectExcel(test.xls, readOnly=F) sqlSave(xlsFile, test, tablename=test, rownames=F, addPK=T, append=F) odbcCloseAll() However, the function odbcConnectExcel() doesn't work on Mac. Have you installed the necessary ODBC drivers and registered them? Cheers, Simon Could you help me transposing this code? I don't think some sample data would be necessary, but let me know if you would like to. Thanks in advance Regards, Ivan -- 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/mitarbeiter.php [[alternative HTML version deleted]] ___ R-SIG-Mac mailing list R-SIG-Mac@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-mac -- Ivan CALANDRA PhD Student University of Hamburg Biozentrum Grindel und Zoologisches Institut und Museum Martin-Luther-King-Platz 3 D-20146 Hamburg, GERMANY +49(0)40 42838 6231
