Reading Excel tables via OLEDB .NET - returns null values

P

Paul

I have written a program that reads an excel spreadsheet
using oledb.

I am able to retrieve about 90% of each line. In some
cases I get null values for cells that should return text
information.

In the code below, the value returned by rs.Item(0) is a
System.DBNull, yet there is data in that column (1).

rs.Item(1) of the same row returns the expect data that
is in column 2.

I do not see any pattern related to formatting. These
are text strings without any special characters. There
may be "formatting" such as cell justification, font
size, bolding, etc.

Can anyone shed some light on why I am getting these null
values?


<Code>
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & OpenFileDialog1.FileNames(filenum)
& ";Extended Properties=""Excel8.0;HDR=NO;MAXSCANROWS=1"""

cn.ConnectionString = strCn
cn.Open()
objCmd.CommandText = "Select * from [new products$]"
objCmd.Connection = cn
Dim rs As OleDbDataReader = objCmd.ExecuteReader
For i = 1 To 4 ' skip lines
rs.Read()
Debug.Write(i.ToString & ", " & rs.Item(0) & "," &
rs.Item(1) & "," & rs.Item(2) & "," & rs.Item(3) & vbCrLf)
Next
 
P

Paul Clement

¤ I have written a program that reads an excel spreadsheet
¤ using oledb.
¤
¤ I am able to retrieve about 90% of each line. In some
¤ cases I get null values for cells that should return text
¤ information.
¤
¤ In the code below, the value returned by rs.Item(0) is a
¤ System.DBNull, yet there is data in that column (1).
¤
¤ rs.Item(1) of the same row returns the expect data that
¤ is in column 2.
¤
¤ I do not see any pattern related to formatting. These
¤ are text strings without any special characters. There
¤ may be "formatting" such as cell justification, font
¤ size, bolding, etc.
¤
¤ Can anyone shed some light on why I am getting these null
¤ values?
¤
¤
¤ <Code>
¤ strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=" & OpenFileDialog1.FileNames(filenum)
¤ & ";Extended Properties=""Excel8.0;HDR=NO;MAXSCANROWS=1"""
¤
¤ cn.ConnectionString = strCn
¤ cn.Open()
¤ objCmd.CommandText = "Select * from [new products$]"
¤ objCmd.Connection = cn
¤ Dim rs As OleDbDataReader = objCmd.ExecuteReader
¤ For i = 1 To 4 ' skip lines
¤ rs.Read()
¤ Debug.Write(i.ToString & ", " & rs.Item(0) & "," &
¤ rs.Item(1) & "," & rs.Item(2) & "," & rs.Item(3) & vbCrLf)
¤ Next
¤

Add IMEX=1 to your connection string to see if the behavior changes:

";Extended Properties=""Excel8.0;HDR=NO;IMEX=1"""

http://support.microsoft.com/default.aspx?scid=kb;en-us;194124


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
P

Paul

Thank you. I was not aware of that article.
-----Original Message-----
¤ I have written a program that reads an excel spreadsheet
¤ using oledb.
¤
¤ I am able to retrieve about 90% of each line. In some
¤ cases I get null values for cells that should return text
¤ information.
¤
¤ In the code below, the value returned by rs.Item(0) is
Click to expand...
a
¤ System.DBNull, yet there is data in that column (1).
¤
¤ rs.Item(1) of the same row returns the expect data that
¤ is in column 2.
¤
¤ I do not see any pattern related to formatting. These
¤ are text strings without any special characters. There
¤ may be "formatting" such as cell justification, font
¤ size, bolding, etc.
¤
¤ Can anyone shed some light on why I am getting these null
¤ values?
¤
¤
¤ <Code>
¤ strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=" & OpenFileDialog1.FileNames (filenum)
¤ & ";Extended Properties=""Excel8.0;HDR=NO;MAXSCANROWS=1"""
¤
¤ cn.ConnectionString = strCn
¤ cn.Open()
¤ objCmd.CommandText = "Select * from [new products$]"
¤ objCmd.Connection = cn
¤ Dim rs As OleDbDataReader = objCmd.ExecuteReader
¤ For i = 1 To 4 ' skip lines
¤ rs.Read()
¤ Debug.Write(i.ToString & ", " & rs.Item(0) & "," &
¤ rs.Item(1) & "," & rs.Item(2) & "," & rs.Item(3) & vbCrLf)
¤ Next
¤

