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