Solved

Convert XLS to dataset and update database

Posted on 2008-10-31
16
1,030 Views
Last Modified: 2011-10-19
I am trying to upload an XLS spreadsheet put it into a dataset and then loop through the dataset and update my database.

At the moment I can populate the Gridview1 with the xls BUT it  gets all the EMPTY rows from the xlas as well as the data rows, which is no good!

Another issue is that i keep getting System.IO.IOException errors due to the file bing in use all the time when the Subroutine fails.

And the final issue is with the id/RosterID field. If there is a new entry in the xls spreadsheet there is no id field and so I default it to 0 unless there is a value for id, but there is an issue here in that it says it's null but I am not understanding why


If FileUpload1.HasFile Then
            FileUpload1.PostedFile.SaveAs(Server.MapPath("/roster/temp/rosterimport.xls"))
 
            'View in grid
            'Dim objDataAdapter As New OleDbDataAdapter()
            'objDataAdapter.SelectCommand = ExcelConnection()
            'Dim objDataSet As New DataSet()
            'objDataAdapter.Fill(objDataSet)
            'GridView1.DataSource = objDataSet.Tables(0).DefaultView
            'GridView1.DataBind()
            'GridView1.Visible = True
            'objDataAdapter.Dispose()
            'objDataSet.Dispose()
 
 
           Dim objDataAdapter As New OleDbDataAdapter()
            objDataAdapter.SelectCommand = ExcelConnection()
            Dim dsRosterUpdate As New DataSet()
            objDataAdapter.Fill(dsRosterUpdate)
            Dim i As Integer = 0
 
            While i < dsRosterUpdate.Tables(0).Rows.Count
                Dim RosterID As Integer = dsRosterUpdate.Tables(0).Rows(i)("id")
                Dim FirstName As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("first_name"))
                Dim LastName As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("last_name"))
                Dim JobTitle As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("job_title"))
                Dim Department As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("department"))
                Dim Market As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("market"))
                Dim BusinessUnit As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("business_unit"))
                Dim Division As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("division"))
                Dim Email As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("email"))
                Dim Tel As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("tel"))
                Dim DirectTel As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("direct_tel"))
                Dim Fax As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("fax"))
                Dim Mobile As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("mobile"))
                Dim Address As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("address"))
                Dim Assistant As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("assistant"))
                Dim AssistantTel As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("assistant_tel"))
                Dim Languages As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("languages"))
                Dim OtherInfo As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("other_info"))
 
                ImportLabel.Text &= RosterID & " " & LastName & " " & FirstName & " " & JobTitle & " " & Department & " " & Market & " " & BusinessUnit & " " & Division & " " & Email & " " & Tel & " " & DirectTel & " " & Fax & " " & Mobile & "<br>" & Address & " " & Assistant & " " & AssistantTel & " " & Languages & " " & OtherInfo & "<hr>"
 
                If RosterID <> 0 Then
                    ImportLabel.Text &= "<font color='Blue'>Roster Update!</font><br>"
                    'UpdateRoster(RosterID, LastName, FirstName, JobTitle, Department, Market, BusinessUnit, Division, Email, Tel, DirectTel, Fax, Mobile, Address, Assistant, AssistantTel, Languages, OtherInfo)
                Else
                    ImportLabel.Text &= "<font color='green'>Roster Insert!</font><br>"
                    'AddToRoster(LastName, FirstName, JobTitle, Department, Market, BusinessUnit, Division, Email, Tel, DirectTel, Fax, Mobile, Address, Assistant, AssistantTel, Languages, OtherInfo)
                End If
 
                i += 1
            End While
            
        End If
    End Sub
 
    Protected Function ExcelConnection() As OleDbCommand
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/roster/temp/rosterimport.xls") & ";Extended Properties=Excel 8.0;"
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open()
        Dim objCommand As New OleDbCommand("SELECT * FROM [Roster$]", objXConn)
        Return objCommand
    End Function

Open in new window

0
Comment
Question by:clearchannel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
16 Comments
 

Author Comment

by:clearchannel
ID: 22865608
Do any .net gurus have any ideas on my issues? :(
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 22925849
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
0
 

Author Comment

by:clearchannel
ID: 22932072
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.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:clearchannel
ID: 23054474
vbturbo: please could you answer my question?
0
 

Author Comment

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

Thanks
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 23343680
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
0
 

Author Comment

by:clearchannel
ID: 23352174
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
0
 

Author Comment

by:clearchannel
ID: 23570157
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 :)
0
 

Author Comment

by:clearchannel
ID: 23712146
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.
0
 

Accepted Solution

by:
clearchannel earned 0 total points
ID: 23845669
I've just come back to this issue and unfortunately that code doesn't do as I need it to.
How do I stop the first 2 rows of my XLS file from being imported as they cause contain column headings and instructions and are cuasing issues?
How do I loop through the dataset created form the xlas file and call the function to either insert, update or delete as in my code below? the READ method is causing an issue on the while loop?

