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