RODBC fails: "invalid character value for cast specification" - Excel 2007

Question

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?

Scrobiculate

I can confirm the problem, but I never succeeded in writing Excel with RODBC. Could be that is has to do with the inverse problem: reading Excel only works reliably with named ranges. I always use XLConnect for all Excel-related stuff now. – Palaeobotany

Thanks, Dieter. It seems there's good support for talking to Excel if you are willing to take a dependency on Java. I can't justify it just to get R->Excel to work because we don't use java in-house. Otherwise it would be a no-brainer. =) – Scrobiculate

7 years after the problem is still there, but new options were presented in the function, but anyways, the function is unbelievably slow for larger dataframes so practically the best way still is exporting to a CSV file and then importing to Excel – Alcantara

Answer 1

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.

Scrobiculate

Answer 2

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

Tungus

I found that you also have to add the option sqlSave(..., fast = FALSE). Just using fast = FALSE without specifying varTypes caused dates to be reduced to year only. – Frisian

Needed this today! Thank you very much – Oilstone

Thanks both of you, was tearing my hair out over this! – Crisscross

Answer 3

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.

Strasbourg

Answer 4

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

Keister

Correct. As I mentioned in my post if I convert to character everything works fine. I disagree with the third bullet. I think its bad practice to change app logical to make a particular output work. Its better to get the output/writer code to do what its supposed to do. – Scrobiculate

I agree, in a perfect world, that is what should happen. However, when systems don't work as designed, often these type of hacks are useful – Keister

+1 for helping me relise that RODBC requires manual data type manipulation – Stoneman