R help - Jul 2011 - Problem with RODBC

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

David Scott-6 wrote:
> 
> I have been trying to read some data from an Excel workbook without 
> success.
> ...
>  > 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$]'"
> 
> 
I have given up using odbc/Excel without named ranges, but I know it works
sometimes. xlsReadWrite works well for whole sheets, while the gdata/Perl
solutions can be terribly slow (minutes instead of seconds) with large
files.

I had seen the message above before, and it had to do with some invisible
characters in the fields. I managed to get it to work by exporting value of
the sheet, which seems to do a cleanup. Alternatively, a Copy/PasteValue.
After that, my curiosity was satisfied, and I returned to named ranges or
xlsReadWrite.

Dieter





--
View this message in context:
http://r.789695.n4.nabble.com/Problem-with-RODBC-tp3680047p3680108.html
Sent from the R help mailing list archive at Nabble.com.