Bad record count importing excel spreadsheet to DataAdapter?

[Bmack500]

Hello!
I'm using the code below to import an Excel datasheet into an ado.net
table. The problem is, when I
get to the point where I want to iterate the recods, it always thinks
that there is 999 records in the table! If the table (worksheet) is
larger than that, then it returns an equally fictious number (always
way more).

Does anybody have any idea what I'm doing wrong? I'ts an excel 2003
spreadsheet that I'm reading from. Thanks in advance!


Sub readSheet()
Dim excelConn As New System.Data.OleDb.OleDbConnection


excelConn.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MySpreadSheet.xls;"

& _
"Extended Properties=""Excel
8.0;HDR=Yes;"""
excelConn.Open()
Dim da As New OleDbDataAdapter("Select * From [SheetName$]",
excelConn)
Dim escapeExit As Boolean = False
Dim ds As DataSet = New DataSet()
Dim dc As DataColumn = Nothing
Dim dt As DataTable = Nothing
Dim rec As String = ""
Dim i As Integer = 0
Dim x As Integer = 0
da.Fill(ds)
dt = excelConn.GetSchema


Dim dr As DataRow
While Not Me.bwWorker.CancellationPending
'HERE'S THE PROBLEM: IT ALWAYS DECIDES THAT THERE'S 999
RECORDS!!!
For Each dr In ds.Tables(0).Rows
rec = ""
For x = 0 To UBound(dr.ItemArray)
rec = rec & " " & dr.ItemArray(x).ToString
Next
logIt(txtOutput.Text & ControlChars.CrLf & rec)
'Next
Next
End While
excelConn.Close()
excelConn.Dispose()


End Sub

 

[Paul Clement]

¤ Hello!
¤ I'm using the code below to import an Excel datasheet into an ado.net
¤ table. The problem is, when I
¤ get to the point where I want to iterate the recods, it always thinks
¤ that there is 999 records in the table! If the table (worksheet) is
¤ larger than that, then it returns an equally fictious number (always
¤ way more).
¤
¤ Does anybody have any idea what I'm doing wrong? I'ts an excel 2003
¤ spreadsheet that I'm reading from. Thanks in advance!
¤
¤
¤ Sub readSheet()
¤ Dim excelConn As New System.Data.OleDb.OleDbConnection
¤
¤
¤ excelConn.ConnectionString =
¤ "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=C:\MySpreadSheet.xls;"
¤
¤ & _
¤ "Extended Properties=""Excel
¤ 8.0;HDR=Yes;"""
¤ excelConn.Open()
¤ Dim da As New OleDbDataAdapter("Select * From [SheetName$]",
¤ excelConn)
¤ Dim escapeExit As Boolean = False
¤ Dim ds As DataSet = New DataSet()
¤ Dim dc As DataColumn = Nothing
¤ Dim dt As DataTable = Nothing
¤ Dim rec As String = ""
¤ Dim i As Integer = 0
¤ Dim x As Integer = 0
¤ da.Fill(ds)
¤ dt = excelConn.GetSchema
¤
¤
¤ Dim dr As DataRow
¤ While Not Me.bwWorker.CancellationPending
¤ 'HERE'S THE PROBLEM: IT ALWAYS DECIDES THAT THERE'S 999
¤ RECORDS!!!
¤ For Each dr In ds.Tables(0).Rows
¤ rec = ""
¤ For x = 0 To UBound(dr.ItemArray)
¤ rec = rec & " " & dr.ItemArray(x).ToString
¤ Next
¤ logIt(txtOutput.Text & ControlChars.CrLf & rec)
¤ 'Next
¤ Next
¤ End While
¤ excelConn.Close()
¤ excelConn.Dispose()
¤
¤
¤ End Sub

So what sort of data is in the extra rows? Are they blank?

AFAIK, the Excel driver simply returns what is in the Range of the Worksheet. You may need to limit
the rows returned by specifying column criteria in your SQL statement.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Bmack500]

Yes, the rest of the worksheet is completely blank. How do modify the
SQL statement to not include Null / Blank rows?

 

[Paul Clement]

¤ Yes, the rest of the worksheet is completely blank. How do modify the
¤ SQL statement to not include Null / Blank rows?

Select * From [SheetName$] WHERE ColName IS NOT NULL


Paul
~~~~
Microsoft MVP (Visual Basic)