How to transfer a 2d array into a table

[TheSmileyCoder]

I have a 2d array, with 10 columns, and somewhere between 200 and 10.000 rows. I was hoping there was a simple(and efficient) way to transfer the entire array into a table containing over a million rows, and growing.

I allready know how to transfer it by looping through the array, adding 1 row at a time. However, this is not that effective, and I believe that one of the problems stems from the fact that the main table has its indexes updated each time a row is added. I could probably work around this by first moving the array into a temporary table, and then use SQL to add all the rows at once.

Either way, Im still looking to see if there is a efficient way to transfer an array into a table. Work Smarter, Not Harder

TheSmileyCoder

[NeoPa]

The first idea would be to to delete the index(es) prior to adding the data, then add it (them) again afterwards. Fitting that into a single transaction might not be too good an idea though.

[Stewart Ross]

Interestingly, although it will not help you in this application, Excel has this capability built in. The contents of an array can be directly assigned to a range in code - and it is much faster to do so than to transfer the array contents line by line. Here is a simple test example with two subs, one to build an array of numbers, the other to transfer it to Excel. The transfer is breathtakingly simple.

Option Explicit

Const cCols = 10
Const cRows = 10000
Public arSomeArray(cRows - 1, cCols - 1) As Long

Public Sub SetArray()
    Dim i As Integer
    Dim j As Integer
    For i = 0 To cRows - 1
        For j = 0 To cCols - 1
            arSomeArray(i, j) = i + j
        Next
    Next
End Sub

Public Sub TransferArray()
    Dim objWs As Worksheet
    Set objWs = Application.ActiveSheet
    With objWs
        .Range(.Cells(1, 1), .Cells(cRows - 1, cCols - 1)) = arSomeArray
    End With
End Sub

Although I know this is not the kind of solution you are looking for, it seems appropriate to mention it here given that you have an array as a source object.

-Stewart

[NeoPa]

I thought I knew most of what there is to know about manipulating data on a worksheet Stewart, but I didn't know that. Very interesting. What would make it even more interesting woul be the ability to assign data back the other way, but that seems not to be available. Seems a shame, as that could prove very powerful for data manipulation in Excel :-(

[NeoPa]

I knew there was something else I was thinking about for this, but it turns out that it (GetRows()) is only applicable for dealing with reading the data into an array too. I could find nothing for transferring data from an array into your table (which seems a shame).

My best suggestion is still to remove the indices before adding the data then re-apply them afterwards. To minimise exposure to failure within the process I'd probably transfer the data first into a temporary table before removing the indices, bulk-inserting the data across to the newly, and temporarily, indexless table, then re-applying the indices. Running this within a transaction is still likely to create very heavy overheads on your database of course.

[ADezii]

After much thought, I created a System, using ADO that will enable you to transfer the contents of a 2-Dimensional Array, by only iterating thru the 1st Dimension (Rows) of the Array. The down side of this approach is that you must manually enter the Indices for the 2nd Dimension (Cols) of the Array. The gain should be substantial by eliminating an entire Looping Structure. I'll leave the testing to you.

1. Table Name: tblInsertArray
   1. [First]{TEXT}
   2. [Last]{TEXT}
   3. [Address]{TEXT}
   4. [Zip]{LONG}
2. Code Definition:
   

   Dim astrData(2, 3)
   Dim bytRow As Byte
   Dim bytCol As Byte
   Dim rec As ADODB.Recordset
   Set rec = New ADODB.Recordset
   
   rec.Open "tblInsertArray", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
   'Populate the Test Array
   astrData(0, 0) = "Herman"
   astrData(0, 1) = "Munster"
   astrData(0, 2) = "1313 Mockingbird Lane"
   astrData(0, 3) = 99999
   
   astrData(1, 0) = "Babe"
   astrData(1, 1) = "Ruth"
   astrData(1, 2) = "111 Hall of Fame Drive"
   astrData(1, 3) = 67541
   
   astrData(2, 0) = "Barack"
   astrData(2, 1) = "O'Bama"
   astrData(2, 2) = "White House"
   astrData(2, 3) = 98766
   
   For bytRow = LBound(astrData, 1) To UBound(astrData, 1)
     rec.AddNew Array("First", "Last", "Address", "Zip"), Array(astrData(bytRow, 0), _
                astrData(bytRow, 1), astrData(bytRow, 2), astrData(bytRow, 3))
     rec.Update
   Next
   
   rec.Close: Set rec = Nothing

3. OUTPUT (tblInsertArray after Code Execution):
   

   First	Last	  Address	                Zip
   Herman   Munster   1313 Mockingbird Lane	  99999
   Babe	 Ruth	  111 Hall of Fame Drive     67541
   Barack   O'Bama	White House	            98766

[TheSmileyCoder]

Thank you for your replies. In the mean time, I have been temporarily pulled from that project to put out a fire in another project. I should be back working on it next week, and will take a look at all your, as always, excellent replies.