Store text file in an array

[YasserKhalil]

Hello everyone
I have text file and I need to store it in an array
The lines needed to be stored is after this line

Please Login or Register  to view this content.

till before the last line

Please Login or Register  to view this content.

Then to sort the array based on the column of total in descending way ..
Note : I don't need to open the text file as excel file .. I just need to deal with the text file directly

[Winon]

Hello YasserKhalil,

You may find some good guidelines here: https://www.thespreadsheetguru.com/b...ide-text-files

Hope you find it useful.

Kind Regards.

[mgs73]

Try this:
(I'm sure it's possible to sort in VBA, but easier in a worksheet!)

Please Login or Register  to view this content.

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot for all of you .. You are really awesome
@mgs : your solution is perfect but in fact I will deal with about 2000 text files with the same structure so creating temp sheets I think will take longer time so you noticed that I told in first post that I want to deal with the text files directly

@karedog your solution is awesome ..if possible I need three points :
First: I want to skip the line that start with AVG
Second : After seeing the results I found that some totals are the same so in this case I want to sort the first column in descending
order
Third : I need to get rid of the characters (* and +) from the numbers

Thank you very very much for great help

[karedog]

You are welcome Mr.Yasser Khalil, thanks for rep.points.

Please Login or Register  to view this content.

[YasserKhalil]

Awesome Awesome Awesome Mr. karedog
That's exactly what I was seeking for
Thank you very very much for incredible and wonderful help
Best Regards

[karedog]

You are welcome Mr.Yasser Khalil, glad I can help.

Regards

[YasserKhalil]

I am so sorry for disturbing you my friend
I have two empty rows in results in this sample ..
If there is a fix just point it to me please

[karedog]

In case the blanks must be located at the end :

Please Login or Register  to view this content.

[YasserKhalil]

Now it is perfect .. Thanks a lot for great and continuous help
Best and Kind Regards

[karedog]

You are welcome Mr.Yasser Khalil.

Regards

[xlnitwit]

Hi,

Here's an ADO option, which I wrote mostly to refresh my own memory!

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. xlnitwit for your great contribution

[YasserKhalil]

@Mr. Karedog
I have this sample text file that I tried to do the same but with column named "Hits"
Tried this line instead

Please Login or Register  to view this content.

But it doesn't do the trick
Can you explain what should I edit or fix in the code ..?
Please don't edit the whole code .. just point me out the changes if possible

In this sample column Hits will be as Total from previous sample ....so I need to sort this "Hits" column in descending order ...
the same strategy except for the header of the column and the column number
In previous sample: the header was "Total" and it was column number 7
In this sample : the header is "Hits" and the column number is 12

Thanks a lot for great help

[karedog]

The purpose of this line :
Split(Input$(LOF(1), 1), "Total" & vbCrLf)(1)
is to discard anything left to "Total" in the whole string, in other words, to discard the first two lines of data of file Sample.txt.

Lets take a look at this code :

Please Login or Register  to view this content.

As you can see, if str1 is splitted using "jumped ", the result of Split(...)(0) is the left part, while Split(...)(1) is the right part.

The same thing applied to previous Sample.txt, the first three lines are :
"Test Draw 5811, Thu 05/01/2014","Test Draw 5811, Thu 05/01/2014","Test Draw 5811, Thu 05/01/2014","Test Draw 5811, Thu 05/01/2014","Test Draw 5811, Thu 05/01/2014","Test Draw 5811, Thu 05/01/2014","Test Draw 5811, Thu 05/01/2014"
##,#02,#09,#10,#20,#36,Total
06,12,16,8,15,16,67
so if we split using Split(str1, "Total" & vbCrLf)(1), the result is the right part after "Total" & vbCrLf, in other words, we take the string start from line 3 and afterwards.

So "Total" here is the 'end string' of the line to be discarded, not the string of the column we want to sort.

In your new Sample.txt file, the string that acts as delimiter to discard unwanted left part is "Hit Ratio" (there is a double quote) at the end :
##,Out,Out/Med,Longest,L10,L5,Last,Avg,Med,"Std Dev","Exp Hits",Hits,"Hit Ratio"
01,29,5.8,51,0,0,3,8.2,5.0,8.6,231.2,204,88.2%
so the new code should be :

Please Login or Register  to view this content.

---

On your first Sample.txt, the last line is : AVG,12.7,12.9,13.9,14.9,14.8,69.1
while on your last Sample.txt, the last line is : Avg,8.44,0.04,44.56,1.22,0.61,6.32,8.00,4.77,7.63,231.2,231.2,100.0%

Notice that the first one the "AVG" is all capital letters, while the latter is not, so this line code must be changed to :

Please Login or Register  to view this content.

---

In previous Sample.txt, you want to sort based on 7th column, while on new file, you want to sort based on 12th column so :

Please Login or Register  to view this content.

---

Complete code :

Please Login or Register  to view this content.

[YasserKhalil]

OMG Thank you very very much for that masterpiece explanation. That's wonderful and I liked it a lot

Can this wonderful code be converted to public procedure .. so as to be easier to use with many structures ...?

I imagine
Sub GetFromTextAndSort(strPath as string,colNumber as integer,sep as String,Optional colToExclude as integer)

And use it in this way
GetFromTextAndSort ThisWorkbook.Path & "\Sample.txt",12,"Hit Ratio""",13

Best Regards

[YasserKhalil]

I have tried this

Please Login or Register  to view this content.

But couldn't figure how to use c12 in the parameters of the public procedure

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

OMG That's the most wonderful code I have ever seen. It helped me to deal with about 4000 text files in less than a minute
Thank you very very much Mr. karedog for this masterpiece
Best Regards