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.
