Hello
I thought it would be worthwhile and interesting to add a follow up post here for a couple of reasons
_1) I noticed something a while back here at the Lounge while answering a question ,
https://eileenslounge.com/viewtopic.php ... 91#p266691 , which allows, coincidentally , for an improved way to import text files by VBA , which I used a few times now already , and then , coincidentally the last time I did it, yesterday, (
https://www.ozgrid.com/forum/index.php? ... ost1239241 ) , I noticed that …
_2) This alternative way to import text files by VBA coincidentally also gets over the last problem we had here in this thread….that problem was:
when i click on any cell that has output there is an option numbers stored as text, convert to numbers,help on this error,ignore error,edit in formula bar,error checking options(these are the options coming)
_1) What was noticed is that a 1 D array containing elements that are themselves 1 D Arrays ( sometimes I think referred to as jagged arrays ) appear to be held by Excel as if it were a 2 D array, at least as far as
Index first argument is concerned.
Just to explain that pictorially.. if this was my actual jagged array, say a 1 D array with 3 Elements, which are themselves arrays containing two arbitrary values, pseudo like …_
-…then we can actually treat it for the first argument of
Index as if it was a 2D array. Pseudo like
We can use this strange phenomena to improve the way I did my text import macro in this and other similar codings…
The previous way I did, which is a very common way people use, is:
You bring in the entire text string that is the complete text file, into a single string variable. Then..
_(i) That string is split by the line separator to get a 1 D array of all the lines ( “rows” )
_(ii)a That array is looped through , and at each loop we split again each element by the value separator to get a 1D array of the “columns”, and then further
_(ii)b at each loop we loop again along the elements ( “columns” ) in that 1 D "columns " array– At each of these “innermost” loop points we fill either a spreadsheet or an array with an element
What we can now do is do away with the inner loop _(ii)b and instead at _(ii)a just fill a 1 D array with the 1 D array of the “columns”
Then finally, to get our array / range we apply a code line of pseudo like
Index( OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..} )
( Of course you can pick out whatever set of rows or columns that you want instead )
_2) The last problem we got in this Thread, does not seem to occur in this way. (I suppose this is probably because the way Index works is sort of, it houses each returned value in a
Variant and then somehow something looking like a number gets guessed correctly to be a number when that is pasted out to spreadsheet.
I have not seen this phenomena mentioned before. It seems quite useful. The improved text import way is one use of it.
I am curious if anyone else either came across it, or maybe can think of useful ways of using it?
The macro in the
next post is almost the same
macro I provided previously, but using the
Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})
The important differences are this bit .._
Code: Select all
Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To 10)
Rem 3 An array is built up by _....
Dim Cnt As Long
For Cnt = 1 To RwCnt ' _.. considering each row of data
Dim arrClms() As String
Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare) ' ___.. splitting each row into columns by splitting by the comma
Dim Clm As Long '
For Clm = 1 To UBound(arrClms()) + 1
Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
Next Clm
Next Cnt
_.. gets changed to
Code: Select all
Dim arrOut() As Variant
Rem 3 A 1D array of 1D arrays is built up by _....
Dim Cnt As Long
Dim arrUnjgdJgdRws() As Variant: ReDim arrUnjgdJgdRws(1 To RwCnt)
For Cnt = 1 To RwCnt ' _.. considering each row of data
Dim arrClms() As String
Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare) ' ___.. splitting each row into columns by splitting by the comma
Let arrUnjgdJgdRws(Cnt) = arrClms() ' Fill the next 1 D array element in the unjagged jagged array of rows
Next Cnt
'3b Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})
Let arrOut() = Application.Index(arrUnjgdJgdRws(), Evaluate("=Row(1:" & RwCnt & ")"), Evaluate("=Column(A:J)"))
That change is to remove the last columns loop and replaces it by putting the array of columns into the next element of our 1 D Unjagged Jagged Array of rows, that is to say each element of the main 1D array is a 1 D array of all the column values.
Then finally we need an extra line to get the final 2 D range array via the
the
Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})
In short, we replace a loop that is itself done lots of times with a single
Index type code line. In other words we replace a loop of loops with a single
Index type code line.
Potentially I would have thought that it could be a significant improvement in time, but I don’t personally have enough experience with real data to give typical figures
Alan
P.S. At first glance this way appears to be limited to “unjagged” jagged arrays, ( That is to say the element number must be the same in all the inner 1 D arrays ) , so I modified the first line in the OPs text data file since just that first line had a different number of values in it than the other 15265 rows . I might think of a way to over come this limitation later.
_.___________________
macro.xlsb : https://app.box.com/s/naxiaan0ch7vx5j0dpulguuoothv8rr8
NSEVAR_UnjJg.txt : https://app.box.com/s/449x8qvqjp3vs6ruz3vs6befui5fogzd I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
