Accessing Excel thru Jet gives data in scientific notation

  • Thread starter Thread starter 1SALz
  • Start date Start date
1

1SALz

Hi,

I m trying to access an excel spreadsheet to load a named cells in a
dataset. For this, I m using Jet OLEDB provider with ADO.Net.

String cnString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +
fileName + "; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";

String sql = "select * from MyNamedCells";


Initially I was having problem with null values for certain cells where
datatype appeared different to the provider. e.g. If a top cells of a
column contain text data (say A13331123) and in the middle there was some
numeric data (4509015284) .. the numeric data was being retrieved as null.
This was fixed using IMEX switch.

Now - althought the numeric data is being loaded, but it is coming in
scientific notation (the above value converts to: 4.50902e+009).

How can I get my text back with the right accuracy?

Thanks.
 
Hi,

I think that the value you are getting is the right value.
Excel uses smart display (your value is small enough - so Excel rounds it to
0 for displaying).
Just round it when you display it - like Excel.
 
Hi 1SALz,

It is by design that Excel will convert the numeric data to scientific
notation automatically. The workaround is select this column as a string
field. You can try to use the following statement to convert it to a string
data.

SELECT str(id) FROM table11

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks.
I tried it (select str(F1) from myNamedCells) but that gave the following
error:

System.InvalidOperationException: The provider could not determine the
String value. For example, the row was just created, the default for the
String column was not available, and the consumer had not yet set a new
String value.
at System.Data.OleDb.DBBindings.get_Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
Boolean acceptChanges)
at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
mapping)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at project1.WebForm1.myfunction(String fileName) in
c:\inetpub\wwwroot\webform1.aspx.cs:line 70
 
A few more tries ... but without success.
select F1, F2, F3, iif(isnumeric(f3),val(F3),f3) from myNamedCells

Although now the code works without error, but the values are losing
precision.

Original Value in Excel: 4509015281
Default Loaded Value: 4.50902e+009
Converted Value by val : 4509020000

The problem looks like the value is being read as text (in scientific
notation) from the source.

Any ideas ?


1SALz said:
Thanks.
I tried it (select str(F1) from myNamedCells) but that gave the following
error:

System.InvalidOperationException: The provider could not determine the
String value. For example, the row was just created, the default for the
String column was not available, and the consumer had not yet set a new
String value.
at System.Data.OleDb.DBBindings.get_Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
Boolean acceptChanges)
at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
mapping)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at project1.WebForm1.myfunction(String fileName) in
c:\inetpub\wwwroot\webform1.aspx.cs:line 70


Kevin Yu said:
Hi 1SALz,

It is by design that Excel will convert the numeric data to scientific
notation automatically. The workaround is select this column as a string
field. You can try to use the following statement to convert it to a string
data.

SELECT str(id) FROM table11

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
Click to expand...
Click to expand...
 
I'd think that your column is being interpeted as a float value vs a
double and what you are seeing is the truncation of significant digits from
this. Unfortunately I'm not sure how you convince OleDb to read Excel cells
in a specific type.

Ron Allen
1SALz said:
A few more tries ... but without success.
select F1, F2, F3, iif(isnumeric(f3),val(F3),f3) from myNamedCells

Although now the code works without error, but the values are losing
precision.

Original Value in Excel: 4509015281
Default Loaded Value: 4.50902e+009
Converted Value by val : 4509020000

The problem looks like the value is being read as text (in scientific
notation) from the source.

Any ideas ?


1SALz said:
Thanks.
I tried it (select str(F1) from myNamedCells) but that gave the following
error:

System.InvalidOperationException: The provider could not determine the
String value. For example, the row was just created, the default for the
String column was not available, and the consumer had not yet set a new
String value.
at System.Data.OleDb.DBBindings.get_Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
Boolean acceptChanges)
at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
mapping)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at project1.WebForm1.myfunction(String fileName) in
c:\inetpub\wwwroot\webform1.aspx.cs:line 70


Kevin Yu said:
Hi 1SALz,

It is by design that Excel will convert the numeric data to scientific
notation automatically. The workaround is select this column as a string
field. You can try to use the following statement to convert it to a string
data.

SELECT str(id) FROM table11

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
Click to expand...
Click to expand...
Click to expand...
 
Hi 1SALz,

STR() only works when the data type of the whole column is numeric, if
not, an exception will be thown. You can also try the following SQL
statement:

SELECT CONVERT(char(15), a) FROM table22

For more information, please refer to SQL Book Online.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top