Results 1 to 10 of 10

Thread: Push excel data to Access table - VB Application

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2012
    Posts
    5

    Question Push excel data to Access table - VB Application

    So I have about 60 worksheets that I need to push into an access database (or sql). I'll be getting another 60 worksheets in 6 months, and another 60 after that.. so I wanted to build an app to do this work for me.

    In visual studio 2008 I've built a project that loops through all my rows and columns, and pushes it into an access database. When I built this for another type of data, I used the exact same approach and got a quick and efficient tool, now I've built this tool and it is not efficient or fast.

    It's a single sheet with anywhere from 5 to 50 columns of data, with 60,000 + rows (which is also variable). I tried basic for loops, but it takes hours to complete. Any advice to help speed up this code would be welcomed:

    Code Snippit:

    Code:
    Public Sub ProcessSheet()
            Dim StationID As String
            Dim TimeStamp As Date
            Dim MeasuredValue As Decimal
            Dim ParameterName As String
            Dim c As Integer
            Dim r As Long
            Dim strDestinationPathFile As String
            Dim strPathFile As String
            Dim StrCurYear As String
    
    
            'Set and Open Access Database
            strDestinationPathFile = Form1.TextBox2.Text
            Dim conn As ADODB.Connection
            conn = New ADODB.Connection
    
            conn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDestinationPathFile & ";Persist Security Info=False;")
    
            Processing = True
            StrCurYear = DatePart(DateInterval.Year, Now())
            StationID = Form1.ListBox1.SelectedItem.ToString
    
            'Set and Open Excel Workbook
            strPathFile = Form1.TextBox1.Text
            Dim oExcel As Object
            Dim wb As Object
            oExcel = CreateObject("Excel.Application")
            wb = oExcel.workbooks.open(strPathFile)
            oExcel.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
    
            'Loop across the Columns
            For c = 3 To 75
                If Len(wb.Sheets.Item(1).Cells(4, c).Value) = 0 Then Exit For
    
                ParameterName = Trim(wb.Sheets.Item(1).Cells(4, c).Value)
    
                'Loop down the rows
                For r = 5 To 30000
                    If Len(Trim(wb.Sheets.Item(1).Cells(r, 2).Value)) = 0 Then Exit For
    
                    
    
                    TimeStamp = CDate(wb.Sheets.Item(1).Cells(r, 2).Value)
                    If TimeStamp.Second() = 59 Then
                        TimeStamp = TimeStamp.AddSeconds(1)
                    End If
    
                    Dim cellvalue As String = wb.Sheets.Item(1).Cells(r, c).Value
                    If Len(Trim(cellvalue)) = 0 Then Continue For
    
                    MeasuredValue = CDec(Trim(wb.Sheets.Item(1).Cells(r, c).Value))
    
                    Dim strSQL As String
                    strSQL = "INSERT INTO WeatherData" & StrCurYear & " (StationID, TimeTag, Parameter, MeasuredValue, DateAdded) VALUES ('" & StationID & "',#" & TimeStamp & "#,'" & ParameterName & "'," & MeasuredValue & ",#" & Now() & "#)"
                    conn.Execute(strSQL)
    
                Next
    Thanks in advance!!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Push excel data to Access table - VB Application

    first thing i would check, does it stop looping at empty column and row?
    does the number of records added match the number of cells?
    if you comment out the conn.execute line does it make a huge difference?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2012
    Posts
    5

    Re: Push excel data to Access table - VB Application

    Hey, thanks for looking!

    The loop ends when there is nothing in the cell, with this line (or another like it):
    Code:
    If Len(wb.Sheets.Item(1).Cells(4, c).Value) = 0 Then Exit For
    and it seems to be working ok. When I run a sheet with only 2 rows it finishes instantly.

    All the data in the sheets does move to the database as expected, just much slower than expected.

    I haven't tried to comment out the conn.execute, that's a good idea. It may at least tell me if it's access that is the slow down or not. Something to try tomorrow!

    I guess from your reply, the approach is ok then? I've heard of pushing everything into an array and then work with the array, but that is a little outside my expertise.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Push excel data to Access table - VB Application

    i am not a database expert, but it would probably be much faster to do one insert, with all new records included
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Jul 2012
    Posts
    5

    Re: Push excel data to Access table - VB Application

    So I did a run with the conn.execute commented out, but the speed did not improve significantly. So I don't think it is access, or the inserts, that is slowing it down.

    I'm really just not sure if looping through each row and column is the best approach when dealing with as many as 1.5 - 3 million cells. I've heard of 'InRange' approches, but cannot tell if it would benefit the performance.

    Thanks for the input...

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Push excel data to Access table - VB Application

    you could assign the whole range to an array variable, then loop through the array, but whether this would have significant gains would be up to you to test

    vb Code:
    1. myarr = Range("a1:f2463")
    2. For r = 1 To UBound(myarr, 1)
    3.     For c = 1 To UBound(myarr, 2)
    4.         'do stuff here
    5.     Next
    6. Next
    change range to suit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2012
    Posts
    5

    Re: Push excel data to Access table - VB Application

    Ahhh... It was the Ubound stuff that was confusing about the arrays.. I'll try a test using an array, and see if it improves. It may simply be that inserting 3 million rows takes some time!

    thanks again..

  8. #8
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439

    Re: Push excel data to Access table - VB Application

    Do you _have_ to push from Excel or can you possibly pull from Access instead?

  9. #9

    Thread Starter
    New Member
    Join Date
    Jul 2012
    Posts
    5

    Re: Push excel data to Access table - VB Application

    All the incoming data is in either monthly or annual spreadsheets, I ultimately need to get it into sql just to do anything with it... but whatever I want to do, I'm stuck with the spreadsheets..

    Access was really just my intermediary tool to do some QAQC before dropping it into the sql database, which should go much smoother.

    I may try just linking each sheet to access, and query it out from there.. Using the code above, 1 full sheet takes 40+ minutes to 'automate' .. and that even starts to slow down over time.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Push excel data to Access table - VB Application

    you can run sql query on excel worksheets, but i doubt that it will be any gain as you would want to add each field of each record as a new record to access
    http://support.microsoft.com/kb/257819
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width