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
6 11957 NeoPa 32,556
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.
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 32,556
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 :-(
NeoPa 32,556
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.
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. - Table Name: tblInsertArray
- [First]{TEXT}
- [Last]{TEXT}
- [Address]{TEXT}
- [Zip]{LONG}
- 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
- 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
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: kamalkumar |
last post by:
Hi Please help for this simple problem
DTS Transfer or any other method?
|
by: Roman Mashak |
last post by:
Hello, All!
I wonder is it possible to define an array containing strings, not
single characters? What I want is array 'table' that will have N
elements, and every element is a strings tailoring...
|
by: ttan |
last post by:
I had 4 arrays
string LastName;
string FirstName;
string BirthDay;
string Phone;
What is the good way to store these information in a table? and when user
click on LastName/FirstName...
|
by: Henri Schomäcker |
last post by:
Hi folks,
what I need is a kind of 2D-Array which should, in the end, represent a
typical html-table and which should be sortable by column.
I thought of an implementation, where an vector...
|
by: Sandesh |
last post by:
Hello All,
I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the...
|
by: blainegray |
last post by:
Greetings
This is one of those Access is not closing Excel problems.
The first time through the code works fine. The second time there is a problem.
After lots of combinations, I finally...
|
by: umamaheshg |
last post by:
Hi All,
I tried to transfer a table in .mdb file to table in sybase database
I used following query in VB. But i m getting many errors from this.
Please correct the below query....
|
by: flavourofbru |
last post by:
Hi all...
I declared a matrix ofl arge size as follows:
int (* abc) = new int;
now I need to transfer all the contents of the above array into another array of same size. How can I do that?
...
|
by: mostafijur |
last post by:
Hello
I want to transfer database table from one pc to another pc.
1. How it possible? Pls can u tell me step by step?
2. Where the table and database store in my PC?
OS:...
|
by: nuhbye |
last post by:
I am trying to transfer output of a query to some file whose name will be available only at the run time.
My CODE looks something like this, this code is a part of stored procedure.
set...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |