External Text File Read, Parse, Import

[hayden]

Hello again everyone.

I've got another problem that is over my head. I'm trying to take a MODFLOW output file(basically just a text file) that is ~ 9 million lines and extract only a set portion of data from it for import into excel.

The data that I need from the text file always begins at the line that starts with

 {14 spaces}HEAD IN LAYER{3 spaces}1 AT END OF TIME STEP

Note: the {14 spaces} is just that, 14 blank characters. The time step number changes throughout the file so there is other text after this, but the beginning is always those characters. I need the data from that line through the next 13565 lines copied/imported into excel.

Once this data has be imported, I would like to continue to move through the text file until the next occurrence of

 {14 spaces}HEAD IN LAYER{3 spaces}1 AT END OF TIME STEP

is found and repeat the procedure, only importing it into a new worksheet. This would continue until I reach the end of the file.

I did a search and found this code, but the whole reading from a text file is so foreign to me I'm not sure I know what it is doing.

Sub snb()
  Open "C:\ExcelTemp\M4A00P13.out" For Input As #1
    sq = Split(Input(LOF(1), #1), "              HEAD IN LAYER")
  Close #1
 
  For j = 0 To UBound(sq)
    With ThisWorkbook.Sheets.Add
      sn = Split(sq(j), vbCrLf)
      .Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
    End With
  Next
End Sub

Any help or direction is greatly appreciated. Thanks all.

 

[hayden]

Well I found another way... I used unix and the grep command.

Specifically:

grep -A 13565 "HEAD IN LAYER   1" inputfile.out >outputfile.out

This takes the text file and creates another text file that only includes the data after the Head in layer line. With that I used this scrip to import it into excel.

http://www.mrexcel.com/forum/showthread.php?t=26267

If you are on a windows machine, you can download cygwin for a unix shell that will allow you to run these types of commands.