[R] RODBC, Excel, and data truncation when writing

[Uwe Ligges]

On 29.05.2012 17:27, Steven Lacey wrote:
> 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...


MySQL != Excel


Uwe Ligges



> 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 at 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.