Import Specific Rows from Large Text File using VBA

[ExcelNoob93]

Hey Everyone,

I am looking to import specific rows of data from a very large text file (2.8GB+) into a new worksheet based upon unique text found within each row.
Example Row of Data: 1234656|AV65712|ALBHM000|Blah|Blah|Blah... so on for roughly 90 columns. (The unique txt would be "ALBHM000").

Any suggestions or additional information I should provide?

Thanks!

 

[yky]

If you open a cmd window, change to the proper directory, enter the command "type myhugefile.txt|find "ALBHM000"", would you get what you're looking for? If so, you may be able to do it using shell command.

 

[ExcelNoob93]

If you open a cmd window, change to the proper directory, enter the command "type myhugefile.txt|find "ALBHM000"", would you get what you're looking for? If so, you may be able to do it using shell command.

Sadly that does not work. Looks as if excel.exe is giving me the not recognized command. Double cheeked and confirmed excel being installed and everything fun that goes with it.

 

[yky]

cmd has nothing to do with Excel. OK, try this:

Sub test()
    Call Shell("cmd.exe /S /K type c:\myhugefile.txt|find " & """ALBHM000""", vbNormalFocus)
End Sub

You need to change "c:\myhugefile.txt" to suit your case. If that large file locates at d:\documents and is named xyz.txt, you want to change it to "d:\documents\xyz.txt." You'd better copy and paste the module and change only what I said above and nothing else, not even punctuation.

Is what you see in the subsequent black window what you want to extract? If yes, we can go on. Otherwise, we'll stop here.

 

[diddi]

i am interested to see how you retrieve the value, if you would not mind showing that anyway.

 

[yky]

What I have in mind actually is something like the following, redirecting the output to a file. Then, Excel can open the file and further manipulate it. Of course, that depends on the content of the file, which I have no clue right now. I don't know the fomat and content of the original file, hence, don't know what will be extracted. Judging from what the OP said, I guess only relevant info would be extracted. If this is true, then, direct importing such info into Excel should be possible.

Sub test()
     Call Shell("cmd.exe /S /K type c:\myhugefile.txt|find " & """ALBHM000"" >C:\test.txt", vbNormalFocus)
End Sub

 

[ExcelNoob93]

That worked. Spit out everything I was looking for but looks like it cut off at 3k rows. Should have been around 58K.

Your cut above also works for me but again the initial run cuts it of at 3k rows.

 

[yky]

That worked. Spit out everything I was looking for but looks like it cut off at 3k rows. Should have been around 58K.

Your cut above also works for me but again the initial run cuts it of at 3k rows.

Are you saying you tried the second script and got an output file of 3k rows when you should get 58k rows? Then, can't use this method. I suggested this method thinking it might be faster to extract data in cmd window.

Can you open the file in Excel? What'd you get? How many columns would you get and in which column is "ALBHM000"?