I have been trying to read some data from an Excel workbook without
success. The workbook is in .xls format and has multiple sheets, one
with the sheet name Data, which is the sheet I wish to read from. One
complication is that the header row of this sheet is comprised of
dropdown boxes.
I tried what I normally would do plus some variations. Here is the output.
> require(RODBC)
> options(stringsAsFactors = FALSE)
> fileName <- paste(getwd(),
+ "/../Data/10_11 Quality Threshold Calculations v3.xls",
+ sep = "")
> channel <- odbcConnectExcel(fileName)
> sqlTables(channel)$TABLE_NAME
[1] "Data$"
[2] "PBC$"
[3] "SQL$"
[4] "'10_11 Summary$'"
[5] "'10_11 Summary$'Print_Area"
[6] "'Cust Nos$'"
[7] "Data$_"
[8] "'Diagnostic Pivot$'"
[9] "'Historic summary$'"
[10] "'MED Supporting Evidence$'"
[11] "'MED Supporting Evidence$'Print_Area"
> faults <- sqlFetch(channel, sqtable = 'Data',
+ colnames = FALSE, as.is = TRUE)
> faults
[1] "HY001 -1040 [Microsoft][ODBC Excel Driver] Too many fields defined."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM [Data$]'"
> faults <- sqlFetch(channel, sqtable = 'Data$',
+ colnames = FALSE, as.is = TRUE)
> faults
[1] "HY001 -1040 [Microsoft][ODBC Excel Driver] Too many fields defined."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM [Data$]'"
> faults <- sqlFetch(channel, sqtable = 'Data$_',
+ colnames = FALSE, as.is = TRUE)
> faults
[1] "42S02 -1305 [Microsoft][ODBC Excel Driver] The Microsoft Jet
database engine could not find the object 'Data$_'. Make sure the
object exists and that you spell its name and the path name correctly."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM [Data$_]'"
> odbcCloseAll()
I was able to read the data in using xlsReadWrite by skipping the header
row and specifying the sheet name, so I have a workaround. I would like
to hear any advice on what might be wrong though since usually RODBC has
been extremely reliable. Data is confidential (and in a 14Mb file) so I
can't provide it.
My session info is:
> sessionInfo()
R version 2.13.0 Patched (2011-06-09 r56106)
Platform: i386-pc-mingw32/i386 (32-bit)
locale:
[1] LC_COLLATE=English_New Zealand.1252 LC_CTYPE=English_New Zealand.1252
[3] LC_MONETARY=English_New Zealand.1252 LC_NUMERIC=C
[5] LC_TIME=English_New Zealand.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RODBC_1.3-2 djsmisc_1.0-1
loaded via a namespace (and not attached):
[1] tools_2.13.0
David Scott
--
_________________________________________________________________
David Scott Department of Statistics
The University of Auckland, PB 92019
Auckland 1142, NEW ZEALAND
Phone: +64 9 923 5055, or +64 9 373 7599 ext 85055
Email: d.sc...@auckland.ac.nz, Fax: +64 9 373 7018
______________________________________________
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.
