Convert XLS to dataset and update database

This should work

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ImportExcellIntoTable()
    End Sub
    Public Sub ImportExcellIntoTable()
        Dim mConn As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Test.mdb")
        Dim mDataAdapter As New OleDb.OleDbDataAdapter
        Dim mCommandBuilder As OleDb.OleDbCommandBuilder
        Dim ds As New DataSet

        ds.Tables.Add(New DataTable("dtbl"))

        ds.Tables("dtbl").Columns.Add(New DataColumn("Id", GetType(Integer)))
        ds.Tables("dtbl").Columns.Add(New DataColumn("Name", GetType(String)))
        ds.Tables("dtbl").Columns.Add(New DataColumn("Test", GetType(String)))

        'read the excell sheet into a dataset
        Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;""")
        Dim da As New OleDb.OleDbDataAdapter("Select * from [MySheet$]", cnn)

        da.Fill(ds, "dtbl")
        ds.Tables( "dtbl").AcceptChanges()


        Try
            mDataAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM AccessTargetTable;", mConn)
            mCommandBuilder = New OleDb.OleDbCommandBuilder(mDataAdapter)
            mDataAdapter.FillSchema(ds, SchemaType.Mapped, "dtbl")

            For Each oDatarow As DataRow In ds.Tables("dtbl").Rows
                oDatarow.SetAdded()
            Next


            mDataAdapter.Update(ds.Tables("dtbl"))
            ds.AcceptChanges()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

vbturbo

Will your code actually not import the empty XLS rows?
I need seperate functions for update and insertion as there may be new employees and there maybe updates.

vbturbo: please could you answer my question?

Has anyone got any solutions on how to not insert loads of empty rows at the bottom of the XLS file?

Thanks

Hi

Access

Id|name|test


Excell first row have these column names =Id|name|test

Id|name|test
1   aa      bb
2   aa      bb  
3   aa      bb



   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ImportExcellIntoTable()
    End Sub
    Public Sub ImportExcellIntoTable()
        Dim mConn As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Test.mdb")
        Dim mDataAdapter As New OleDb.OleDbDataAdapter
        Dim mCommandBuilder As OleDb.OleDbCommandBuilder
        Dim ds As New DataSet

        ds.Tables.Add(New DataTable("dtbl"))

        ds.Tables("dtbl").Columns.Add(New DataColumn("Id", GetType(Integer)))
        ds.Tables("dtbl").Columns.Add(New DataColumn("Name", GetType(String)))
        ds.Tables("dtbl").Columns.Add(New DataColumn("Test", GetType(String)))

        'read the excell sheet into a dataset
        Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;""")
        Dim da As New OleDb.OleDbDataAdapter("Select * from [MySheet$]", cnn)

        da.Fill(ds, "dtbl")
        ds.Tables( "dtbl").AcceptChanges()


        Try
            mDataAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM AccessTargetTable;", mConn)
            mCommandBuilder = New OleDb.OleDbCommandBuilder(mDataAdapter)
            mDataAdapter.FillSchema(ds, SchemaType.Mapped, "dtbl")

            For Each oDatarow As DataRow In ds.Tables("dtbl").Rows
                oDatarow.SetAdded()
            Next


            mDataAdapter.Update(ds.Tables("dtbl"))
            ds.AcceptChanges()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

Then if you want to check for emty rows then iterate through the dataset table and pick a column where you
want to examine if the cell value is empty.

            For Each oDatarow As DataRow In ds.Tables("dtbl").Rows
                if oDatarow(0) = "" then
                  oDatarow.Delete()
               endif
            Next

then delete that row!

vbturbo

Cool I had a feeling the only way to check for empty rows was to check for an empty column value.

Will give this a try when I get round to it and let you know how it goes. Thanks

Sorry, I have not had chance to try the code out yet due to other work.
I will try and test the code in the enxt week :)

Still not got round to this work, due to having to work on other code.
I will reply properly once I have tried the method listed above :)
Sorry for the delay.

set the HDR=NO ' then you dont get the header data

after the adapter has read the xls data into the table then tbl.rows(0).delete()

/vbturbo

clearchannel

You could a least look like you bothered !
Eighter reject the answer or accept it , you know!  someone took his time and replied your question and provided you with a solution.


/Jens

Jens,

I did try to implement your solution with my code but I could not get it work with the application I am working. Also you were busy and I could not get an answer soI had to go look elsewhere to find a solution that I could use as I required when I required it. Non of my code contains any of your solutions. I found other ways round my issues.

With regards to the header and null rows I added in an extra column into the Excel file and checked for certain values to get the desires result.

Sorry you feel agrieved. I can't share the points between myself as the question poster and you, otherwise I would :)

Simon