Hi Guys,

I need help on using Excel files in vb.net 2005.
I am writing an application which will attach the xl files reads the data in the xl files and stores into the database. The problem is i have 62k Data in the XL file. And 10k data in database. now i want to merge both the data (data from XL file and Data from Database) and find the duplicates in the merged data and generate a New XL file.

for this i am taking OLEDB support to attach the XL files. I am reading the xl file and binding it to the dataset and then in a loop i am storing the records into the database. but as the data is huge it is taking lots of time to store the data.


I need a alternative solution with which i can store the data in XL file to Database in less time.

my logic is taking 2 hrs to store 13k records in database.

If u have the solution please write to [email protected]

Thank you:)

Hi there..

you seems to be having a problem with the logic... maybe in the merge you are full scanning all the data always??? or the data is (besides the quantity of records) too large??

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Import your x tra L sheet in MS Access as link table and then open two record set and probably two connections
like this

'Open Imported Table
r.Open "Table1", Connection1, adOpenForwardOnly, adLockReadOnly
t.Open "Table2", Connection2, adOpenKeyset, adLockOptimistic


Do While Not r.EOF

    If Not t.RecordCount <= 0 Then
        t.MoveFirst
    End If

            If t.EOF Then

                If Nz(r!ID, "") <> "" AND r!Quan >= 1 Then ' you can use any condition

                    t.AddNew
                    t!ID = r!ID
                    t.Update

                Else
                    lblWarning.Visible = True
                     If r!F1 <> Null Then
                            Error = r!PID
                        Else
                            Error = "Null"
                    End If
                        lblWarning.Caption = "Warning: Problem with importing Data, Please check Excel Sheet Line No: " & Error

                End If

            End If

            If Nz(r!ID, "") <> "" r!Quan >= 1 Then

                    t!ID = r!ID
                    t.Update
            Else
                lblWarning.Visible = True
                If r!ID <> Null Then
                    Error = r!ID
                Else
                    Error = "Null"
                End If
                    lblWarning.Caption = "Warning: Problem with Updating Data, Please check Excel Sheet Line No: " & Error
            End If

    r.movenext
Loop
r.Close
t.Close

Hi gbianchi

my code is like this

 Dim oda As New OleDbDataAdapter("Select * from [" + cmbSheet.Text + "]", conn)
        Dim ods As DataSet
        ods = New DataSet
        oda.Fill(ods, "ns")
        Application.DoEvents()
        dv = New DataView
        dv.Table = ods.Tables(0)
        dg2.DataSource = dv
        Try
            For R As Integer = 0 To dg2.RowCount - 1
                ssql = ""
                ssql = ssql & " INSERT INTO [SalesLogix].[sysdba].[TMPHouseList]"
                ssql = ssql & " ([accountid]"
                ssql = ssql & " ,[contactid]"
                ssql = ssql & " ,[account]"
                ssql = ssql & " ,[firstname]"
                ssql = ssql & " ,[lastname]"
                ssql = ssql & " ,[address1]"
                ssql = ssql & " ,[address2]"
                ssql = ssql & " ,[city]"
                ssql = ssql & " ,[state]"
                ssql = ssql & " ,[postalcode]"
                ssql = ssql & " ,[country]"
                ssql = ssql & " ,[workphone]"
                ssql = ssql & " ,[homephone]"
                ssql = ssql & " ,[mobile]"
                ssql = ssql & " ,[email]"
                ssql = ssql & " ,[createdate]"
                ssql = ssql & " ,[modifydate]"
                ssql = ssql & " ,[lsttrandate])"
                ssql = ssql & " VALUES"
                ssql = ssql & " ('" & dg2.Item(1, R).Value & "','" & dg2.Item(2, R).Value & "',"
                ssql = ssql & " '" & dg2.Item(3, R).Value & "','" & dg2.Item(4, R).Value & "',"
                ssql = ssql & " '" & dg2.Item(5, R).Value & "','" & dg2.Item(6, R).Value & "',"
                ssql = ssql & " '" & dg2.Item(7, R).Value & "','" & dg2.Item(8, R).Value & "',"
                ssql = ssql & " '" & dg2.Item(9, R).Value & "','" & dg2.Item(10, R).Value & "',"
                ssql = ssql & " '" & dg2.Item(11, R).Value & "','" & dg2.Item(12, R).Value & "',"
                ssql = ssql & " '" & dg2.Item(13, R).Value & "','" & dg2.Item(14, R).Value & "',"
                ssql = ssql & " '" & dg2.Item(15, R).Value & "',"
                If IsDate(dg2.Item(16, R).Value) = True Then
                    ssql = ssql & " '" & Convert.ToDateTime(dg2.Item(16, R).Value) & "',"
                Else
                    ssql = ssql & " '01/01/1900',"
                End If

                If IsDate(dg2.Item(17, R).Value) = True Then
                    ssql = ssql & " '" & Convert.ToDateTime(dg2.Item(17, R).Value) & "',"
                Else
                    ssql = ssql & " '01/01/1900',"
                End If

                If IsDate(dg2.Item(18, R).Value) = True Then
                    ssql = ssql & " '" & Convert.ToDateTime(dg2.Item(18, R).Value) & "')"
                Else
                    ssql = ssql & " '01/01/1900')"
                End If

                cmdsql.CommandText = ssql
                cmdsql.ExecuteNonQuery()

            Next


