OLEDB, writing an Excel cell without leading apostrophe

Question

I am writing to an Excel file using OLEDB (C #). I only need the RAW data format.

I noticed that all cells (headers and values) have an apostrophe prefix (')

Is this a way to avoid adding them to 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 tried using IMEX = 1 as follows:

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

But after that I get 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 that the object exists, and you correctly name its name and path name.

Finally, I tried using IMEX = 0 as follows:

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

There were no special comments this time.

Unfortunately, there is still a problem with apostrophes (so each of my values ​​looks like this: '123,' abc, etc.)

Any idea?

Answer 1

http://support.microsoft.com/kb/257819 contains a statement that this behavior may be unavoidable when pasting text into Excel via ADO:

Warning about changing Excel data using ADO . When you paste text data into Excel using ADO, the text value is preceded by a single quote. This can cause problems when working with new data.

Is the text explicitly textual, can it be forcibly entered into a numerical format? (clutching on straws ...)

Answer 2

Could you use Excel DSN? It seems to be pretty ubiquitous. I don't know .NET, so grab this with salt, but here is my connection string for OLEDB Query right from the 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 had no apostrophes in my data range. I also use Excel 2007, and see that you are using Excel 8.0 as your driver?

Hope this pushes you to a decision!

Answer 3

Insert some dummy values ​​for columns that have an apostrophe attached to the template file. Say, for example, something like this dummyname is placed for the Name column, and age column put 99. Instead of inserting a new line into the template, just update the line (Update ..... where Name = 'dummyname' and age = 99).

It worked for me ..

Hope this works for you too!

Answer 4

Remove IMEX = 1 from the connection string.

http://www.connectionstrings.com/excel

Answer 5

I know that when entering data into Excel, prefixing it with an apostrophe is an easy way to turn it into a text field. Are you sure that the data does not actually contain an apostrophe? If it is added to the data at the time of entry, the only option is to trick it during import and access it in some special code.

Answer 6

Check the registry Hkey_Local_Machine / Software / Microsoft / Jet / 4.0 / Engines / Excel / TypeGuessRows

This determines how many rows to scan before deciding on the format for the column. The default is 8, and 0 will cause ADO to scan all column values ​​before selecting the appropriate data type.

Answer 7

This may not meet your requirements, especially if you need to configure formatting on an Excel worksheet, but have you thought about writing data to a CSV file and saving it with the .XLS extension?

Another attempt you could try would be to explicitly set the format data types of your target cells to an Excel worksheet to print “Text” (as through “Format”> “Cells in Excel”) before trying to load data. By default, the cells will be of type "General", and the driver can add an apostrophe to make your data appear in the text.

Answer 8

Try the following procedure to resolve the problem. Change the template according to the instructions

• The first row of data is just below the header row. Format the columns in the required format.
• Enter some dummy values, such as a space for characters, 0 for numeric values, etc.
• Hide the first row of data containing dummy values ​​and save the template

Now run your paste script using ADO.net

-Venkat Kolla