StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBig data upload to server, better in chunks?
text
Body
copied!<p>I've inherited a set of excel VBA macros that grabs the data from a spreadsheet and uploads the data to a database (SQL DB). The problem is the insane amount of time it takes when the data is "big" (46 columns * 10,500 rows on a particular sheet). It seems to me that it would be better to chunk the data to get it to the database, but is this correct? If so, what would be the best way to go about it? I'm currently trying encapsulating the following code in a for loop that chunks it into 500 rows, but it's not elegant as vba is not my forte.</p> <pre><code> Sub Upload_Claims() Dim SubmissionNumber As Integer Dim LoopVar As Integer, row As Integer Set cnnConn = New ADODB.Connection cnnConn.ConnectionString = "driver={SQL Server};server=" & Server & ";database=happyfunserver" cnnConn.Open SubmissionNumber = Sheets("Quality Check").Range("SubID").Value 'Upload HPL - PPL Set cmdCommand = New ADODB.Command Set cmdCommand.ActiveConnection = cnnConn With cmdCommand .CommandText = "Select * from losses where submission_id = " & SubmissionNumber .CommandType = adCmdText .Execute End With ' Open the recordset. Set rstRecordset = New ADODB.Recordset Set rstRecordset.ActiveConnection = cnnConn rstRecordset.Open cmdCommand, , adOpenStatic, adLockBatchOptimistic 'upload ' Sheets("PL").Select row = 8 Do While Range("C" & row).Value <> vbNullString With rstRecordset .AddNew .Fields("submission_id") = SubmissionNumber If Range("A" & row).Value <> vbNullString Then .Fields("tag_id") = Range("A" & row).Value End If If Range("B" & row).Value <> vbNullString Then .Fields("batch_tag_id") = Range("B" & row).Value End If If Range("C" & row).Value <> vbNullString Then .Fields("source") = Left(Range("C" & row).Value, 250) End If If IsDate(Range("D" & row).Value) Then .Fields("evaluation_date") = Range("D" & row).Value End If If Range("E" & row).Value <> vbNullString Then If Range("E" & row).Value = "HPL" Then .Fields("coverage_type_id") = 22 ElseIf Range("E" & row).Value = "PL" Then .Fields("coverage_type_id").Value = 2 End If End If '--------------' If Range("F" & row).Value <> vbNullString Then .Fields("claim_no") = Left(Range("F" & row).Value, 250) End If If Range("G" & row).Value <> vbNullString Then .Fields("claimant") = Left(Range("G" & row).Value, 200) End If 'upload layer' If Range("H" & row).Value <> vbNullString Then If UCase(Range("H" & row).Value) = "UNKNOWN" Then .Fields("layer_id") = 0 ElseIf UCase(Range("H" & row).Value) = "AAA" Then .Fields("layer_id") = 1 ElseIf UCase(Range("H" & row).Value) = "BBBBBB" Then .Fields("layer_id") = 2 ElseIf UCase(Range("H" & row).Value) = "CCCCC" Then .Fields("layer_id") = 3 ElseIf UCase(Range("H" & row).Value) = "DDDDDDDD" Then .Fields("layer_id") = 4 ElseIf UCase(Range("H" & row).Value) = "EEE" Then .Fields("layer_id") = 5 End If End If '-------------------' If Range("I" & row).Value <> vbNullString Then .Fields("aaaaaaaa_name") = Left(Range("I" & row).Value, 100) End If If IsNumeric(Range("J" & row).Value) And Range("J" & row).Value <> 0 Then .Fields("bbb_id") = Left(Range("J" & row).Value, 7) End If If Not IsError(Range("K" & row).Value) Then .Fields("ccc_id_verified") = Range("K" & row).Value End If If Not IsError(Range("L" & row).Value) Then If Range("L" & row).Value <> vbNullString And Range("L" & row).Value <> 0 Then .Fields("dddddddd_city") = Left(Range("L" & row).Value, 80) End If End If If Range("M" & row).Value <> vbNullString And Range("M" & row).Value <> 0 Then .Fields("eeeeeeee_fips") = Left(Range("M" & row).Value, 5) End If If Not IsError(Range("N" & row).Value) Then If Range("N" & row).Value <> vbNullString And Range("N" & row).Value <> 0 Then .Fields("ffffffff_stateabbr") = Left(Range("N" & row).Value, 2) End If End If If IsDate(Range("O" & row).Value) Then .Fields("gggggggg_date") = Range("O" & row).Value End If If IsDate(Range("P" & row).Value) Then .Fields("hhhhhh_date") = Range("P" & row).Value End If If IsNumeric(Range("Q" & row).Value) Or Range("Q" & row).Value = 0 Then .Fields("iiiiiiiii_paid") = Range("Q" & row).Value End If If IsNumeric(Range("R" & row).Value) Or Range("R" & row).Value = 0 Then .Fields("jjjjjjjjj_reserve") = Range("R" & row).Value End If If IsNumeric(Range("S" & row).Value) Or Range("S" & row).Value = 0 Then .Fields("kkkk_paid") = Range("S" & row).Value End If If IsNumeric(Range("T" & row).Value) Or Range("T" & row).Value = 0 Then .Fields("llll_reserve") = Range("T" & row).Value End If 'upload claim status' If Range("U" & row).Value <> vbNullString Then If UCase(Range("U" & row).Value) = "CLOSED" Then .Fields("status_id") = 1 ElseIf UCase(Range("U" & row).Value) = "OPEN" Then .Fields("status_id") = 0 ElseIf UCase(Range("U" & row).Value) = "REOPEN" Then .Fields("status_id") = 2 End If End If '---------------------------' If IsDate(Range("V" & row).Value) Then .Fields("closed_date") = Range("V" & row).Value End If If Range("W" & row).Value <> vbNullString Then .Fields("description") = Range("W" & row).Value End If If IsNumeric(Range("AN" & row).Value) Then .Fields("manual") = Range("AN" & row).Value End If If IsNumeric(Range("AB" & row).Value) Then .Fields("11111") = Range("AB" & row).Value End If If IsNumeric(Range("AC" & row).Value) Then .Fields("2222222") = Range("AC" & row).Value End If If IsNumeric(Range("AD" & row).Value) Then .Fields("33333333333") = Range("AD" & row).Value End If If IsNumeric(Range("AE" & row).Value) Then .Fields("444444444") = Range("AE" & row).Value End If If IsNumeric(Range("AF" & row).Value) Then .Fields("55555555") = Range("AF" & row).Value End If If IsNumeric(Range("AG" & row).Value) Then .Fields("666666666") = Range("AG" & row).Value End If If IsNumeric(Range("AH" & row).Value) Then .Fields("7777777777777") = Range("AH" & row).Value End If If IsNumeric(Range("AI" & row).Value) Then .Fields("other") = Range("AI" & row).Value End If If IsNumeric(Range("AJ" & row).Value) Then .Fields("88") = Range("AJ" & row).Value End If If IsNumeric(Range("AK" & row).Value) Then .Fields("cause") = Range("AK" & row).Value End If If IsNumeric(Range("AL" & row).Value) Then .Fields("dept") = Range("AL" & row).Value End If If IsNumeric(Range("AM" & row).Value) Then .Fields("outcome") = Range("AM" & row).Value End If If IsNumeric(Range("AS" & row).Value) Then .Fields("report_lag") = Range("AS" & row).Value End If If IsNumeric(Range("AT" & row).Value) Then .Fields("closed_lag") = Range("AT" & row).Value End If .Update End With row = row + 1 If row Mod 25 = 0 Then Application.StatusBar = "PL" & " - " & row DoEvents End If Loop Application.StatusBar = "Performing " & "PL" & " Batch Update..." rstRecordset.UpdateBatch '(Similar loop repeats for 5 different pieces) End Sub </code></pre> <p>Any advice is appreciated. I tried to keep it short, but it's hard when you don't know what you are really doing or what direction to go.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload