Importing a ton of excel data question

[thechazm]

I am importing a huge amount of excel data somewhere in the 500 thousand rows that is being exported from quite a few servers. The current process I have written using vba takes roughly almost 1 hour and 30 minutes to import. Now doing it this way I am normalizing the data as well as pulling it in.

My question is would there be a faster meathod that someone knows that could pull in the data and normalize it?

I was thinking maybe it might be faster to just pull in the raw data from excel into tmp tables and try to setup queries to normalize, update, and insert into the active tables but I don't know if I would gain anything from this.

If anyone has any idea's it would be greatly appreciated.

Also just to let everyone know the reason for the imports is to produce metrics based from several systems semi automatedly if that makes sense

Thanks

 

[Galaxiom]

Show us your code and we might find ways to speed it up.

 

[thechazm]

Alright well here is one of the functions that takes the longest time. It takes 34 minutes for this function alone. Thanks for the help.

Function ImportTraining(strFile As String)
Dim xlsApp As Excel.Application, xlsWorkbook As Excel.Workbook, xlsSheet As Excel.Worksheet
Dim db As Database, rs As DAO.Recordset, rs2 As DAO.Recordset
Dim strProject As String, strBadge As String
If strFile = "" Then
    Exit Function
End If
Set xlsApp = New Excel.Application
Set xlsWorkbook = xlsApp.Workbooks.Open(strFile)
Set xlsSheet = xlsApp.Worksheets(1)
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [Training]", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Select * from [Other Events]", dbOpenDynaset)
ii = xlsSheet.Cells(xlsSheet.Rows.Count, "B").End(xlUp).Row
For i = 2 To xlsSheet.Cells(xlsSheet.Rows.Count, "B").End(xlUp).Row Step 1
    StatusLabel i & " - " & ii
    If Len(xlsSheet.Cells(i, 13)) > 1 Then
    
        If Len(CStr(xlsSheet.Cells(i, 5))) > 3 Then
            strProject = Left(CStr(xlsSheet.Cells(i, 5)), 3)
        Else
            strProject = CStr(xlsSheet.Cells(i, 5))
        End If
            
        rs.FindFirst "[Shop ID] = " & LookupShop(CStr(xlsSheet.Cells(i, 2))) & " AND [Project ID] = " & LookupProject(strProject) & " AND [Badge] = """ & CStr(xlsSheet.Cells(i, 3)) & """ AND [Start Date] = #" & Format(CDate(xlsSheet.Cells(i, 12)), "Short Date") & "# AND [End Date] = #" & Format(CDate(xlsSheet.Cells(i, 13)), "Short Date") & "#"
        If rs.NoMatch = True Then
            With rs
                .AddNew
                ![Shop ID] = LookupShop(CStr(xlsSheet.Cells(i, 2)))
                ![Project ID] = LookupProject(strProject)
                If Len(CStr(xlsSheet.Cells(i, 3))) = 5 Then
                    strBadge = "0" & CStr(xlsSheet.Cells(i, 3))
                Else
                    strBadge = CStr(xlsSheet.Cells(i, 3))
                End If
                
                ![Badge] = strBadge
                ![Start Date] = Format(CDate(xlsSheet.Cells(i, 12)), "Short Date")
                ![End Date] = Format(CDate(xlsSheet.Cells(i, 13)), "Short Date")
                .Update
            End With
        End If
    Else
        If Len(CStr(xlsSheet.Cells(i, 5))) > 3 Then
            strProject = Left(CStr(xlsSheet.Cells(i, 5)), 3)
        Else
            strProject = CStr(xlsSheet.Cells(i, 5))
        End If
        
        rs2.FindFirst "[Shop ID] = " & LookupShop(CStr(xlsSheet.Cells(i, 2))) & " AND [Project ID] = " & LookupProject(strProject) & " AND [Badge] = """ & CStr(xlsSheet.Cells(i, 3)) & """ AND [Start Date] = #" & Format(CDate(xlsSheet.Cells(i, 12)), "Short Date") & "# AND [Total Time] = " & CLng(xlsSheet.Cells(i, 13))
        If rs2.NoMatch = True Then
            With rs2
                .AddNew
                ![Shop ID] = LookupShop(CStr(xlsSheet.Cells(i, 2)))
                ![Project ID] = LookupProject(strProject)
                If Len(CStr(xlsSheet.Cells(i, 3))) = 5 Then
                    strBadge = "0" & CStr(xlsSheet.Cells(i, 3))
                Else
                    strBadge = CStr(xlsSheet.Cells(i, 3))
                End If
                
                ![Badge] = strBadge
                ![Start Date] = Format(CDate(xlsSheet.Cells(i, 12)), "Short Date")
                ![Total Time] = CLng(xlsSheet.Cells(i, 13))
                .Update
            End With
        End If
    End If
Next i
xlsApp.Quit
Set xlsSheet = Nothing
Set xlsWorkbook = Nothing
Set xlsApp = Nothing
rs.Close
Set rs = Nothing
Set db = Nothing
StatusLabel "Completed!"
End Function

 

[DJkarl]

http://www.access-programmers.co.uk/forums/showthread.php?t=195567

This may shed some light on why it's slow, and offer a different way to do it.

 

[thechazm]

That is an interesting way of doing it. Thank you for the reference. I'll test the theory and give it a go. Thanks Again