-
Jul 9th, 2012, 02:36 PM
#1
Thread Starter
New Member
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!!
-
Jul 9th, 2012, 04:13 PM
#2
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
-
Jul 9th, 2012, 05:23 PM
#3
Thread Starter
New Member
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.
-
Jul 10th, 2012, 04:57 AM
#4
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
-
Jul 11th, 2012, 10:17 AM
#5
Thread Starter
New Member
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...
-
Jul 11th, 2012, 03:57 PM
#6
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:
myarr = Range("a1:f2463") For r = 1 To UBound(myarr, 1) For c = 1 To UBound(myarr, 2) 'do stuff here Next 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
-
Jul 11th, 2012, 04:05 PM
#7
Thread Starter
New Member
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..
-
Jul 11th, 2012, 04:21 PM
#8
Hyperactive Member
Re: Push excel data to Access table - VB Application
Do you _have_ to push from Excel or can you possibly pull from Access instead?
-
Jul 11th, 2012, 05:27 PM
#9
Thread Starter
New Member
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.
-
Jul 12th, 2012, 04:38 PM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|