Hi, I am hoping to use the RODBC package to write a dataframe to an Excel .xlsx file. The dataframe contains at least one field with character elements that exceed 255 bytes, which appears to be the cell width limit in Excel.
Below is example code and the warning message received: library(RODBC) d <- data.frame(v1=c(1,2),v2=c(paste(rep("test",100),collapse=""),"test")) z <- odbcConnectExcel2007("test_rodbc.xlsx",readOnly=FALSE) sqlSave(z,d,tablename="Sheet1",rownames=FALSE) odbcClose(z) Warning message: In odbcUpdate(channel, query, mydata, coldata[m, ], test = test, : character data 'testtesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttest' truncated to 255 bytes in column 'v2' Some search of the R-Help Archives yielded a possible solution, http://tolstoy.newcastle.edu.au/R/help/06/05/28088.html defining the typeInfo argument... typeInfo <- getSqlTypeInfo("EXCEL") typeInfo$character <- "varchar(3000)" z <- odbcConnectExcel2007("test_rodbc.xlsx",readOnly=FALSE) sqlSave(z,d,tablename="Sheet2",rownames=FALSE,typeInfo=typeInfo) odbcClose(z) Error in sqlSave(z, d, tablename = "Sheet2", rownames = FALSE, typeInfo = typeInfo) : 42000 -1506 [Microsoft][ODBC Excel Driver] Size of field 'v2' is too long. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [Sheet2] ("v1" NUMBER, "v2" varchar(3000))' This does not appear to work. Some further investigation into Excel indicates that truncation is a known issue when reading and writing with Excel. One solution offered was to set the number of rows used to determine the datatype in Excel to zero. This does not seem relevant as there are only 2 rows in the example above, which is less than the default (8 rows) Excel appears to use for data typing, and the cell width limit is still an issue. Also, the offending element appeared first in the dataframe, so I assume it was utilized in defining the data type. Any thoughts on how I might get RODBC to work (ideally) or a workaround would be greatly appreciated. Thanks, Steve PS My R Version and System Information are below. > R.Version() $platform [1] "i386-pc-mingw32" $arch [1] "i386" $os [1] "mingw32" $system [1] "i386, mingw32" $status [1] "" $major [1] "2" $minor [1] "13.2" $year [1] "2011" $month [1] "09" $day [1] "30" $`svn rev` [1] "57111" $language [1] "R" $version.string [1] "R version 2.13.2 (2011-09-30)" > Sys.info() sysname release version "Windows" "Vista" "build 6002, Service Pack 2" machine "x86" [[alternative HTML version deleted]] ______________________________________________ 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.