read from Excel worksheet into dataset

  • Thread starter Thread starter constantin
  • Start date Start date
C

constantin

I have problem with my code I need to read from Excel worksheet into dataset
the problem is in this line oleda.Fill(ds). The data adapter should fill
dataSet with data from Excel worksheet but isn't doing so. Below is my
code,thanks

Imports System.Data.OleDb
Imports System.Data
Imports Oracle.DataAccess.Client

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim connString As String = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\employeetable.xls;Extended Properties=Excel 8.0;"
' Create the connection object

Dim oledbConn As OleDbConnection = New OleDbConnection(connString)

Try
' Open connection
oledbConn.Open()

' Create OleDbCommand object and select data from worksheet Sheet1
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM
[Sheet1$]", oledbConn)

' Create new OleDbDataAdapter
Dim oleda As OleDbDataAdapter = New OleDbDataAdapter()

' Create a DataSet which will hold the data extracted from the
worksheet.
Dim ds As DataSet = New DataSet()

' Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds)

Catch
Finally
' Close connection
oledbConn.Close()
End Try

'1.Create connection object to Oracle database
Dim con As OracleConnection = New OracleConnection()
Try
'2.Specify connection string
con.ConnectionString = ("Data Source=dprod;User Id=mtr;
Password=lar7777")

'3. Open the connection through ODP.NET
con.Open()
Catch ex As Exception
'4.display if any error occurs
MsgBox(ex.Message, Microsoft.VisualBasic.MsgBoxStyle.Exclamation,
"OraScan")

'3.Create command object to perform a query against the database:

Dim cmdQuery As String = "SELECT * FROM employeetable"

' Create the OracleCommand object to work with select
Dim cmd As OracleCommand = New OracleCommand(cmdQuery)
cmd.Connection = con
cmd.CommandType = CommandType.Text

' Dispose OracleCommand object
cmd.Dispose()
Finally

' Close and Dispose OracleConnection object
con.Close()
con.Dispose()

End Try
End Sub
End Class
 
Greetings,

Try it like this:

----------------------------------------------------
Imports System
Imports System.Data.OleDb

Public Class frmExcelStuff

Dim conOle As OleDbConnection
Dim daOle As OleDbDataAdapter, ds As DataSet

Private Sub frmExcelStuff2_Load(...) Handles MyBase.Load

conOle = New OleDbConnection
conOle.ConnectionString
= "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\1A\test1.xls;Extended Properties=Excel 8.0"

daOle = New OleDbDataAdapter
daOle.SelectCommand = New OleDbCommand
daOle.SelectCommand.Connection = conOle

ds = New DataSet

End Sub

Private Sub btnReadFromExcel_Click(...) Handles ...

daOle.SelectCommand.CommandText = "Select * From [Sheet1$]"
daOle.Fill(ds, "tbl1")

'--this is a datagridview I added to the form
dgrv1.DataSource = ds.Tables("tbl1")
End Sub
End Class
---------------------------------------------------

Note: you should do a Sheet1.UsedRange.Select in Excel to see if there
are any empty rows/columns that are part of the sheet's used range. Go
into Tools/Macro/VisualBasic Editor -- add a code module and type

Sub xxxx()
Sheet1.UsedRange.Select
End Sub

Then in Tools/Macros -- run the macro xxxx to select the Sheet's
usedRange.

You should delete these extra rows/columns and then save the Excel file.
Otherwise, you will import a bunch of empty rows/columns into your
dataset which could make it look like you are not retrieving any data.
The method above will import the entire UsedRange of the Excel Sheet.

Rich
 
Back
Top