OleDb Provider for Excel Not Reading Properly

[xenophon]

I am having a problem with an Excel spreadsheet import
using the Jet OLEDB Provider. One of the columns has a "Text" type
in Excel, and the values are all numeric or have a character
in the first position (but they should be read as character).
A sample is
ABC123
3904600098

My connection string looks like this (client is ADO.NET, .NET
Framework
v1.1 on WinXP-SP2):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\....;Extended
Properties="Excel 8.0;HDR=YES;IMEX=0;"



When the "Select *" happens in my code, the data comes across like
this:

null
3904600098.0

Both are unacceptable because the first should not be null and the
second should not be a decimal.


If I change the connection string to this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\....;Extended
Properties="Excel 8.0;HDR=YES;IMEX=1;"


When the "Select *" happens in my code, the data comes across like
this:

ABC123
3.9046e+009

The first value is OK, but the second is unacceptable.


I have set the Regisry Key at HKLM\Software\Microsoft\Jet\4.0\Engines\
Excel\TypeGuessRows to 85 to make sure enough data is being sampled by
the provider. ImportMixedTypes is also set to "Text".


How can I get consistent results for this data read?


Thanks.

 

[Kevin Yu [MSFT]]

Hi xsnophon,

Have you tried to set the cell format in Excel to Text and fill again? I
didn't reproduce this on my computer with a simple .xls file. If you still
have problem after adjusting the cell format, could you attach the Excel
file with you post?

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

 

[xenophon]

I have set the range of cells to Text, saved, and still have the
problem.

I have set the entire column to Text, saved, and still have the
problem.

I have tried all "save as" versions of Excel within Excel 2003, and
still have the problem.

I put an apostrophe (') in front of one of the pure-numeric column
values, and that column imports properly.

It appears that I need some kind of macro to place an apostrophe in
front of every column value.... How do I do that? Is there a Managed
newsgroup for that kind of coding?


Thanks.

 

[Kevin Yu [MSFT]]

Hi,

You can try to post in the following managed newsgroup for Excel
programming issue.

micrisoft.public.excel.programming

HTH.

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