Accessing Excel thru Jet gives data in scientific notation

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.

Miha
Fri Nov 21 01:52:57 CST 2003

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.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"1SALz" <salman_z_g@hotmail.com> wrote in message
news:%230RVY28rDHA.2416@TK2MSFTNGP10.phx.gbl...
> 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.
>
>

v-kevy
Fri Nov 21 03:00:00 CST 2003

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

1SALz
Fri Nov 21 11:25:11 CST 2003

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 [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:FhPpc3AsDHA.3024@cpmsftngxa07.phx.gbl...
> 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."
>

1SALz
Fri Nov 21 13:31:05 CST 2003

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" <salman_z_g@hotmail.com> wrote in message
news:u3$q$SFsDHA.2252@TK2MSFTNGP09.phx.gbl...
> 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 [MSFT]" <v-kevy@online.microsoft.com> wrote in message
> news:FhPpc3AsDHA.3024@cpmsftngxa07.phx.gbl...
> > 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."
> >
>
>

Ron
Sat Nov 22 09:18:26 CST 2003

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" <salman_z_g@hotmail.com> wrote in message
news:OTHPfqGsDHA.2360@TK2MSFTNGP09.phx.gbl...
> 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" <salman_z_g@hotmail.com> wrote in message
> news:u3$q$SFsDHA.2252@TK2MSFTNGP09.phx.gbl...
> > 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 [MSFT]" <v-kevy@online.microsoft.com> wrote in message
> > news:FhPpc3AsDHA.3024@cpmsftngxa07.phx.gbl...
> > > 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."
> > >
> >
> >
>
>

v-kevy
Mon Nov 24 00:57:43 CST 2003

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