Solved

importing Excel to SQL server - runs a bit slow - can we make it work faster ??

Posted on 2004-10-18
24
273 Views
Last Modified: 2012-06-21
Hello All,

Iam new to the world of ASP .Net. Right now iam building an application that will IMPORT about 5,000 records from an Excel spreadsheet to a table in MS SQL Server. Right now the code works correctly, but i feel it is not efficient and takes a little bit of more time in doing the import. Could you guys throw some light on how i can make the code run more faster ?  Someone suggested me that i can use DataAdapter and update the table in the database thru an update method available with it. I dont know how to do it?  Could anyone share with me a snippet of code that does this ?

 Here is my code:

       
Private Sub ProcessRecords()
         Dim ds2 As New DataSet
        ' readExcelSheet is a user-defined function that  reads a spreadsheet and returns a DataSet object
        ds2 = readExcelSheet("C:\Inetpub\wwwroot\Project1\Book2.xls", "SELECT * FROM [Sheet1$]")
        Dim myConnection As SqlConnection = Connection()     ' user-defined function that returns a SQLConnection object
        myConnection.Open()
        Dim strSQL As String = "insert_member"  ' stored procedure that inserts records
        Dim myCommand As New SqlCommand(strSQL, myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.Parameters.Add("@salutation", SqlDbType.NVarChar)
        myCommand.Parameters.Add("@firstname", SqlDbType.NVarChar)
        myCommand.Parameters.Add("@lastname", SqlDbType.NVarChar)
        myCommand.Parameters.Add("@company", SqlDbType.NVarChar)
       
        Dim i, j As Integer
        Response.Write(Date.Now() & "<br>")
        For i = 0 To ds2.Tables("Members").Rows.Count() - 1
            myCommand.Parameters("@salutation").Value = ds2.Tables("Members").Rows(i).Item("sal")
            myCommand.Parameters("@firstname").Value = ds2.Tables("Members").Rows(i).Item("firstname")
            myCommand.Parameters("@lastname").Value = ds2.Tables("Members").Rows(i).Item("lastname")
            myCommand.Parameters("@company").Value = ds2.Tables("Members").Rows(i).Item("company")
            j = myCommand.ExecuteNonQuery()
            If (j > 0) Then
                Response.Write("Record Inserted - " & i + 1 & "<br>")
            End If
        Next
        Response.Write(Date.Now() & "<br>")
        myConnection.Close()
End Sub




Please reply soon.
Thank You.
0
Comment
Question by:dn_learner
  • 9
  • 8
  • 2
  • +3
24 Comments
 
LVL 15

Expert Comment

by:praneetha
Comment Utility
http://www.aspfree.com/c/a/ASP.NET-Code/Read-Excel-files-from-ASPNET/

you can create a dataset from excel and then use dataset to update or insert into sql...

see if that helps...
0
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
In SQL Server you can use DTS to import from Excel sheet to table. Is that an option?
0
 
LVL 17

Expert Comment

by:AerosSaga
Comment Utility
a datareader would be faster than a dataset

Aeros
0
 
LVL 17

Expert Comment

by:AerosSaga
Comment Utility
 Public Sub Submit1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        'Variable declerations
        Dim cnnMisc As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cnnExcel As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Inetpub\wwwroot\Blaise\Upload.XLS;Mode=ReadWrite;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False")
        Dim cmdReadChanges As New OleDb.OleDbCommand
        Dim cmdClearTable As New OleDb.OleDbCommand
        Dim cmdWriteChanges As New OleDb.OleDbCommand
        Dim drChanges As OleDb.OleDbDataReader
        Dim intColIndex, intContentLength As Integer
        Dim strStockNumber, strYear, strMake, strModel, strColor, strPrice, strFileName, strDealership, strDealershipID, strNew, strTempNew, strMiles As String
        Dim temp As Text.StringBuilder

        'Extract File Name,get DealerID, and New boolean value
        strFileName = System.IO.Path.GetFileNameWithoutExtension(File1.Value)
        strDealership = strFileName
        If strDealership.StartsWith("Montoursville") Then
            strDealershipID = 1
        ElseIf strDealership.StartsWith("Selinsgrove") Then
            strDealershipID = 2
        ElseIf strDealership.StartsWith("MountCarmel") Then
            strDealershipID = 3
        ElseIf strDealership.StartsWith("WilkesBarre") Then
            strDealershipID = 4
        ElseIf strDealership.StartsWith("Williamsport0") Then
            strDealershipID = 5
        ElseIf strDealership.StartsWith("Hughesville") Then
            strDealershipID = 6
        ElseIf strDealership.StartsWith("Williamsport1") Then
            strDealershipID = 7
        ElseIf strDealership.StartsWith("Sunbury") Then
            strDealershipID = 8
        ElseIf strDealership.StartsWith("Bloomsburg") Then
            strDealershipID = 9
        End If
        strTempNew = strFileName.EndsWith("New")
        If strTempNew = "True" Then
            strNew = "True"
        Else
            strNew = "False"
        End If

        'Set connections and select Excel data
        cmdReadChanges.Connection = cnnExcel
        cmdClearTable.Connection = cnnMisc
        cmdWriteChanges.Connection = cnnMisc
        If strNew = "True" Then
            cmdReadChanges.CommandText = "SELECT * FROM [RWTemp$] WHERE [F1] IS NOT NULL AND [F3] NOT LIKE '--%' AND [WEBS] <> 'MODEL DESCRIPTION'"
        Else
            cmdReadChanges.CommandText = "SELECT * FROM [RWTemp$] WHERE [F2] IS NOT NULL AND [F3] NOT LIKE '--%' AND [F4] <> 'MODEL DESCRIPTION'"
        End If

        'Save Excel data locally
        If Not (File1.PostedFile Is Nothing) Then
            Try
                File1.PostedFile.SaveAs("C:\Inetpub\wwwroot\blaise\upload.xls")
                Span1.InnerHtml = "<b>Upload Successful!</b>"
            Catch ex As Exception
                Span1.InnerHtml = "Error saving file <b>C:\\" & _
                   File1.Value & "</b><br>" & ex.ToString()
            End Try
        End If

        'Select New/Used and Get strings from Excel data accordingly
        cnnExcel.Open()
        cnnMisc.Open()
        drChanges = cmdReadChanges.ExecuteReader(CommandBehavior.CloseConnection)
        drChanges.Read()

        'Clear appropriate information based on dealer and new/used status before importation
        cmdClearTable.CommandText = "DELETE * FROM Vehicle WHERE DealershipID = " & strDealershipID & " AND New = " & strNew
        cmdClearTable.ExecuteNonQuery()

        While drChanges.Read()
            For intColIndex = 0 To drChanges.HasRows = False
                If strNew = "True" Then
                    If drChanges.IsDBNull(0) Then
                        strStockNumber = "N/A"
                    Else
                        strStockNumber = drChanges.GetString(0)
                    End If
                    strYear = drChanges.GetString(1)
                    strMake = drChanges.GetString(2)
                    strModel = drChanges.GetString(3)
                    If drChanges.IsDBNull(4) Then
                        strColor = "N/A"
                    Else
                        strColor = drChanges.GetString(4)
                    End If
                    If drChanges.IsDBNull(5) Then
                        strPrice = 0
                    Else
                        strPrice = drChanges.GetString(5)
                    End If
                Else
                    strStockNumber = drChanges.GetString(0)
                    strYear = drChanges.GetDouble(1)
                    strMake = drChanges.GetString(2)
                    strModel = drChanges.GetString(3)
                    If drChanges.IsDBNull(4) Then
                        strColor = "N/A"
                    Else
                        strColor = drChanges.GetString(4)
                    End If
                    If drChanges.IsDBNull(5) Then
                        strPrice = 0
                    Else
                        strPrice = drChanges.GetDouble(5)
                    End If
                    If drChanges.IsDBNull(6) Then
                        strMiles = 0
                    Else
                        strMiles = drChanges.GetDouble(6)
                    End If
                End If

                'Insert new data into database based upon New boolean value
                'Response.Write(drChanges.GetValue(intColIndex) & "<br>")
                If drChanges.HasRows And strNew = "True" Then
                    cmdWriteChanges.CommandText = "INSERT INTO Vehicle (StockNumber,[Year],Make,Model,Color,Price,DealershipID,New) VALUES (" & "'" & strStockNumber & "'," & strYear & ",'" & strMake & "','" & strModel & "','" & strColor & "'," & strPrice & "," & strDealershipID & "," & strNew & ")"
                    cmdWriteChanges.ExecuteNonQuery()
                Else
                    cmdWriteChanges.CommandText = "INSERT INTO Vehicle (StockNumber,[Year],Make,Model,Color,Price,DealershipID,New,Miles) VALUES (" & "'" & strStockNumber & "'," & strYear & ",'" & strMake & "','" & strModel & "','" & strColor & "'," & strPrice & "," & strDealershipID & "," & strNew & "," & strMiles & ")"
                    cmdWriteChanges.ExecuteNonQuery()
                End If
            Next
        End While

        'Close and dispose of connections
        drChanges.Close()
        cnnExcel.Close()
        cnnMisc.Close()
        cmdReadChanges.Dispose()
        cmdWriteChanges.Dispose()
        cmdClearTable.Dispose()
        cnnExcel.Dispose()
        cnnMisc.Dispose()
    End Sub
0
 
LVL 33

Expert Comment

by:raterus
Comment Utility
I'm with CJ_S, you should be using DTS, it's built for import/export operations like this.  No reason I see to reinvent the wheel.
0
 

Author Comment

by:dn_learner
Comment Utility
Hi all,

I have to do this import programatically using ASP .Net and VB.Net that iam doing currently. I do NOT want to use DTS....

Praneetha, could you please share a snippet of code on how to update the DB with a DataAdapter.  The URL that u mentioned doesn't discuss anything about updating the DataAdapter.

Please help.

Anyways guys, thanks for your fast response and iam still looking for the best solution to solve this problem.  Aero, i dont know exactly what your code is doing? Could u please edit my code and address my concern ?

Thank You.

0
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
In that case you could perhaps use the following SQL statement:

INSERT INTO databasename.dbo.MyTableName (your, fields, here)
SELECT your, fields, here
FROM [Sheet1$]

from a linked server. The Linked Server must be set in SQL Server, but can be an ODBC connection.

I'm just passing options... I am not telling you what to do.

CJ.
0
 
LVL 15

Expert Comment

by:praneetha
Comment Utility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconUpdatingDatabaseWithDataAdapterDataSet.asp

but u need to declare database table with adapter before u declare dataadapter with temp table..and then write a trigger or something to insert into main table(table u want it to insert) whenever there is insert in temp table....  http://www.experts-exchange.com/Programming/Q_20953988.html
0
 
LVL 15

Expert Comment

by:praneetha
Comment Utility
0
 

Author Comment

by:dn_learner
Comment Utility
Hello Praneetha,

Could you please edit my code at the top of the forum addressing my concern.  I know iam asking too much .... please help me out.
Basically, my code is calling the stored procedure 5,000 times and i think its not good from a performance point of view.   Could u just modify my code and show me how u do it with  a DataAdapter using Update method?

The links that you provided are indeed helpfull.

Thank You again.
0
 
LVL 15

Expert Comment

by:praneetha
Comment Utility
Hi dn_learner,

i am little busy today..will try to get back to you again tomorrow..sorry about that...

good luck
0
 

Author Comment

by:dn_learner
Comment Utility
Hi praneetha,

I understand your situation. I will be eagerly awaiting for your response.  

Thank You.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 15

Expert Comment

by:praneetha
Comment Utility
Hi,

i have this example code in C# - I am not a vb.net person..but it should not be hard to translate it to vb.net - if you have questions i will try to help..and i am sure there are many other experts who partcicipated in this post can assist u 2...

ok my sqltable has

column1,column2,column3

and my excel book contains

column1      column2      column3
test1      test1      test1
test2      test2      test2


here is the code to read from excel and convert it to dataset....


#region exceltodataset
                  string strConn;
                  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\exceltest.xls;" + "Extended Properties=Excel 4.0;";
                  //You must use the $ after the object you reference in the spreadsheet
                  OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

                  DataSet myDataSet = new DataSet();
                  myCommand.Fill(myDataSet);
                  #endregion

code to update database with dataset object...

      #region datasettodb
                  string ConnStr="connection string ";
                  DataSet ds=new DataSet();
                  SqlConnection myConnection=new SqlConnection(ConnStr);
                  try
                  {
                        SqlDataAdapter myAdapter=new SqlDataAdapter("select * from tbl_Excel",myConnection);
                        SqlCommandBuilder custCB = new SqlCommandBuilder(myAdapter);
                        myAdapter.SelectCommand.CommandType = CommandType.Text;
                        custCB.RefreshSchema(); // this is for adapter to write own insert command..or else you can write your own insert command...
                        myAdapter.Fill(ds);
                        foreach(DataRow dr1 in myDataSet.Tables[0].Rows)
                        {
                              DataRow dr=ds.Tables[0].NewRow();
                              dr[0]=dr1[0];
                              dr[1]=dr1[1];
                              dr[2]=dr1[2];
                              ds.Tables[0].Rows.Add(dr);
                        }
                        DataSet ds1=ds.GetChanges();
                        myAdapter.Update(ds1.Tables[0]);
                                          
                  }
                  catch(SqlException excep)
                  {
                        throw excep;
                  }
                  finally
                  {
                        myConnection.Close();
                  }
                  #endregion

wil try to look at your code and see if i can convert to C#...in the mean while you can try it too..you can try with sample table and sample excel and see how it works...

Thank You.
Good Luck.

0
 

Author Comment

by:dn_learner
Comment Utility
Praneetha,

You have been really helpfull. You are really a GURU in ASP.Net.

Let me try your code and i will get back to you as soon as iam done with it.

Thank You.

Bye.
0
 

Author Comment

by:dn_learner
Comment Utility
Praneetha,

Could u please tell me what exactly is the tbl_Excel in the code statement below?  Is is the table from SQL Server or the sheet from the excel file ?
What is the purpose of doing this ?

SqlDataAdapter myAdapter=new SqlDataAdapter("select * from tbl_Excel",myConnection);


Please reply soon.

0
 
LVL 15

Accepted Solution

by:
praneetha earned 500 total points
Comment Utility
tbl_excel - SQL Table...

well i have to do this..bcz you have to associate a sql table with a dataadapter...the prvious adapter was linked to excel file..so i just used an other adpater object.

SqlDataAdapter myAdapter=new SqlDataAdapter("select * from tbl_Excel",myConnection);
                    SqlCommandBuilder custCB = new SqlCommandBuilder(myAdapter);
                    myAdapter.SelectCommand.CommandType = CommandType.Text;
                    custCB.RefreshSchema(); // this is for adapter to write own insert command..or else you can write your own insert command...
                    myAdapter.Fill(ds);
                    foreach(DataRow dr1 in myDataSet.Tables[0].Rows)
                    {
                         DataRow dr=ds.Tables[0].NewRow();
                         dr[0]=dr1[0];
                         dr[1]=dr1[1];
                         dr[2]=dr1[2];
                         ds.Tables[0].Rows.Add(dr);
                    }
                    DataSet ds1=ds.GetChanges();
                    myAdapter.Update(ds1.Tables[0]);
0
 

Author Comment

by:dn_learner
Comment Utility
Hi Praneetha,

With your valuble help, iam able to read data from an excel file and put it into a SQL Server table.  

Since i have an auto-increment column called 'memberID' in my sql server table 'Members', iam not selecting that column in my select statement of the dataAdapter and therefore it works perfectly fine. Note that in my excel file i do not have a column called 'memberID' but do have the other 4 columns corresponding to my 'Members' table in sql server.

Now, please clarify some questions that i have regarding your code that i have used.

1)   What is the purpose of using the SqlCommandBuilder and what role is it playing  in our code ?

