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 valu开发者_如何学Ce 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?

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",...)).

I also experienced the same issue today. I want update a table in R to SQL-serve. It gives me the exactly same error message. Then I changed all the "Date" type fields to "character" type. I updated again, it worked.

It seems that SQL-server cannot recognize "Date" type variable from R properly.

I've heard of this problem before:

Workaround:

• Use "0" for false,
• Set Paid up as a text field
• Change your application logic to use !=0 for True

I'll try and find you the bug ticket # for you to track

closing this question. There doesn't seem to be a good fix aside from converting to character. I opted to write a command-line program that writes the data to a temporary CSV file, opens Excel, and imports the CSV.