Posts by Doc.AElstein

Edit P.S. Possibly the problems I encountered in your file are the results of a lot of conditional formatting you have in your file, which I have no experience with. So forget my comments about corruption in your original file. ( Although I have heard some experts comment that conditional formatting is a form of corruption , Lol )

Here is your file again with the my macro in it, in your original form:

_._____________________________________________________

Share ‘Sample excel file.xls’ : https://app.box.com/s/hw4uxwjlm8t8zty17kc07xihq0bfhifs

Share ‘Sample excel file.xlsm’ : https://app.box.com/s/ccmk5sgazueejb4dc0eqw6yex0zhjar2

Hello CapG

This does not seem too difficult, - you have a fairly well organised text file .

I have done a simple macro, Sub LookInAndImportTextStringSample() , to get you started.

There are probably lots of different ways to do this.

You can import the text file into Excel, I would suggest using VBA initially , put the data in an array, usually a 1 dimensional array of rows is convenient to start with, then go through that array, picking out the data you want.

( BTW, you have what I would call a text file. It can be opened by Excel, Notepad , and a lot of other software. It is not “in notepad” and it is not a notepad or a notepad file. At least that’s how I understand it. I could be wrong

( Also, I personally probably wouldn’t call your text file a comma separated text file, since I think that usually implies it contains a fairly regular data table , separated by commas. ) )

The only thing I can see that might cause awkward problems is that you are dealing with comparing dates and times. I have simply copied your date text strings accross to the Excel file for now.

There seems to be some inconsistency between the Excel file and the text file in the exact text used for the Header. You’ll need to consider that and tidy up either you text file or Excel file. For the purposes of getting you started with a macro I have modified your Headers in the Excel file so that they tie up with those in the sample file.

( The macro could be adjusted to match similar words as an alternative, if you can’t control the exact words used, but I am keeping it simple initially )

Also your time format is different in different sections in the text file. I have simply copied your date text strings accross to the Excel file for now.

This is basically wot I done in the macro for you:

It looks like the conventional “hidden” characters of vbCr and vbLf are used as line separators in your text file, so we can use that to Split the text file string into a 1 D array where each element has the text from a single line

I make an array to put the required output into, arrOut(). For convenience, to simplify things later, I pick a range to capture via use of .Value, that makes the array, and the specific range I choose so that the second dimension ( “column” ) will coincide with the day. So my range starts at column B , and goes up to AC, so that conveniently gives me 1 To 31 in the second dimension.

After that its just tedious basic looping stuff to go through and match headings and put the data in the appropriate place.

The macro I did certainly isn’t the most efficient, but it should be easy for you to follow through, figure out what’s going on, and modify to suit your actual data.

An initial look at the results , suggest that the macro is doing what you want, but I leave it to you to check it thoroughly

Alan

P.S. Whenever I tried to do anything at all in your Excel file it took ages and sometimes crashed. Possibly something is corrupted in it. You don’t have much in it, so I would recommend you dump it and start a new file

Quote from rory

You can't .........

Sn-ap

Hi Rony. You possibly missed the point , like I did on first read.

The OP actually has in t1 a 1 D array. In that array he has 5 elements. Those elements are themselves 1 D arrays with 6 elements in them. ( Maybe you might call that an “Unjagged” jagged array?,I am not too sure what the definition is of a jagged array)

What the OP wanted to do is, rather than looping, to apply a Index type thing to that t1 1 D array of 1 D arrays, so as return him a similar 1 D array of 1 D arrays but with just 3 of the previous 6 elements in the “inner” arrays.

I was surprised to find that you can actually do that, all be it in a limited way. .._

_... The thing what surprised me is that it appears that Index treats that t1 1D array of 1 D arrays as if it was a 2 D array. So that means you can in fact apply that code line like you and Fluff suggested to get what he wanted. Its all in my post

Alan

@ rabsofty

Hi

