Copy specific lines from text files into excel

Question

I have developed the below code to open a large number of text files (within the same folder) and copy everything from each file into excel (one cell for each line of text file & one row for each text file).

However, I do not require all of the data from the text files and it is slowing down the process. The text files are in following format:

DATASET UNSTRUCTURED_GRID
POINTS 5 float
0.096853 0.000000 0.111997
0.096853 -0.003500 0.111997
0.096890 0.000000 0.084015
0.096853 -0.003500 0.111997
0.096890 -0.003500 0.084015
CELL_DATA 5
SCALARS pressure float 1
LOOKUP_TABLE default
-0.000000
-0.000000
-3.000000
-2.000000
-6.000000

The data that I need to copy from this file is the second batch of numbers (below "LOOKUP_TABLE default"). The number of lines in this example is five (as stated on line starting "CELL_DATA" but this number can change from file to file.

In summary, I'm looking my code to only copy this last batch of numbers into excel instead of everything but I'm at a loss on how to tackle this.

Any help or advice would be greatly appreciated.

Sub ImportTextFile()

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As String
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Long
Dim SaveColNdx As Integer

FName = "E:\zdump\"
MyFile = Dir(FName & "*.txt")
Sep = vbLf

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Do While MyFile <> ""
    Open (FName & MyFile) For Input As #1

    While Not EOF(1)
        Line Input #1, WholeLine
        If Right(WholeLine, 1) <> Sep Then
            WholeLine = WholeLine & Sep
        End If
        ColNdx = SaveColNdx
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
            TempVal = Mid(WholeLine, Pos, NextPos - Pos)
            Cells(RowNdx, ColNdx).Value = TempVal
            Pos = NextPos + 1
            ColNdx = ColNdx + 1
            NextPos = InStr(Pos, WholeLine, Sep)
        Wend
        RowNdx = RowNdx + 1
    Wend
    Close #1
    MyFile = Dir()
    Debug.Print text
Loop End Sub

score 0 · Accepted Answer · edited Aug 04 '17 at 09:37

0

Give this a try:

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As String
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Long
Dim SaveColNdx As Integer
Dim SaveRowNdx As Long
Dim FoundData As Boolean
Dim NumberOfData As Long

FName = "E:\zdump\"
MyFile = Dir(FName & "*.txt")
Sep = vbLf

ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
SaveRowNdx = RowNdx

Do While MyFile <> ""
    Open (FName & MyFile) For Input As #1

    While Not EOF(1)
        Line Input #1, WholeLine

        If Right(WholeLine, 1) <> Sep Then
            WholeLine = WholeLine & Sep
        End If
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
            TempVal = Mid(WholeLine, Pos, NextPos - Pos)

            If FoundData = False Then
                If InStr(TempVal, "CELL_DATA") Then
                    NumberOfData = Val(Right(TempVal, Len(TempVal) - Len(Left(TempVal, Len("CELL_DATA") + 1))))
                End If

                If InStr(TempVal, "LOOKUP_TABLE default") <> 0 Then
                    FoundData = True
                End If

                Pos = NextPos + 1
                NextPos = InStr(Pos, WholeLine, Sep)
            Else
                If NumberOfData <> 0 Then
                    Cells(RowNdx, ColNdx).Value = TempVal
                    Pos = NextPos + 1
                    RowNdx = RowNdx + 1
                    NextPos = InStr(Pos, WholeLine, Sep)
                    NumberOfData = NumberOfData - 1
                End If
            End If
        Wend

    Wend
    Close #1
    FoundData = False
    ColNdx = ColNdx + 1
    Cells(SaveRowNdx, ColNdx).Activate
    RowNdx = SaveRowNdx
    MyFile = Dir()
    'Debug.Print Text
Loop

edited Aug 04 '17 at 09:37

slartidan

20,403
15
83
131

answered Jul 26 '17 at 11:57

Rafael Matos

258
2
5

Thank you kindly for this code!!! Much appreciated. It is working perfectly apart from one thing regarding my text files. This code does not seem to find the line breaks in my text files, instead treating the text as one continuous line. In my initial code you will see that I had to define a separator (Sep) as 'vbLf' which I was able to use to show the line breaks in the text files. Is there a simple way of incorporating this back into the code? – Jon Jul 26 '17 at 13:19
I didn't understand why you need to find the line breaks. I ran your code and it did the same as mine: treated every line as a single text and put the whole line in a single cell. @Jon – Rafael Matos Jul 26 '17 at 13:27
Hi Rafael, the issue is that for my text files it cannot find where each line of the text files ends and rather treats the whole file as one very long line. I'm not sure why this happens, I am guessing it's some kind of character encoding unix line endings something or other with my text files that the code doesn't pick up. The link below talks about this very issue. https://www.mrexcel.com/forum/excel-questions/787225-vba-reading-text-file-parsing-line-breaks-carriage-returns.html – Jon Jul 26 '17 at 13:54
@Jon I re-edited the code. Since I don't have the right text file like you, I'm not sure if it will works out. But you can try. – Rafael Matos Jul 26 '17 at 14:31
Thanks so much Rafael. that now works perfectly for me. Much appreciated :) – Jon Jul 26 '17 at 15:07
@Jon You're welcome. Just hit this answer as useful, please. – Rafael Matos Jul 26 '17 at 15:11
Done. One thing I have noticed since is that it no longer reads the number beside "CELL_DATA" and use that number to copy the correct number of values beneath it. Instead it just copies everything beneath the line "LOOKUP_TABLE default". :/ – Jon Jul 27 '17 at 11:00
@Jon You could debug the program and see what the value of NumberOfData are getting. – Rafael Matos Jul 27 '17 at 12:45
Not sure how to return the value of NumberOfData that the code is finding :/ – Jon Aug 01 '17 at 14:18
@Jon You can use the Watch to see the values of your variables. To use that, choose the menu Debug and then select "Add Watch". In the dialog box, put the name of the variable you want do watch and then debug your code using `F8`. – Rafael Matos Aug 02 '17 at 10:07
Thanks Rafael. I'm not the most competent with VBA. FYI I found a typo to one of the NumberOfData variables which cause the issue. Sorted now :) – Jon Aug 04 '17 at 08:45

Copy specific lines from text files into excel

1 Answers1

Linked