this is how i am inserting the data into the table
:)


Regards
Bhaskar Kiran
Mail:[email protected]

Some ideas to try to improve this:

1. use a store procedure: you can prepare it so it will execute faster and also passing parameters should be a little more fast that building a huge string with the insert statement. Also did you know that every time you concatenate 2 strings it will copy the first one to a new position that can hold the result, so in every concatenation it will build the string again, bad for performance.
2. Open the data from the excel in a reader. It's faster, read only and you can forgive the for loop and replace with a while reader.read.

To Ayazhoda: nice solution, but this is a .net forum, not a vb6.

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Hi,
Let me explain you my previous post. first i am fetching the MS XLs file using open dialog and then i am reading the sheet names and adding them into the combo box. now i am passing the sheet name into the query and fetching the data of that sheet and binding it with data set. and then i am inserting the data into the data base.

my application will have 2 data grids

in 1st grid i am fetching the data from the data base which i said earler 10k data.
in 2nd grid i am attaching the XL files data after i am storing the xlfile data into the data base

here one point is there. I have to store data of 4 XL files in which 1 XL files data will be very huge ex. 61k and rest will be less.

now i have to merge the both data grids data and find the duplicates in that. all the things are happening fine except when i am attaching the 61k data XL file it is taking more time.

i need a solution with which i can attach that 61k XL file in short time.
bcoz it is taking 2 hrs to store 13k data into database



Regards
Bhaskar Kiran
Mail:[email protected]

Hi,

Thanq for u r reply. but the problem is my database is residing in US and i have to connect to that db and do the operations. so as per u if i use Datareader every time i have to connect to the db for each record. where the performance will effect.



Regards
Bhaskar Kiran
Mail:[email protected]

but the excel is on your side?? that's where i told you to use the reader, since you are fetching records one by one because you are using a command to save the data...

maybe it's faster is you build a huge sql file with the inserts and run it only once?

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

hmmm.

You could do this:

Dim dt as New DataTable()
oda.fill(dt)

For Each dr as DataRow in dt.Rows
   sSql += "INSERT INTO [SalesLogix].[sysdba].[TMPHouseList]"
   sSql += "([accountid], [contactid], [account], [firstname], [lastname], [address1], [address2], [city], [state], [postalcode], [country], [workphone], [homephone], [mobile], [email], [createdate], [modifydate], [lsttrandate])"
   sSql += dr("value1") + ", " + dr("value2") ...etc
   sSql += ";"
Next

cmdsql.CommandText = ssql
cmdsql.ExecuteNonQuery()

that will create one huge SQL Statement and execute it against your server however, its not very effecient to do something like this.

First, that string is going to get extremely big and suck up memory and, on top of that, if it is taking you 2 hours to transfer 13K of data you are going to run into a time out problem trying to execute this file (i think).

This would be better as a DTS process, but that is my opinion.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

That was my idea Doug!!! ;p

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========