[R] avoiding excel's odbc limit on number of columns

About this list Date view Thread view Subject view Author view Attachment view

From: Alejandro Munoz del Rio (munoz@stat.wisc.edu)
Date: Thu 13 Mar 2003 - 09:05:18 EST


Message-id: <000c01c2e8e4$009be740$a0566880@surgery.wisc.edu>

Dear R-Helpers,

I would like to read an Excel .xls file via RODBC. I have successfully run
the example in p. 18 of "R Data Import/Export". The problem I am facing is
that Excel's ODBC driver seems to have a limit on the number of
fields/columns (output below). I haven't found any documentation on what
this limit L might be, but I know that 128 <= L < 256.

Does anyone know of a way to avoid "L" via
- the arguments to sqlQuery() or sqlGetResults()?
- an SQL select statement that can subset the columns/fields and index a
range of columns?
- otherwise?

[system info: win98 with 128Mb RAM, R v1.6.1, RODBC v. 1.0-1, excel 2000,
odbc driver v. 03.51; my knowledge of sql = $\epsilon$.]

Gratefully,

alejandro

> #Sheet1 has 92 rows and 256 columns; its structure is roughly as follows:
> # [blank] 0 1 2 ... 254
> # 0 70 70 71 ... 63
> # [89 rows deleted]
> # 90 57 56 52 ... 37
> frame1 <- sqlQuery(channel, "select * from [Sheet1$]")
> frame1
[1] "[RODBC] ERROR: Could not SQLExecute"
[2] "S1001 -1040 [Microsoft][ODBC Excel Driver] Too many fields defined."
> odbcGetInfo(channel)
[1] "EXCEL version 08.00.0000. Driver ODBC version 03.51"
> version
         _
platform i386-pc-mingw32
arch i386
os mingw32
system i386, mingw32
status
major 1
minor 6.1
year 2002
month 11
day 01
language R

______________________________________________
R-help@stat.math.ethz.ch mailing list
https://www.stat.math.ethz.ch/mailman/listinfo/r-help


About this list Date view Thread view Subject view Author view Attachment view

This archive was generated by hypermail 2.1.3 : Tue 01 Jul 2003 - 09:11:25 EST