Add IMEX=1 to your connection string to see if the behavior changes:

";Extended Properties=""Excel8.0;HDR=NO;IMEX=1"""

http://support.microsoft.com/default.aspx?scid=kb;en- us;194124


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
.
Click to expand...
Click to expand...
 
M

MikeGledhill

Joined
May 22, 2009
Messages
2
Reaction score
0
Hi Paul,

Paul Clement said:
On Tue, 21 Oct 2003 22:12:14 -0700, "Paul" wrote:

Add IMEX=1 to your connection string to see if the behavior changes:

";Extended Properties=""Excel8.0;HDR=NO;IMEX=1"""

http://support.microsoft.com/default.aspx?scid=kb;en-us;194124

Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...

This is a great tip, and solves half of my problem....

I am actually using SQL Server to create a "Linked Server" to connect to my Excel spreadsheet, and import the data from one of it's Worksheet.

EXEC sp_addlinkedserver 'ExcelLinkedServer',
'Excel','Microsoft.Jet.OLEDB.4.0',
@xlsFilename,
NULL,
'Excel 8.0;IMEX=1',
NULL


Now, it works fine, except for occasions where a column doesn't have any data in the first few rows, but then DOES have data later on (in the same column).

Without your "IMEX=1" addition, it'd create the Linked Server, but every value in that column would be reported as NULL.

With the "IMEX=1" addition, it DOES now retrieve the values where they exist, but gets them in exactly the way they're shown onscreen, rather than the numbers they represent.

So, if I have a column in a Excel worksheet which looks like this

blank
blank
blank
2.3%
1.5%
3.2%





..then without the "IMEX=1", my LinkedServer would return:

(NULL, NULL, NULL, NULL, NULL, NULL)

but with the "IMEX=1", my LinkedServer returns the cell values exactly as they appear on the screen:
(NULL, NULL, NULL, '2.3%', '1.5%', '3.2%',

..but what I need it to do is return the actual values in the cells::
(0, 0, 0, 0.022923981, 0.014920384, 0.032128924)

Is there any way to force the Jet provider to return the actual Excel figures (eg 0.022923981) rather than what's being shown on the screen ("2.3%") ?

It looks as though the "IMEX=1" parameter makes it assume that my column of percentage-formatted figures is a list of text strings.


And, yes, if I load the Excel spreadsheet, go into that worksheet, and put, say, a value of 1 in white text on a white background (so it can't be seen) at the top of the column..

1 (which looks blank to the user)
blank
blank
2.3%
1.5%
3.2%

..then it DOES manage to get the figures correctly:


(1, 0, 0, 0.022923981, 0.014920384, 0.032128924)

But, of course, I don't want to have to force my users to put a row of fake data at the top of their spreadsheets, just to get my Linked Server to work properly !!


Any suggestions ?
 
Last edited:
M

MikeGledhill

Joined
May 22, 2009
Messages
2
Reaction score
0
MikeGledhill said:
Hi Paul,
Blah blah blah
Any suggestions ?
Click to expand...

Many hours later...

I've found the solution:
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

Basically, it requires a Registry change on the server.

My connection string just needs to be this:
'Excel 8.0;IMEX=1'

And, on the SQL Server, I needed to change a registry value in
HKLM/Software/Microsoft/Jet/4.0/Engines/Excel/
"TypeGuessRows"=dword:00000000

Once this registry value was changed, Jet happily imports all my data, in the format I wanted it:
(0, 0, 0, 0.022923981, 0.014920384, 0.032128924)

Note: the MaxScanRows setting doesn't do a thing. Microsoft admits this here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;257819

"However, due to a bug in the ODBC driver, specifying the Rows to Scan (MaxScanRows) setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always scans the first 8 rows in the specified data source in order to determine each column's datatype. "

But, the Registry value change solved my problem.
I'm a very happy man.
 
Last edited:
Top