Parsing of file

[johndoe_]

Hi,
I'm trying to parse out some information from a file. I want to get everything between "D 12" and "D 13".

When I've managed that I want to get out data one after one. The format is given below:

...
lots of text
...
 D 12  Final trend cycle
  From  2002.Jan to 2005.Dec
  Observations         48
  Trend filter    23-term Henderson moving average
  I/C ratio        6.20
 -----------------------------------------------------------------------------
               Jan      Feb      Mar      Apr      May      Jun 
               Jul      Aug      Sep      Oct      Nov      Dec        TOTAL  
 -----------------------------------------------------------------------------
  2002          49.      52.      54.      57.      60.      63.
                66.      68.      71.      74.      76.      77.          766.
 
  2003          78.      78.      78.      78.      78.      77.
                77.      76.      76.      77.      78.      79.          931.
 
  2004          80.      81.      82.      83.      84.      85.
                86.      87.      88.      88.      88.      89.         1020.
 
  2005          89.      90.      91.      92.      93.      94.
                95.      96.      96.      96.      96.      96.         1125.
 
  AVGE          74.      75.      76.      78.      79.      80.
                81.      82.      83.      84.      85.      85.

  Table Total-      3842.39   Mean-      80.05   Std. Dev.-      12.06
                              Min -      48.61        Max -      96.39
        ICMETI,  Total Inventories Communications Equipment            PAGE  10, SERIES foo

 D 13  Final irregular component
...
lots of text
...

I've tried this to get data:

Sub Command1_Click() 
    text1 = "" 
    Dim tempvar As String 
    Open "I:\Jørgen\diverse\test\FOO.OUT" For Input As #1 
    Do While Not EOF(1) And LCase(tempvar) <> " D 12" 
        Input #1, tempvar 
    Loop 
    If Not EOF(1) Then 
        Do While Not EOF(1) And LCase(tempvar) <> " D 13" 
            Input #1, tempvar 
            If LCase(tempvar) <> "stop" Then 
                text1 = text1 + tempvar + Chr(13) + Chr(10) 
            End If 
        Loop 
    End If 
    Close #1 
    Debug.Print text1 
End Sub

but without success.. If I input som other strings into the Sub like f.ex "start" and "stop" and also enter those data into the file I'm parsing it works OK. But I really need to do it without manually entering "start" and "stop" into the file..

Any suggestions?

Thanks in advance for any help!

[westconn1]

f1 = FreeFile
Open "I:\Jørgen\diverse\test\FOO.OUT" For Input As f1
    strfi = Input(LOF(f1), #f1)
    Close f1
    
pos = InStr(1, strfi, "D 12")
pos1 = InStr(pos + 1, strfi, "D 13")
mytest = Mid(strfi, pos, pos1 - pos)
Debug.Print mytest

give this a try to see if it does what you want


this will put D12 in the string, but not D 13, just adjust you start and length in the Mid to get what you want


pete

[johndoe_]

It does!

But then my problem is how to parse the data I've receivind into the String mytest.

mytest is now equal to the following:

D 12  Final trend cycle
  From  2002.Jan to 2005.Dec
  Observations         48
  Trend filter    23-term Henderson moving average
  I/C ratio        6.20
 -----------------------------------------------------------------------------
               Jan      Feb      Mar      Apr      May      Jun 
               Jul      Aug      Sep      Oct      Nov      Dec        TOTAL  
 -----------------------------------------------------------------------------
  2002          49.      52.      54.      57.      60.      63.
                66.      68.      71.      74.      76.      77.          766.
 
  2003          78.      78.      78.      78.      78.      77.
                77.      76.      76.      77.      78.      79.          931.
 
  2004          80.      81.      82.      83.      84.      85.
                86.      87.      88.      88.      88.      89.         1020.
 
  2005          89.      90.      91.      92.      93.      94.
                95.      96.      96.      96.      96.      96.         1125.
 
  AVGE          74.      75.      76.      78.      79.      80.
                81.      82.      83.      84.      85.      85.

  Table Total-      3842.39   Mean-      80.05   Std. Dev.-      12.06
                              Min -      48.61        Max -      96.39
        ICMETI,  Total Inventories Communications Equipment            PAGE  10, SERIES foo

I want to get all the values from each year, but not the total.
That is a list with the values (49, 52, 54, 57, ...., 83, 84, 85, 85)

Is this almost impossible? I can't think of a fast way to do this myself

[Static]

each array element of Data(x) will = all the data for that year... so in this case it would be like
Data(0) = "49,52,54,57,60,63,66,68,71,74,76,77"

Dim Data() As String
 
Private Sub GetData()
Dim tmp() As String
Dim cnt As Integer
    Open "C:\Path\to\File.txt" For Input As #1
        tmp = Split(Input(LOF(1), 1), vbCrLf)
    Close #1
    
    
    cnt = 0
    For x = 0 To UBound(tmp)
        If IsNumeric(Left(tmp(x), 4)) Then
            ReDim Preserve Data(cnt)
            Data(cnt) = Mid(tmp(x), 15, 55)
            Data(cnt) = Data(cnt) & Mid(tmp(x + 1), 15, 55)
            Data(cnt) = Replace(Data(cnt), ".", ",") 'change . to ,
            Data(cnt) = Replace(Data(cnt), " ", "") 'remove spaces
            Data(cnt) = Left(Data(cnt),Len(Data(cnt))-1)
            x = x + 1
        End If
    Next
End Sub