Search code examples
c#.netexceloledbapostrophe

OLEDB, Writing Excel cell without leading apostrophe

I'm writing to Excel file using OLEDB (C#). What I need is just RAW data format.

I've noticed all cells (headers and values) are prefixed by apostrophe (')

Is it a way to avoid adding them in all text cells?

Here is my connection string:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  
 filePath + ";Extended Properties='Excel 8.0;HDR=Yes'";

I've tried use IMEX=1 like this: 

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  
   filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

But after that I'm receiving below error:

The Microsoft Jet database engine could not find the object 'C:\Temp\New Folder\MF_2009_04_19_2008-11-182009_DMBHCSAM1118.xls'.
Make sure the object exists and that you spell its name and the path name correctly.

Finally I've tried use IMEX=0 like this: 

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  
   filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\"";

This time no exeptions raised.

Unfortunately there is still problem with apostrophes (so each my values looks as: '123, 'abc etc...)

Any idea?

Solution

  • http://support.microsoft.com/kb/257819 has a statement to the effect that this behaviour might be unavoidable when inserting text into Excel via ADO:

    A caution about editing Excel data with ADO: When you insert text data into Excel with ADO, the text value is preceded with a single quote. This may cause problems later in working with the new data.

    Is the data explicitly text, might it be coerced into a numeric format? (clutching at straws...)