2)  what does myCB.RefreshSchema has to do in our code ?

3) Lastly,  performance-wise , which code is very efficient? the one that i have below OR the one at the top question of the forum-thread where iam calling the stored procedure many times within a for loop ?

Please let me know.

You have been indeed very helpfull for me in learning .Net.

Thank You.
 

Here is my current version of the code based on your code:
--------------------------------------------------------------------
Private Sub Process_Records()

        Dim i, j As Integer
        Dim dt2 As New DataTable
        ' readExcelSheet is a user-defined function that returns a DataTable containing records from the excel spreadsheet
        dt2 = readExcelSheet("C:\Inetpub\wwwroot\Project1\Book2.xls", "SELECT * FROM [Sheet1$]")
        Dim myConnection As SqlConnection = Connection()      'user-defined function that returns a SqlConnection object
        Dim ds As New DataSet
        myConnection.Open()
        Dim myAdapter As New SqlDataAdapter("SELECT salutation, first_name, last_name, company FROM Members", myConnection)
        Dim myCB As New SqlCommandBuilder(myAdapter)
        Dim dr1 As DataRow
        myAdapter.SelectCommand.CommandType = CommandType.Text
        myCB.RefreshSchema()
        myAdapter.Fill(ds, "Members")
        i = dt2.Rows.Count()
        For Each dr1 In dt2.Rows
            Dim dr As DataRow = ds.Tables("Members").NewRow()
            dr(0) = dr1(0)
            dr(1) = dr1(1)
            dr(2) = dr1(2)
            dr(3) = dr1(3)
            ds.Tables("Members").Rows.Add(dr)
        Next
       
        Dim ds1 As New DataSet
        ds1 = ds.GetChanges()
        myAdapter.Update(ds1, "Members")
        Response.Write("Number of records inserted" &  i   )
        myConnection.Close()
    End Sub


