C# Excel INSERT Error using OleDb and the Access Database Engine

Question

All, there are many question on this subject but none solve my problem. I have written a fairly involved routine to export a passed `DataSet`/`DataTable` to Excel (I have Office 2010 running under Win7) using OleDb and the Access Database Engine. The problem is no matter how I define the columns to be written to Excel all values are exported as `TEXT/STRING` fields.

I am using the `OleDbConnection` string

    string fileName = @"F:\SomePath\MyExcel.xlsx";
    string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
        {0};Extended Properties=""Excel 12.0 Xml;HDR=YES;MaxScanRows=0;IMEX=0""", fileName);

and have tried numerous other [connection string options](http://www.connectionstrings.com/excel-2007) with no luck.

I generate the Excel definitions in code, but explicitly these are produced as

    CREATE TABLE [MetaTab] ([Table] TEXT,[Field] TEXT,[Seq] NUMBER,[DataLevel] NUMBER)

I then generate the code for inserts, for the example above this is
 
    INSERT INTO [MetaTab$]([Table],[Field],[Seq],[DataLevel])VALUES('B1A','EstabID','1','9')

This works, but all values are written as TEXT. **How can I force Excel to take other data formats?**

Note: I have tried removing the apostrophes for non-string but this does not work either. I am genuinely stuck and any ideas would be greatly appreciated. Thanks for your time.

 

---

"Everything should be made as simple as possible, but not simpler" - Einstein

Answer 1

The data will always be wrtten as text.  Depending of the formats on each column will determine how excel processes the data.  If you make all the columns general format then excel will attempt to convert the text to another fomat like Number, or currency.  Excel often make errors in determining the data type so using a format other than general get better results.  I think what you are getting is all Text columns and you should change the format after the exporting is done.

I thought there may be a method of specifying a schema since your file is XML.  I didn't find anything but I found an interesting trick.  See this webpage

http://weblogs.asp.net/fmarguerie/archive/2003/10/01/29964.aspx

---

jdweng