c# Error inserting to Excel when string data is more than 255 chars

Question

I am trying to export some data to Excel. I am using OLEDB 12. The connectio string looks like:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'"

And I use an INSERT query. But whenever the data in a target column exceeds 255 chars, I get an exception.

Exception Details: System.Data.OleDb.OleDbException: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

There is a similar post on SO: Excel unable to insert more than 255 chars? but it is not into c#,

I also referred to http://support.microsoft.com/kb/213841 and not getting any solution.

Please help.

Answer 1

I was initially thinking that you might be able to define the data type (to memo/text) of the cells before writing the data but this does not appear possible from this article http://support.microsoft.com/kb/278973 (About half way down it explictly states defining the data type in excel is not possible.)

The "best" solution I could offer you is to chop your data into 255 char pieces and insert them into the excel file into nieghbouring columns. from there you could use some excel interop to splice them back together.