RE: OleDB query to Excel returning Null values for numeric data

[Ben Reese]

Many thanks for your speedy reply Curtis. That worked a treat!

The extended property I required was IMEX=1
My connection string now looks like this:
Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=myFile.xls;Extended Properties=""Excel 8.0; IMEX=1""")

Thanks

"Curtis Deems [MSFT]" wrote:

> This KB articles provides more details about using mixed types with Excel:
> http://support.microsoft.com/kb/257819/
>
> Go down to the section "Considerations That Apply to Both OLE DB Providers
> A Caution about Mixed Data Types". It discusses what to expect when using
> mixed types.
>
> Hope that helps...
>
> -Curtis
>
>
> "Ben Reese" wrote:
>
> > Hi
> >
> > I have an OleDB connection to Excel like this:
> > Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=myFile.xls;Extended Properties=Excel 8.0")
> >
> > and a select command like this:
> > Dim _com As New OleDb.OleDbCommand("SELECT * FROM Sheet1$")
> >
> > All pretty straight forward, but...
> > Some of the values returned for a particular row in the resultant dataset
> > have null values. Now, If I open the spreadsheet I can see values in all rows
> > for this field, but some of the rows are formatted as number and some are
> > formatted as text with a preceeding apostrophe. Excel gives a message next to
> > these "The number in this cell is formatted as text or proceeded by an
> > apostrope". Clicking the floating message removes the appostrophe and formats
> > the cell as a number. It is the cells formatted as numbers that are returning
> > Null! The text formated numbers are returning the correct data.
> >
> > My guess is that I need to specify something in the Extended Pproperties of
> > the connection string. But what please?
> >
> > Incedentally. I have tried importing the spread sheet into SQL Server using
> > the impoprt data wizard and this suffers the same problem.
> >
> > Many thanks
> >
> > Ben
> >
> >
> >

[Todd]

Ben,
I am using the IMEX = 1 extended properties in my vb.net (VSTO 2003) code
and it is not over riding the the mixed type. Here is the following code:

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")

' Select the data from Sheet1 of the workbook.
sItemSQL = " Select * "
sItemSQL += " From [item charts$]"

MyCommand = New System.Data.OleDb.OleDbDataAdapter(sItemSQL,
MyConnection)

DS = New System.Data.DataSet
MyCommand.Fill(DS, "Item")

The result is giving only numeric information on my parts column and blank
when the part number starts with an alpha numeric. Is there anything else I
have to do to get this to recogonize my mixed type column?

Thanks,

Todd

[Paul Clement]

On Thu, 18 Oct 2007 07:40:02 -0700, Todd <To...@discussions.microsoft.com> wrote:

¤ Ben,

¤ I am using the IMEX = 1 extended properties in my vb.net (VSTO 2003) code
¤ and it is not over riding the the mixed type. Here is the following code:
¤
¤ MyConnection = New System.Data.OleDb.OleDbConnection( _
¤ "provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=" & sFile & ";" & _
¤ "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
¤
¤ ' Select the data from Sheet1 of the workbook.
¤ sItemSQL = " Select * "
¤ sItemSQL += " From [item charts$]"
¤
¤ MyCommand = New System.Data.OleDb.OleDbDataAdapter(sItemSQL,
¤ MyConnection)
¤
¤ DS = New System.Data.DataSet
¤ MyCommand.Fill(DS, "Item")
¤
¤ The result is giving only numeric information on my parts column and blank
¤ when the part number starts with an alpha numeric. Is there anything else I
¤ have to do to get this to recogonize my mixed type column?

¤

Probably will require a change to the Excel Registry setting TypeGuessRows:

http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

Paul
~~~~
Microsoft MVP (Visual Basic)

[Todd]

Paul,
Thanks for responding. It seems like this is an issue with a lot of folks
out there. I will not have access to edit everyone's registry. This would
not be a viable solution for myself. Any other solution that can be done
without editing any local machine settings? I hate to open the excel file
and read it line by line. Any other ideas?

Thanks,

Todd

[Paul Clement]

On Fri, 19 Oct 2007 07:06:03 -0700, Todd <To...@discussions.microsoft.com> wrote:

¤ Paul,

¤ Thanks for responding. It seems like this is an issue with a lot of folks
¤ out there. I will not have access to edit everyone's registry. This would
¤ not be a viable solution for myself. Any other solution that can be done
¤ without editing any local machine settings? I hate to open the excel file
¤ and read it line by line. Any other ideas?

¤

Unfortunately I'm not aware of another workaround for this.

[Yalin Meric]

First of all thanks for that useful information. I'd like to add a caution
about numeric fields in Excel though. The oledb adapter tends to convert
large numeric values to E representation (i.e. 4906945 to 4.906945E+006) and
this creates a mass if you are transferring the numeric data to a text field
(i.e. phone number which may be written as +90(312)4906945 or simply 4906945
depending on users choice). Therefore if you are willing to have the exact
text entered to a cell in Excel you should set cell content type to Text
before entering and transferring data.