Alejandro Munoz del Rio
2003-Mar-12 22:05 UTC
[R] avoiding excel's odbc limit on number of columns
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