[R] Problem with RODBC

[David Scott]

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.scott at auckland.ac.nz,  Fax: +64 9 373 7018