RODBC Fails: "Invalid Character Value for Casting Specification" - Excel 2007

Question

I am trying to use RODBC to write to an Excel2007 file and I keep getting errors. I brought the problem to this most basic case: a 1-line data.frame with character, numeric, dates and logical data types:

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 ) 

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 the logical columns to a character, everything works fine. The problem is that they are now characters in Excel and cannot be used as intended data types without conversion. I dig into sqlSave code and it seems to be doing the right thing. Has anyone else encountered this problem?

Answer 1

closing this question. There seems to be no good solution other than conversion to character. I decided to write a program from the command line that writes data to a temporary CSV file, opens Excel, and imports the CSV.

Answer 2

For someone who stumbles upon this (after 5 years), in R you can use the varTypes argument in sqlSave() as sqlSave(..., varTypes = c(somecolname="datetime", anothercolname= "datetime",...)) .

Answer 3

Today I also had the same question. I want to update a table in R to an SQL service. This gives me exactly the same error message. Then I changed all the fields of type "Date" to "character". I updated again, it worked.

It seems that the SQL server cannot recognize the variable of type “Date” from R correctly.

Answer 4

I heard about this problem before:

Temporary solution:

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

I will try to find you a ticket with # error so that you can track