[R] Re ading Excel 5.0 files with RODBC?

David Scott d.scott at auckland.ac.nz
Wed Apr 8 04:31:53 CEST 2009


On Tue, 7 Apr 2009, Yuri Volchik wrote:

>
> Hi,
>
> i'm trying to read some data from excel files but it seems that neither
> xlsReadWrite nor sqlFetch (RODBC) doesn't like the format (Excel 5.0).
> When i open the file in Excel and save it in a new format Excel 97 -2003
> everything works fine.
> Is it possible to use ODBC connection to open old format files, or i guess i
> will have to open and save every file in Excel in new format, which isn't
> very practical.
>

Your question is a bit confusing. This is the current state of play 
regarding reading and writing Excel files with xlsReadWrite and RODBC.

xlsReadWrite will read and write from Excel 97-2003 files (.xls files) 
including reading from named sheets, but only writing a single sheet 
workbook.

RODBC can be used to read both Excel 97-2003 files and the latest version 
of Excel files (.xlsx) files. It can be used to update these files too I 
believe but I have not tested that. Here follows tested code (examples 
given to my students) showing how to read from various Excel files. The 
example builds on the example provided by Hans-Peter Suter on the R-wiki 
page concerning reading and writing from Excel.

Note that in the following bikesWithDate.xls is an Excel 97-2003 file 
obtained from bikes.xls by formatting the Date column as an Excel date. In 
addition the table has been made into a named range for use with RODBC. 
bikesWithDate.xlsx is the same file but saved in .xlsx format

The code illustrates date conversions also.

### Using xlsReadWrite
### Load xlsReadWrite
library(xlsReadWrite)
tdat <- data.frame(Price = c(6399,3699,2499),
                    Amount = c(2,3,1),
                    Date = c(39202,39198,39199),
                    row.names = c("Pro machine","Road racer", "Streetfire"))
### Write
write.xls(tdat, "bikes.xls")

### Read and check
bikes1 <- read.xls(file = "bikes.xls")
bikes1
class(bikes1[,"Date"])

### Read as data.frame (custom colnames, date as iso-string)
bikes2 <- read.xls(file = "bikes.xls",
                    colNames = c("","CHF","Number","Date"), from = 2,
                    colClasses = c("numeric","numeric","isodate"))
bikes2
class(bikes2[,"Date"])
### Date is actually of class character
### Convert Date to class Date
bikes2[,"Date"] <- as.Date(bikes2[,"Date"])
bikes2[,"Date"]
class(bikes2[,"Date"])
### Convert Date to class POSIXct
bikes2[,"Date"] <- as.POSIXct(bikes2[,"Date"])
bikes2[,"Date"]
class(bikes2[,"Date"])

### Read Excel file with date column
bikes3 <- read.xls("bikesWithDate.xls", dateTimeAs = "isodate")
bikes3
class(bikes3[,"Date"])
### Date is of class character---needs to be converted to a date class

### Try with RODBC
library(RODBC)
bikes4 <- sqlFetch(odbcConnectExcel("bikes.xls"), sqtable = "Sheet1",
                    na.strings = "NA", as.is = TRUE)
bikes4
str(bikes4)
class(bikes4[,"Date"])
### Dates are actually in Excel format, number of days since 1899-12-30
bikes4 <- sqlFetch(odbcConnectExcel("bikes.xls"), sqtable = "Sheet1",
                    na.strings = "NA", as.is = TRUE)
bikes4[,"Date"] <- as.Date(bikes4[,"Date"], origin = "1899-12-30")
bikes4[,"Date"]
class(bikes4[,"Date"])

### Try with formatted date
bikes5 <- sqlFetch(odbcConnectExcel("bikesWithDate.xls"),
                    sqtable = "DateAsDate",
                    na.strings = "NA", as.is = TRUE)
bikes5
class(bikes5[,"Date"])
### Convert to Date class??
bikes5[,"Date"] <- as.Date(bikes5[,"Date"])
bikes5[,"Date"]
class(bikes5[,"Date"])

### Use a named range
channel <- odbcConnectExcel("bikesWithDate.xls")
bikes6 <- sqlQuery(channel, "SELECT * FROM DataRange",
                     na.strings = "NA", as.is = TRUE)
bikes6
class(bikes6[,"Date"])
bikes6[,"Date"] <- as.Date(bikes6[,"Date"])
bikes6[,"Date"]
class(bikes6[,"Date"])

### Read from .xlsx file
channel <- odbcConnectExcel2007("bikesWithDate.xlsx")
bikes7 <- sqlQuery(channel, "SELECT * FROM DataRange",
                     na.strings = "NA", as.is = TRUE)
bikes7
class(bikes7[,"Date"])
bikes7[,"Date"] <- as.Date(bikes7[,"Date"])
bikes7[,"Date"]
class(bikes7[,"Date"])

### Clean up
odbcCloseAll()


David Scott


_________________________________________________________________
David Scott	Department of Statistics
 		The University of Auckland, PB 92019
 		Auckland 1142,    NEW ZEALAND
Phone: +64 9 373 7599 ext 85055		Fax: +64 9 373 7018
Email:	d.scott at auckland.ac.nz

Graduate Officer, Department of Statistics
Director of Consulting, Department of Statistics




More information about the R-help mailing list