0
 
LVL 15

Expert Comment

by:praneetha
Comment Utility
hi dn_learner,

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclassupdatetopic.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.asp

all these articles will answer your questions about dataadapter...i would be glad to help if you have more questions after that...

i am sure this way of doing is lot faster then hitting the database every row in a excel sheet....do u see any difference performance ways...

0
 

Author Comment

by:dn_learner
Comment Utility
Hi,

Once again thank you so much for all your help. I really appreciate it.

Well, iam not that good at research but i did put  Response.write(Date.Now()) statements before and after the block of code that inserted or updated the data from my excel file to sql server.    

When i was using the calls to stored procedure from the for loop, it took 3 seconds to insert 3559 records from excel file to the sqlserver table.
When i used the DataAdapter update method, it took surprisingly 5 seconds to do the import process.

But iam sure that no matter it took 5 seconds to do the import, performance wise it is better since it consumed less system resources and less network traffic. Thats all i can conclude..           What do u have to say ??

Since iam new to EE forum, is there a way i can directly contact you thru this forum coz iam not sure if in the future, i post a question and you might not reply or may be away from computer ???   :-(

Please let me know.

Thanks.

0
 
LVL 15

Expert Comment

by:praneetha
Comment Utility
hi,

yeah i think making database trip once is always better...

but u still have to take care of concurrency though....(incase more then one person is trying to modify the data)

well i don't think you can directly write to me thru EE...i participate in EE alot so you can post your question here..that way you get other experts expertise too....

good luck.



0
 
LVL 3

Expert Comment

by:Pro_buddy
Comment Utility
Hi,

Above discussion found very useful for importing data from excel into DB. But i would like to know some more details regarding this.

1. How to update existing DB data(>> already imported from excel), when trying to import data from updated excel file?

2. Is there any way to compare two dataset data(s) for the changes made?


Thanks in Advance for your time!
Pro
0
 

Author Comment

by:dn_learner
Comment Utility
Hi,

For your number 2 question, there is a method which u can use like this:

myDataSet.HasChanges()   and also
myDataSet.GetChanges() ..


Do you have any idea for question number 1 ??  I can think but dont know how to exactly implement it ?

Thank You.

0
 
LVL 3

Expert Comment

by:Pro_buddy
Comment Utility
Hi,

Methods you have mentioned are used to get a copy of the dataset containing all the changes made to it since it was last loaded(GETCHANGES) & HasChanges method of a dataset returns true if changes have been made in the dataset.

eg;

* NewDataSet = myDataSet.GetChanges(DataRowState.Modified)
* myDataSet.HasChanges(DataRowState.Modified)

But actually i want to compare two different datasets. As per above discussion, I am able to insert data into DB from excel. Its works fine. Problem arises while trying to update DB from same excel with some more additional data(s).

I thought of using two different datasets, one for existing data from DB and another one for retreiving data from the excel. Finally compare 2 datasets & update DB with only new changes made in Excel.

I dont know whether i am on the right path? Help me!

Thank You!
0
 

Author Comment

by:dn_learner
Comment Utility
I would suggest you to post a new thread on this forum...

Someone would definitely answer your concerns...

Let me know when you do it.

thanks
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now