469,312 Members | 2,524 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

How to transfer a 2d array into a table

2,321 Expert Mod 2GB
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

Sep 1 '11 #1
6 11003
32,173 Expert Mod 16PB
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.
Sep 1 '11 #2
Stewart Ross
2,545 Expert Mod 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  3. Const cCols = 10
  4. Const cRows = 10000
  5. Public arSomeArray(cRows - 1, cCols - 1) As Long
  7. Public Sub SetArray()
  8.     Dim i As Integer
  9.     Dim j As Integer
  10.     For i = 0 To cRows - 1
  11.         For j = 0 To cCols - 1
  12.             arSomeArray(i, j) = i + j
  13.         Next
  14.     Next
  15. End Sub
  17. Public Sub TransferArray()
  18.     Dim objWs As Worksheet
  19.     Set objWs = Application.ActiveSheet
  20.     With objWs
  21.         .Range(.Cells(1, 1), .Cells(cRows - 1, cCols - 1)) = arSomeArray
  22.     End With
  23. 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.

Sep 3 '11 #3
32,173 Expert Mod 16PB
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 :-(
Sep 3 '11 #4
32,173 Expert Mod 16PB
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.
Sep 3 '11 #5
8,800 Expert 8TB
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim astrData(2, 3)
    2. Dim bytRow As Byte
    3. Dim bytCol As Byte
    4. Dim rec As ADODB.Recordset
    5. Set rec = New ADODB.Recordset
    7. rec.Open "tblInsertArray", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    9. 'Populate the Test Array
    10. astrData(0, 0) = "Herman"
    11. astrData(0, 1) = "Munster"
    12. astrData(0, 2) = "1313 Mockingbird Lane"
    13. astrData(0, 3) = 99999
    15. astrData(1, 0) = "Babe"
    16. astrData(1, 1) = "Ruth"
    17. astrData(1, 2) = "111 Hall of Fame Drive"
    18. astrData(1, 3) = 67541
    20. astrData(2, 0) = "Barack"
    21. astrData(2, 1) = "O'Bama"
    22. astrData(2, 2) = "White House"
    23. astrData(2, 3) = 98766
    25. For bytRow = LBound(astrData, 1) To UBound(astrData, 1)
    26.   rec.AddNew Array("First", "Last", "Address", "Zip"), Array(astrData(bytRow, 0), _
    27.              astrData(bytRow, 1), astrData(bytRow, 2), astrData(bytRow, 3))
    28.   rec.Update
    29. Next
    31. rec.Close: Set rec = Nothing
  3. OUTPUT (tblInsertArray after Code Execution):
    Expand|Select|Wrap|Line Numbers
    1. First    Last      Address                    Zip
    2. Herman   Munster   1313 Mockingbird Lane      99999
    3. Babe     Ruth      111 Hall of Fame Drive     67541
    4. Barack   O'Bama    White House                98766
Sep 3 '11 #6
2,321 Expert Mod 2GB
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.
Sep 7 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by kamalkumar | last post: by
7 posts views Thread by Roman Mashak | last post: by
1 post views Thread by ttan | last post: by
1 post views Thread by Sandesh | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.