im objDataAdapter As New OleDbDataAdapter()
            objDataAdapter.SelectCommand = ExcelConnection()
            Dim dsRosterUpdate As New DataSet()
            objDataAdapter.Fill(dsRosterUpdate)
            Dim i As Integer = 0
 
            Try
                While dsRosterUpdate.Tables(0).Rows(i).Read()
                    Dim RosterID As Integer
                    Dim FirstName As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("first_name"))
                    Dim LastName As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("last_name"))
                    Dim JobTitle As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("job_title"))
                    Dim Department As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("department"))
                    Dim Market As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("market"))
                    Dim BusinessUnit As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("business_unit"))
                    Dim Division As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("division"))
                    Dim Email As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("email"))
                    Dim Tel As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("tel"))
                    Dim DirectTel As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("direct_tel"))
                    Dim Fax As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("fax"))
                    Dim Mobile As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("mobile"))
                    Dim Address As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("address"))
                    Dim Assistant As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("assistant"))
                    Dim AssistantTel As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("assistant_tel"))
                    Dim Languages As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("languages"))
                    Dim OtherInfo As String = Convert.ToString(dsRosterUpdate.Tables(0).Rows(i)("other_info"))
 
                    If dsRosterUpdate.Tables(0).Rows(i)("id").ToString <> "" Then
                        RosterID = dsRosterUpdate.Tables(0).Rows(i)("id")
                    Else
                        RosterID = Nothing
                    End If
 
                    ImportLabel.Text &= "Details: " & RosterID & " " & LastName & " " & FirstName & " " & JobTitle & " " & Department & " " & Market & " " & BusinessUnit & " " & Division & " " & Email & " " & Tel & " " & DirectTel & " " & Fax & " " & Mobile & "<br>" & Address & " " & Assistant & " " & AssistantTel & " " & Languages & " " & OtherInfo & "<hr>"
 
                    If LCASE(dsRosterUpdate.Tables(0).Rows(i)("Action").ToString) = "u" Then
                        ImportLabel.Text &= "<font color='Blue'>Roster Update!</font><br>"
                        'UpdateRoster(RosterID, LastName, FirstName, JobTitle, Department, Market, BusinessUnit, Division, Email, Tel, DirectTel, Fax, Mobile, Address, Assistant, AssistantTel, Languages, OtherInfo)
                    ElseIf LCASE(dsRosterUpdate.Tables(0).Rows(i)("Action").ToString) = "n" Then
                        ImportLabel.Text &= "<font color='green'>Roster Insert!</font><br>"
                        'AddToRoster(LastName, FirstName, JobTitle, Department, Market, BusinessUnit, Division, Email, Tel, DirectTel, Fax, Mobile, Address, Assistant, AssistantTel, Languages, OtherInfo)
                    ElseIf LCASE(dsRosterUpdate.Tables(0).Rows(i)("Action").ToString) = "d" Then
                        ImportLabel.Text &= "<font color='Red'>Roster DELETE!</font><br>"
                        'DeleteFromRoster(LastName, FirstName, JobTitle, Department, Market, BusinessUnit, Division, Email, Tel, DirectTel, Fax, Mobile, Address, Assistant, AssistantTel, Languages, OtherInfo)
                    End If
 
                End While
 
                dsRosterUpdate.Tables(0).Rows(i).Close()
 
            Catch ex As Exception
                ImportLabel.Text &= "<font color='red'>" & ex.ToString & "</font><br />"
            End Try
 
        End If
 
    Protected Function ExcelConnection() As OleDbCommand
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/roster/temp/rosterimport.xls") & ";Extended Properties=Excel 8.0;"
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open()
        Dim objCommand As New OleDbCommand("SELECT * FROM [Roster$]", objXConn)
        Return objCommand
    End Function

Open in new window

roster.xls
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 23859831
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
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 24087643
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
0
 

Author Comment

by:clearchannel
ID: 24089141
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
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 24089670
Hi

well your initial questions was

<I am trying to upload an XLS spreadsheet put it into a dataset and then loop through the dataset and update my <database.

I provided you with a solution that does exactly that.
Did you even test my last provided solution ?

<How do I loop through the dataset created form the xlas file and call the function to either insert, update or <delete as in my code below? the READ method is causing an issue on the while loop?

Regarding your upload screen shot

i replied:
set the HDR=NO '
then that way you dont get the header data
after the adapter has read the xls data into the table then tbl.rows(0).delete()

but your code does not reflect this on the fill.
 Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/roster/temp/rosterimport.xls") & ";Extended Properties=Excel 8.0;"

http://www.connectionstrings.com/excel
Excel file without header row
Data Source =c:\myExcelFile.xls;HDR=no;Format=xls;


Secondly :

You will not get any updating against your database table if you do not set all of
your imported rows (from excel) to added

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

You will need set your rows state to added or your adapter will not reconize anything to update.
This your code does neighter reflect.
http://msdn.microsoft.com/en-us/library/ww3k31w0(VS.71).aspx

i recommend you to test my last provided solution (This works)


=====================================================================

Computer101:

Please refund clearchannel his point
Its very hard for me to guess what his is looking for, since he is not elaborating or follow up on his thread
and it seems like he wants me to write he's entire solution from pure speculation

/vbturbo

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question