Re: [R] Import from excel 2007
Now my Windows machine is back online again, I have been able to do some work on this. You can download the latest ODBC drivers from download.microsoft.com (select Office as the product, search for ODBC and you will get to AccessDatabaseEngine.exe). You don't need any version of Office installed to make use of this. RODBC_1.2-2, which will be available shortly, has convenience wrappers odbcConnectExcel2007 and odbcConnectAccess2007 to make use of those drivers. However, if you use this sort of thing at all frequently I suggests setting up a UserDSN with the paths and file types you use, and using odbcConnect on that. On Wed, 17 Oct 2007, Marc Schwartz wrote: > On Wed, 2007-10-17 at 20:53 +0200, kees wrote: >> For me, it works. That is to say, not the simple way, which gave the error: >> odbcConnectExcel("C:\\Users\\Kees\\Desktop\\Map1.xlsx") >> >> But it does when you ask the correct driver >> odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, >> *.xlsb);DBQ=C:\\Users\\Kees\\Desktop\\Map1.xlsx; ReadOnly=False") >> >> It works correctly. >> >> Cheers, >> Kees > > > > Thanks Kees. > > If I am reading the RODBC source correctly, in file win.R, there is the > following: > > if(.Platform$OS.type == "windows") { >## originally based on suggestions from [EMAIL PROTECTED] >odbcConnectExcel <- function(xls.file, readOnly = TRUE, ...) >{ >full.path <- function(filename) { >fn <- chartr("\\", "/", filename) >is.abs <- length(grep("^[A-Za-z]:|/", fn)) > 0 >chartr("/", "\\", > if(!is.abs) file.path(getwd(), filename) > else filename) >} >con <- if(missing(xls.file)) >"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" >else { >fp <- full.path(xls.file) >paste("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=", > fp, ";DefaultDir=", dirname(fp), ";", sep = "") >} > if(!readOnly) con = paste(con, "ReadOnly=False", sep=";") >odbcDriverConnect(con, tabQuote=c("[", "]"), ...) >} > > > > > > It would appear that the defacto name for the ODBC driver has changed in > 2007 from prior versions. Thus, it would seem that some additional > checking is needed here for compatibility with Office 2007 when using > this function. > > Not running on Windows, I would defer to Prof. Ripley as to his > preferred approach here. > > I would guess that the same would be the case for odbcConnectAccess(). > > In the other case, using odbcDriverConnect(), you are explicitly naming > the driver, so it works. > > So for the time being, using odbcDriverConnect() would be the approach > to use. > > HTH, > > Marc > > -- Brian D. Ripley, [EMAIL PROTECTED] Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UKFax: +44 1865 272595 __ 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] Import from excel 2007
"Earl F. Glynn" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'll try the odbcDriverConnect and let you know. Thanks. odbcDriverConnect worked fine for me, too, with new Excel 2007 formats: library(RODBC) channel <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);DBQ=G:/RODBC/TimeOrdering.xlsx; ReadOnly=True") d <- sqlFetch(channel,"Sheet1", as.is=TRUE) odbcClose(channel) names(d) I haven't checked, but the same trick is likely needed for Access 2007 using RODBC. On Windows Vista with Office 2007, the ODBC Data Source Adminstrator shows two drivers for Access: Microsoft Access Driver (*.mdb) Microsoft Access Driver (*.mdb, *.accdb). In Access 2007, the new database extension is .accdb instead of .mdb. efg __ 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] Import from excel 2007
"Marc Schwartz" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > This might be a question along the lines of "is it plugged in?", but do > you have the Office 2007 version of the ODBC drivers installed? > > If not, then your results would not be a surprise. > > If you do, then we should get Prof. Ripley involved here, as we did have > a brief offlist communication on this yesterday, thinking that RODBC > "should" work if the proper version of the ODBC drivers are installed. ... "Marc Schwartz" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I would guess that the same would be the case for odbcConnectAccess(). > > In the other case, using odbcDriverConnect(), you are explicitly naming > the driver, so it works. > > So for the time being, using odbcDriverConnect() would be the approach > to use. I was still exploring this when your newer post appeared. I installed everything from the Microsoft Office 2007 Professional DVD back in March. I assumed all the ODBC drivers were installed but don't really know. In Control Panel | Administrative Tools | Data Sources (ODBC) Under drivers: Driver do Microsoft Excel (*.xls) 6.00.6000.16386 Microsoft Corporation ODBCJT32.dll 11/2/2006 Microsoft Excel Driver (*.xls) 6.00.6000.16386 Microsoft Corporation ODBCJT32.dll 11/2/2006 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) 6.00.6000..16386 Microsoft Corporation ACEODBC.DLL 10/26/2006 Microsoft Excel-Treiber (*.xls) 12.00.4518.1014Microsoft Corporation ACEODBC.DLL 10/26/2006 I'll try the odbcDriverConnect and let you know. efg __ 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] Import from excel 2007
On Wed, 2007-10-17 at 20:53 +0200, kees wrote: > For me, it works. That is to say, not the simple way, which gave the error: > odbcConnectExcel("C:\\Users\\Kees\\Desktop\\Map1.xlsx") > > But it does when you ask the correct driver > odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, > *.xlsb);DBQ=C:\\Users\\Kees\\Desktop\\Map1.xlsx; ReadOnly=False") > > It works correctly. > > Cheers, > Kees Thanks Kees. If I am reading the RODBC source correctly, in file win.R, there is the following: if(.Platform$OS.type == "windows") { ## originally based on suggestions from [EMAIL PROTECTED] odbcConnectExcel <- function(xls.file, readOnly = TRUE, ...) { full.path <- function(filename) { fn <- chartr("\\", "/", filename) is.abs <- length(grep("^[A-Za-z]:|/", fn)) > 0 chartr("/", "\\", if(!is.abs) file.path(getwd(), filename) else filename) } con <- if(missing(xls.file)) "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" else { fp <- full.path(xls.file) paste("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=", fp, ";DefaultDir=", dirname(fp), ";", sep = "") } if(!readOnly) con = paste(con, "ReadOnly=False", sep=";") odbcDriverConnect(con, tabQuote=c("[", "]"), ...) } It would appear that the defacto name for the ODBC driver has changed in 2007 from prior versions. Thus, it would seem that some additional checking is needed here for compatibility with Office 2007 when using this function. Not running on Windows, I would defer to Prof. Ripley as to his preferred approach here. I would guess that the same would be the case for odbcConnectAccess(). In the other case, using odbcDriverConnect(), you are explicitly naming the driver, so it works. So for the time being, using odbcDriverConnect() would be the approach to use. HTH, Marc __ 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] Import from excel 2007
For me, it works. That is to say, not the simple way, which gave the error: odbcConnectExcel("C:\\Users\\Kees\\Desktop\\Map1.xlsx") But it does when you ask the correct driver odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);DBQ=C:\\Users\\Kees\\Desktop\\Map1.xlsx; ReadOnly=False") It works correctly. Cheers, Kees Op Wed, 17 Oct 2007 19:45:11 +0200 schreef Marc Schwartz <[EMAIL PROTECTED]>: > On Wed, 2007-10-17 at 11:53 -0500, Earl F. Glynn wrote: >> "Marc Schwartz" <[EMAIL PROTECTED]> wrote in message >> news:[EMAIL PROTECTED] >> > On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: >> >> Hey Seniors, >> >> >> >> Really new to R, please has anyone imported a dataset from MS >> office >> >> excel 2007 into R yet? It seem to be giving me hard times which I did >> >> not expect as it use to go well with the previous versions. >> >> >> >> Thanks in advance. >> >> arnoldo >> > >> > >> > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet >> > compatible with most applications (including OpenOffice), since MS >> does >> > not document it. >> > >> > The best thing to do is to save the Excel file as a .CSV file and then >> > use read.csv() to import the data. >> >> Your question made me curious about RODBC and Excel, so I ran a test >> using >> Excel 2007. >> >> Using RODBC (Version 1.2-1) with R 2.6.0 with Windows Vista, I cannot >> read >> any of the new Excel formats, which there are three: >> >> .xlsx - Excel Workbook >> .xlsb - Excel Binary Workbook >> .xlsm - Excel Macro-Enable Workbook >> >> I get the same error for all three: "ODBC Excel Driver Logic Failed: >> External table is not in the expected format." >> >> When saving new Excel 2007 files, you can select "Save As" and "Excel >> 97-2003 Workbook" and use those .xls files like before with RODBC in R. >> >> But be careful using RODBC and Excel, such as this problem: >> >> RODBC and Excel: Wrong Data Type Assumed on Import >> http://tolstoy.newcastle.edu.au/R/help/05/11/14938.html > > This might be a question along the lines of "is it plugged in?", but do > you have the Office 2007 version of the ODBC drivers installed? > > If not, then your results would not be a surprise. > > If you do, then we should get Prof. Ripley involved here, as we did have > a brief offlist communication on this yesterday, thinking that RODBC > "should" work if the proper version of the ODBC drivers are installed. > > Let us know. > > Thanks, > > Marc > > __ > 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. > > -- Gemaakt met Opera's revolutionaire e-mailprogramma: http://www.opera.com/mail/ __ 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] Import from excel 2007
On Wed, 2007-10-17 at 11:53 -0500, Earl F. Glynn wrote: > "Marc Schwartz" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: > >> Hey Seniors, > >> > >> Really new to R, please has anyone imported a dataset from MS office > >> excel 2007 into R yet? It seem to be giving me hard times which I did > >> not expect as it use to go well with the previous versions. > >> > >> Thanks in advance. > >> arnoldo > > > > > > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet > > compatible with most applications (including OpenOffice), since MS does > > not document it. > > > > The best thing to do is to save the Excel file as a .CSV file and then > > use read.csv() to import the data. > > Your question made me curious about RODBC and Excel, so I ran a test using > Excel 2007. > > Using RODBC (Version 1.2-1) with R 2.6.0 with Windows Vista, I cannot read > any of the new Excel formats, which there are three: > > .xlsx - Excel Workbook > .xlsb - Excel Binary Workbook > .xlsm - Excel Macro-Enable Workbook > > I get the same error for all three: "ODBC Excel Driver Logic Failed: > External table is not in the expected format." > > When saving new Excel 2007 files, you can select "Save As" and "Excel > 97-2003 Workbook" and use those .xls files like before with RODBC in R. > > But be careful using RODBC and Excel, such as this problem: > > RODBC and Excel: Wrong Data Type Assumed on Import > http://tolstoy.newcastle.edu.au/R/help/05/11/14938.html This might be a question along the lines of "is it plugged in?", but do you have the Office 2007 version of the ODBC drivers installed? If not, then your results would not be a surprise. If you do, then we should get Prof. Ripley involved here, as we did have a brief offlist communication on this yesterday, thinking that RODBC "should" work if the proper version of the ODBC drivers are installed. Let us know. Thanks, Marc __ 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] Import from excel 2007
"Marc Schwartz" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: >> Hey Seniors, >> >> Really new to R, please has anyone imported a dataset from MS office >> excel 2007 into R yet? It seem to be giving me hard times which I did >> not expect as it use to go well with the previous versions. >> >> Thanks in advance. >> arnoldo > > > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet > compatible with most applications (including OpenOffice), since MS does > not document it. > > The best thing to do is to save the Excel file as a .CSV file and then > use read.csv() to import the data. Your question made me curious about RODBC and Excel, so I ran a test using Excel 2007. Using RODBC (Version 1.2-1) with R 2.6.0 with Windows Vista, I cannot read any of the new Excel formats, which there are three: .xlsx - Excel Workbook .xlsb - Excel Binary Workbook .xlsm - Excel Macro-Enable Workbook I get the same error for all three: "ODBC Excel Driver Logic Failed: External table is not in the expected format." When saving new Excel 2007 files, you can select "Save As" and "Excel 97-2003 Workbook" and use those .xls files like before with RODBC in R. But be careful using RODBC and Excel, such as this problem: RODBC and Excel: Wrong Data Type Assumed on Import http://tolstoy.newcastle.edu.au/R/help/05/11/14938.html efg Earl F. Glynn Scientific Programmer Stowers Institute for Medical Research __ 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] Import from excel 2007
Quoting Scionforbai <[EMAIL PROTECTED]>: >> It would be dumb to ignore the fact that Excel is a very widespread >> program, and therefore in the real world we are very likely to >> encounter data formatted by Excel. > > Of course I know the widespreading of such programs. But the point is: > how can we start to change this in the real world? People using > proprietary formats are annoying, even more if they don't even think > about it and don't know what a format is. > >> I use R as a means to an end, as a tool to solve real world problems, >> not out of a religious idealism. > > I think this is a very important point. Whatever critic to the "real > world habits" one can move, it is said to be religious idealism (or at > least geeky attitude). > There are technical and philosophical reasons for discouraging using > proprietary software/formats (Marc Schwartz provided a link which > points out some about spreadshits - yes, intentional ;) ). This is > true even if you aren't communist, anarchist, or GNUliban. > The fact that in your real life you solve real problems doesn't mean > that you should not ask yourself which means you use and why, nor > discussing about others'choices. "Because everyone out there does the > same" is not, for me, a satisfying answer. And I just wanted to > discuss about that, in the hope that maybe someone, reading these few > messages, would start asking himself the same question. > Regards, > s. Ah! You're really talking about trying to change the world! Now I understand. ;-) It's not a trivial task, and whilst I tell everyone who wants to listen how great R is, and I have turn a handful of people towards R, I am not sure I want to become a hardcore campaigner! Hence my comment about being "means to an end". Doing what everybody else does is not a good reason to do things, in an ideal world. Just because most people choose one way it doesn't make it the best. Truth is not democratic. But life is a lot easier if we adapt to existing trends, and use *all* the tools available to our advantage. regards, Jose -- Dr. Jose I. de las Heras Email: [EMAIL PROTECTED] The Wellcome Trust Centre for Cell BiologyPhone: +44 (0)131 6513374 Institute for Cell & Molecular BiologyFax: +44 (0)131 6507360 Swann Building, Mayfield Road University of Edinburgh Edinburgh EH9 3JR UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. __ 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] Import from excel 2007
> It would be dumb to ignore the fact that Excel is a very widespread > program, and therefore in the real world we are very likely to > encounter data formatted by Excel. Of course I know the widespreading of such programs. But the point is: how can we start to change this in the real world? People using proprietary formats are annoying, even more if they don't even think about it and don't know what a format is. > I use R as a means to an end, as a tool to solve real world problems, > not out of a religious idealism. I think this is a very important point. Whatever critic to the "real world habits" one can move, it is said to be religious idealism (or at least geeky attitude). There are technical and philosophical reasons for discouraging using proprietary software/formats (Marc Schwartz provided a link which points out some about spreadshits - yes, intentional ;) ). This is true even if you aren't communist, anarchist, or GNUliban. The fact that in your real life you solve real problems doesn't mean that you should not ask yourself which means you use and why, nor discussing about others'choices. "Because everyone out there does the same" is not, for me, a satisfying answer. And I just wanted to discuss about that, in the hope that maybe someone, reading these few messages, would start asking himself the same question. Regards, s. __ 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] Import from excel 2007
Quoting Scionforbai <[EMAIL PROTECTED]>: > I just wonder: why should R and its community try to support such an > awful program, with its protected formats and unmantained > features/bugs? > I mean, from both philosophical and technical point of view: R is free > software and should rather try to be 'viral' than to compete. It > already has the strength, in my humble opinion. > > You want to use excel: go and use, you payed for it, so you have a > commercial support elsewhere. You are not able to communicate with > other applications? That's the fault of excel, not of R, which is free > software and uses well documented formats. It would be dumb to ignore the fact that Excel is a very widespread program, and therefore in the real world we are very likely to encounter data formatted by Excel. I use R as a means to an end, as a tool to solve real world problems, not out of a religious idealism. Jose -- Dr. Jose I. de las Heras Email: [EMAIL PROTECTED] The Wellcome Trust Centre for Cell BiologyPhone: +44 (0)131 6513374 Institute for Cell & Molecular BiologyFax: +44 (0)131 6507360 Swann Building, Mayfield Road University of Edinburgh Edinburgh EH9 3JR UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. __ 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] Import from excel 2007
> > The best way to read or write Excel files is with the RExcel package. The Well, I'd argue, that often 'the best way' is to use the xlsReadWrite package, but I am biased... There are some precondition: - you have to be on windows (but my new soon-to-arrive notebook will be a Mac, therefore...) - it reads and write in the Excel 97-2003 xls format (BIFF8). (With Excel 2007 you'd have to choose the compatibility mode for now and thus fall back to the 65536 rows and 256 columns limit). Regards, Hans-Peter [[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.
Re: [R] Import from excel 2007
Excel 2007 xlsx files are zip files that contain XML files which define the spreadsheet -- its all readable text. Create an Excel 2007 xlsx file, rename its extension to .zip, unzip it and you can look at all the constituent files using any text editor or your browser. On 10/16/07, Marc Schwartz <[EMAIL PROTECTED]> wrote: > On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: > > Hey Seniors, > > > > Really new to R, please has anyone imported a dataset from MS office > > excel 2007 into R yet? It seem to be giving me hard times which I did > > not expect as it use to go well with the previous versions. > > > > Thanks in advance. > > arnoldo > > > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet > compatible with most applications (including OpenOffice), since MS does > not document it. > > The best thing to do is to save the Excel file as a .CSV file and then > use read.csv() to import the data. > > Alternatively, save the file in the older .xls format, which will enable > you to use the CRAN packages with functions that support it. > > It is possible that Prof. Ripley's RODBC package might work, but I don't > recall any posts confirming that. > > HTH, > > Marc Schwartz > > __ > 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. > __ 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] Import from excel 2007
On 10/16/07, Marc Schwartz <[EMAIL PROTECTED]> wrote: > On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: > > Hey Seniors, > > > > Really new to R, please has anyone imported a dataset from MS office > > excel 2007 into R yet? It seem to be giving me hard times which I did > > not expect as it use to go well with the previous versions. > > > > Thanks in advance. > > arnoldo > > > Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet > compatible with most applications (including OpenOffice), since MS does > not document it. It is documented, but the spec is ~1,200 pages so implementing it is the hard part. Hadley -- http://had.co.nz/ __ 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] Import from excel 2007
On 17/10/2007, at 7:52 AM, Alberto Monteiro wrote: > Scionforbai wrote: >> >> I just wonder: why should R and its community try to support >> such an awful program, with its protected formats and unmantained >> features/bugs? >> > By such logic, why should R be ported to Windows? :-) > > Short answer: because some of the data you want to use > is writen in Excel by people who only knows how to write > Excel spreadshits I hope this was intentional!!! It certainly is appropriate. :-) cheers, Rolf Turner ## Attention:\ This e-mail message is privileged and confid...{{dropped:9}} __ 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] Import from excel 2007
Scionforbai wrote: > > I just wonder: why should R and its community try to support > such an awful program, with its protected formats and unmantained > features/bugs? > By such logic, why should R be ported to Windows? :-) Short answer: because some of the data you want to use is writen in Excel by people who only knows how to write Excel spreadshits, and you want to automate the reading and interpretation of such data so that you, as a R user, does not have to use Excel to convert the data to text. Alberto Monteiro __ 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] Import from excel 2007
R's developers have made the right decision. Like it or not, my company colleagues and "customers" provide me most data in Excel. I want to use R to analyze/plot/etc the data, and sometimes to provide them R applications to do the analyses themselves instead of the current Excel analyses (sic) that they do. I am not paid to tell them to go look elsewhere for help (especially when it can be difficult to convince them to work with me in the first place -- but that's a much more difficult "cultural" issue). Like it or not, Excel is the most widely used data analytical/graphics package in the world. In the business world, we have to deal with such realities. Bert Gunter Genentech Nonclinical Statistics -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scionforbai Sent: Tuesday, October 16, 2007 11:37 AM To: [EMAIL PROTECTED]; r-help@r-project.org Subject: Re: [R] Import from excel 2007 I just wonder: why should R and its community try to support such an awful program, with its protected formats and unmantained features/bugs? I mean, from both philosophical and technical point of view: R is free software and should rather try to be 'viral' than to compete. It already has the strength, in my humble opinion. You want to use excel: go and use, you payed for it, so you have a commercial support elsewhere. You are not able to communicate with other applications? That's the fault of excel, not of R, which is free software and uses well documented formats. __ 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. __ 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] Import from excel 2007
I just wonder: why should R and its community try to support such an awful program, with its protected formats and unmantained features/bugs? I mean, from both philosophical and technical point of view: R is free software and should rather try to be 'viral' than to compete. It already has the strength, in my humble opinion. You want to use excel: go and use, you payed for it, so you have a commercial support elsewhere. You are not able to communicate with other applications? That's the fault of excel, not of R, which is free software and uses well documented formats. __ 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] Import from excel 2007
On Tue, 2007-10-16 at 19:21 +0200, Scionforbai wrote: > > Such workarounds should normally be avoided. > > You forgot to mention: Excel should normally be avoided. > > Risk of scrambling data while exporting to a simple ascii formatted text file? > Is it a joke? No. Excel is notorious for this. See: http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html HTH, Marc Schwartz __ 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] Import from excel 2007
> Such workarounds should normally be avoided. You forgot to mention: Excel should normally be avoided. Risk of scrambling data while exporting to a simple ascii formatted text file? Is it a joke? __ 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] Import from excel 2007
The best way to read or write Excel files is with the RExcel package. The development package available from http://sunsite.univie.ac.at/rcom/download/devel/RExcel.installer_1.80-14.zip reads both Excel 2003 and Excel 2007 files. In this development release, the installer is structured as an ordinary R package for R on Windows. It installs an Excel addin and includes complete documentation. RExcel provides good two-way communication between R and Excel. The normal method of reading an Excel file is to open the file in Excel and then send the worksheet to R from the Excel right-click menu. Changing formats from Excel to .CSV or anything else has the risk of scrambling data. Such workarounds should normally be avoided. Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Arnold Akem Sent: Tuesday, October 16, 2007 09:38 AM To: r-help@r-project.org Subject: [R] Import from excel 2007 Hey Seniors, Really new to R, please has anyone imported a dataset from MS office excel 2007 into R yet? It seem to be giving me hard times which I did not expect as it use to go well with the previous versions. Thanks in advance. arnoldo - [[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. __ 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] Import from excel 2007
On Tue, 2007-10-16 at 14:38 +0100, Arnold Akem wrote: > Hey Seniors, > > Really new to R, please has anyone imported a dataset from MS office > excel 2007 into R yet? It seem to be giving me hard times which I did > not expect as it use to go well with the previous versions. > > Thanks in advance. > arnoldo Office 2007 is using a new format (.xlsx, .docx, etc.) that is not yet compatible with most applications (including OpenOffice), since MS does not document it. The best thing to do is to save the Excel file as a .CSV file and then use read.csv() to import the data. Alternatively, save the file in the older .xls format, which will enable you to use the CRAN packages with functions that support it. It is possible that Prof. Ripley's RODBC package might work, but I don't recall any posts confirming that. HTH, Marc Schwartz __ 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] Import from excel 2007
Please read the Posting Guide and provide reproducible code. What do you mean by "import"? -- via RODBC?... as a delimited text file? ... using read.table(), scan(),... ??? Bert Gunter Genentech Nonclinical Statistics -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Arnold Akem Sent: Tuesday, October 16, 2007 6:38 AM To: r-help@r-project.org Subject: [R] Import from excel 2007 Hey Seniors, Really new to R, please has anyone imported a dataset from MS office excel 2007 into R yet? It seem to be giving me hard times which I did not expect as it use to go well with the previous versions. Thanks in advance. arnoldo - [[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. __ 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.