Scientific notation when importing from Excel in .Net

One workaround to this issue is to change your select statement, instead of SELECT * do this:

"SELECT Format([F1], 'General Number')  From [Sheet1$]"
 -or-
"SELECT Format([F1], \"#####\")  From [Sheet1$]"

However, doing so will blow up if your cells contain more than 255 characters with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Fortunately my customer didn't care about erroring out in this scenario.

This page has a bunch of good things to try as well: http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

The OleDb library will, more often than not, mess up your data in an Excel spreadsheet. This is largely because it forces everything into a fixed-type column layout, guessing at the type of each column from the values in the first 8 cells in each column. If it guesses wrong, you end up with digit strings converted to scientific-notation. Blech!

To avoid this you're better off skipping the OleDb and reading the sheet directly yourself. You can do this using the COM interface of Excel (also blech!), or a third-party .NET Excel-compatible reader. SpreadsheetGear is one such library that works reasonably well, and has an interface that's very similar to Excel's COM interface.

Using this connection string:

Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"

with Excel 2010 I have noticed the following. If the Excel file is open when you run the OLEDB SELECT then you get the current version of the cells, not the saved file values. Furthermore the string values returned for a long number, decimal value and date look like this:

5.0130370071e+012
4.08
36808

If the file is not open then the returned values are:

5013037007084
£4.08
Monday, October 09, 2000