Efficient way to read in text file and account for possibility of large number of rows

Hi,

I have read in files into excel but never have i tried to read in a txt file line by line. But i think this is what i need to do for this one.

I have an input test file (see enclosed)
I have an output file of what i would like the input file transferred to.

Note: I am running excel 2003 for this macro. So my max number of rows is ~65000 and the input files CAN BE larger than 65000 rows.

I am not sure how to even start reading in the file or the best way to parse the file.........any suggestions or ideas would be helpfull.

marcL,

thanks.......i am trying to run your code but i am having an issue just getting the dir/path setup.

I put in the dir and path and name of the file but it just beeps at me......i know i am doing something wrong but not sure what .........i will keep looking at it but thought i would ask

"D:\Tests4Noobs\welchs101 test file .txt",

what part of the above is just the path and what part is the file name..........

thanks again.

i am starting to think that maybe i am looking at this wrong

Please Login or Register  to view this content.

i thought TXT$ was a variable but it does not seem to be referenced anywhere else

for some reason i am not getting past this point

Please Login or Register  to view this content.

i have the dir/path in there......and the file name is "test_input_file.txt" but it does not seem to be working.......any idea what i am doing wrong

You just don't well assign the file ‼

Wait a little, I'll do a mod to select the file to open …

Please Login or Register  to view this content.

hi marc.........wow it worked!

however,the output does not contain the header information. also the cols A through O should be information from the informatin from the header section of the input file..........

but thanks a lot ..........this gives me a great start on how to do this.......just need to figure out how to get the headers in there.........see my output file in original post for what i am talking about.........

merci

marcL, i am going through your code......very nice. I will have more questions but my first is this

what is this variable

HF%

i guess i dont understasnd this

Please Login or Register  to view this content.

Originally Posted by welchs101

see my output file in original post for what i am talking about ...

Yes I saw that : it's one of the most awful import / export file structure I've ever seen ‼

'cause the thread purpose is upon the "Efficient way to read in text file" and after reading the GC Excel's answer,
I wanna just demonstrate a faster way and to bring you a good code basis as well …

Originally Posted by welchs101

i guess i dont understasnd this

Please Login or Register  to view this content.

'cause of the awful structure, this is my last step forward code :

Please Login or Register  to view this content.

this is fast ......very fast.......thanks.......i will try it out on a large number of rows but i guess it will be just fine. i am going to learn some as i go through this code in more detail.

i just have to know figure out how to get the headers in the file. but thanks alot!!!!!!!!!!!!

again i just wanted to say thanks........i really like the way you assign all the rows in the input file to an array and then manipulate the array to get the output.

the file structure is not something i can change. the input file structure is fixed by the program that collects the data. I just have to output a file structure so that we can analyze the data..........

thanks again!!!!!!!!!!!!!!!!

does anyone know what this is doing

Please Login or Register  to view this content.

thanks.........for some reason i was just not seeing that....thanks that makes sense

just curious why it equals -1 for true instead of 1

thanks......

ok, i have been going through this code learning a lot........but i have to admit i am not sure i know the best way to somehow incorporate the "headers" .........anyone have a suggestion?

If you want the header identical to what you have, it needs to be modified further.
See if this is good enough.

Please Login or Register  to view this content.

i am just amazed at the quality of programming you guys do..........more still to learn............

i will review this as well. thanks.

Forgot to add one line

Please Login or Register  to view this content.

To delete unnecessary comma after the last column.

jindon, i am trying to figure out where in your code you reference the headers........i see the text "waferid" but i dont see the text for example "HdrOperatorID"......where is this in the code

In one of

Please Login or Register  to view this content.

thanks. i dont know why i did not see that the first time......guess i was tired.

does this

Please Login or Register  to view this content.

read the ENTIRE file into the variable "txt"?

Correct.

1) read the file
2) generate header + prefix for each new records from beginning part up to <EOH>.
3) add "Wafer_ID" to header + prefix from the line contains "LotID".

2) + 3) for static header + prefix to each recods.

4) new record will be determined to find the line starts form "Site".
5) get header for "SiteX", "SiteY" and "BIN", then allocate corresponding value.
6) Else, use 1st part split by a comma to get header, and 2nd part for data.

thanks jindon......as always many many thanks.

I did have another question either for you or someone else if they can answer it:

What is going on here in this section of code.....why did you do this I am trying to figure it out....

Please Login or Register  to view this content.

ok...I think I understand.......your basically just copying array "a" contents to new array "temp"...........but did you use application.index is there some advantage to use thing .........also are there limitations to using this?

It is for CSV out put.

"a" is a 2D array and can be out put in a cells, but not to csv file as it is.

Need to convert it to a single D array to use with Join function later, so those lines are converting each row of array "a" to temp joining each element with a comma.

Do a step debug, so that you will see what is going on.

when I have a large file......ie...many rows of data in the text file........

I get a runtime error at the following location:

Please Login or Register  to view this content.

Is there a limitation on the "Application.Index"......ie....will it not work for "large" arrays?

try change that line to

Please Login or Register  to view this content.

that fixed it!


Just curious though........is there a "limit" to the application.index function? or should I submit another post for this question?

thanks.

Yep, it has a limit that is 2 ^ 16.

So, it will not work when Col/Row has more than 65536.

jindon,

in the last two weeks I have run into situations where vba-excel type function have 2^16 limitations........is there a list of vba-excel type functions with this limitation so I can know ahead of time.........
thanks.

I don't know about the documentation for the limit.

Known limit for me is Transpose/Index functions, try not to use them and simple loop is faster than using such functions for a bulky data.

thanks for the info. I am starting to get a sense of "don't use excels built in worksheet type functions" for large data sets.

I did have another question:

Consider the following code:

Please Login or Register  to view this content.

this creates an array of values. However, the first element in the array is nothing......

Is there a way to get the first element in the array to be the first line after the "<EOH>"

maybe so

Please Login or Register  to view this content.

Please Login or Register  to view this content.

jindon, that worked.


nilem, I actually tried the one you suggested before I made the post because I thought that should work........but for some reason it did not......I guess that space in the string is perhaps not really a space........