RODBC fails: "invalid character value for cast specification" - Excel 2007
I'm trying to use RODBC to write to an Excel2007 file and I keep getting errors. I've reduced the issue to this very basic case, a 1-row data.frame with character, numeric, Date, and logical datatypes:
toWrite = data.frame( Name = "joe" , Date = as.Date("2011-01-01"), Value = 2 , Paid = FALSE )
xlFile = odbcConnectExcel2007( "REPLACE_WITH_XLSB_FILE_PATH" , readOnly = FALSE )
sqlSave( xlFile , toWrite , tablename = "worksheet1" , rownames = FALSE )
The error:
Error in sqlSave(xlFile, toWrite, tablename = "worksheet1", rownames = FALSE) :
[RODBC] Failed exec in Update
22018 39 [Microsoft][ODBC Excel Driver]Invalid character value for cast specification
In addition: Warning message:
In odbcUpdate(channel, query, mydata, coldata[m, ], test = test, :
character data 'FALSE' truncated to 1 bytes in column 'Paid'
If I convert both the Date and logical columns to character then everything works fine. The issue is that these are now characters in Excel and can't be used as the intended data-types without conversion. I dug into the sqlSave code and it seems to be doing the right things. Has anyone else encountered this problem?
Answer
For Anyone stumbling on this (5 years later), in R you can use the varTypes argument in sqlSave() like sqlSave(..., varTypes = c(somecolname="datetime", anothercolname= "datetime",...)).