I am not quite sure what you are missing, because I am not quite sure what your thinkings are behind what you were trying to do. You may be getting slightly mixed up with slicing an array (https://usefulgyaan.wordpress.…t-loop-application-index/ ) and doing the the “Index(array(), Rws(), Clms()) ” type of formula mentioned in Fluff’s post # 2 , Slicing A 2d Array

( Having said that, there is no reason why you should not use combinations of those 2 techniques… )

I thought initially I could actually do what you wanted, but then on closer inspection of what you actually want , I found I could do it, but probably not quite as simply neatly, or dynamically as you would like.._

_... Like Fluff I got a bit lost in your long macros in the uploaded file. You seem to be doing everything in an untypical long way around. Or maybe it is just a way that I am unfamiliar with. But It is interesting following through your uploaded files, since you have 'commented it very well, but it takes a while. ( a small typo: your macros look for a logs.dta file, - it should be logs.txt )

But anyway, if I have understood correctly, it all boils down to

_a) you have a “jagged” array, that is to say an array containing arrays as elements – an array of arrays.

and

_b) you want to use that often seen “Index(array(), Rws(), Clms()) ” type of formula, ( or a combination of that and the slicing idea) , to have the same “jagged” array as output but with “column” elements reduced in the arrays that are the elements of the main “outer” array

( It threw me off for a while you asking for getting specific columns, as did also throw me off your Thread title containing 2d array, since , initially anyway, we are looking at 1 Dimensional arrays…. Or as it finally comes out, maybe we aint…

Let me try to explain a bit more …

_a) you end up at a point in your macro Sub logdata1() , with an array of arrays, t1

That is to say, a main array t1, whose elements are themselves, arrays. Let me call those the “Inner” arrays. ( I think what you end up with is sometimes called, as suggested, a jagged array. I suppose in your case we might call it an “unjagged” jagged array, since you have the same number of elements in each of the “inner” arrays . )

Showing it pseudo pictorially, simplified, you end up with something like this

a b c d e __ f g h i j __ k l m n o

In that simplified example, you have a main 1 D array with three “inner” 1 D arrays in it. Each “inner” array has 5 elements in it. ( I guess we could those 5 elements 5 “columns”, but that is a debatable point in a 1D array )

_b) What you want, ( which took me a while to realise, ) is to have a simple code line, without the need to do any looping to get an output, also a “unjagged” jagged array, like, pseudo

b c d __ g h i __ l m n

Loosely speaking, to offend and upset the academics, we can say you have picked out columns 2 , 3 and 4

You wanted to do that like in this code line of yours

tfn = awf.Index(t1, 0, Array(2, 3, 4))

As you found, it did not work, and I have not been able to figure out yet how to do it exactly in the way you tried with a simple “Index(array(), Rws(), Clms()) ” type code line..

But

It seems as if Excel holds a jagged array like yours somehow as if it was actually a 2D array like, pseudo pictorially

a b c d e

f g h I j

k l m n o

At any rate, Index seems to handle it as if it were that 2D array

So you can get the answer you wanted like this

tfn = Array(awf.Index(t1, 1, Array(2, 3, 4)), awf.Index(t1, 2, Array(2, 3, 4)), awf.Index(t1, 3, Array(2, 3, 4)))

It gets your answer without looping, but the problem is that it is not dynamic, and I can’t see yet an easy way to make it dynamic

_._______________

Out of passing interest, I was surprised to find that Excel was apparently holding such a jagged array as if it was 1 2D array. . I did not expect that. I expect other people wouldn’t?

Out of interest I would welcome any comments on that from people familiar with these Index way of doing things to return parts of an array. Maybe someone else noticed it.

BTW , I can think of one quite useful application of it. …. The following…

There is a fairly common way of importing a text file into an array and/ or into a spreadsheet: .. it is like this…A single complete text string of the text file is first put into a simple 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” ) – At each of these “inner” loops we fill either a spreadsheet or an array with an element

What we can 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 )

I expect that might be a bit more efficient, but I am not sure.

In the uploaded file, I have put two example text import macros which work on your logs.txt file saved at the same place as the file. One does the more typical way, the other demonstrates the way I tried to explain…

Neither macro is particularly efficient, - I am mainly trying to demo what I was on about

Alan