OLEDB, Writing Excel cell without leading apostrophe

Question

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?

Answer 1

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

Answer 2

Could you just use the Excel DSN? It seems to be pretty ubiquitous. I don't know .NET, so take this with a grain of salt, but here's my connection string for an OLEDB Query straight from a stock table:

"Provider=MSDASQL.1;Persist Security Info=True;Extended Properties
=""DSN=Excel Files;DBQ=" & filePath & "\" & fileName &
";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"""

And I used this basic INSERT statement:

INSERT INTO rngOutput VALUES (1, 'ABC', '$1.00', 1300)

When I did this, I didn't have any apostrophes in my data range. I'm also using Excel 2007, and I see you're using Excel 8.0 as your driver?

Hopefully that nudges you toward a solution!

Answer 3

Insert some dummy values for the columns which has apostrophe attached in the template file. Say for example for Name column put something like this dummyname, and age column put 99. Instead of inserting a new row in the template just update the row (Update..... where Name = 'dummyname' and age =99).

This has worked for me..

Hope it works for you also!