Thread: How to import specific data from text file (and each time of different size) to excel

How to import specific data from text file (and each time of different size text file) to excel?

I have attached an example of the text file.

The data in the text file may look like this:

start of the txt file*******************************************************

2160. WST 0.3 MEMB 1 TO 1824
2161. *STA 0 ALL
2162. *PROJEKTUOJAMI ELEMENTAI
2163. DESIGN ELEMENT 1 TO 1824
STAAD SPACE -- PAGE NO. 42



SLAB/WALL DESIGN RESULTS
(by stresses in local axis for load carrying capacity)
------------------------------------------------------------------------------
Element Asx Mx Nx Load. N. Asy My Ny Load N.
sq.cm/m kNm/m kN/m (X) sq.cm/m kNm/m kN/m (Y)
------------------------------------------------------------------------------
1 TOP: 0.00 0.1 -33.1 1 0.00 -1.0 -187.4 1
BOT: 0.00 0.1 -33.1 1 0.00 -1.0 -187.4 1
2 TOP: 0.00 -0.2 -28.7 1 0.00 -1.2 -159.2 1
BOT: 0.00 -0.2 -28.7 1 0.00 -1.2 -159.2 1
3 TOP: 0.00 -0.3 -27.1 1 0.00 -1.6 -155.2 1
BOT: 0.00 -0.3 -27.1 1 0.00 -1.6 -155.2 1



*********** END OF THE STAAD.Pro RUN ***********


enf of txt file********************************************************

I want to get only this in the excel:

1	TOP:	0.00	0.1	-33.1	1	0.00	-1.0	-187.4	1
	BOT:	0.00	0.1	-33.1	1	0.00	-1.0	-187.4	1
2	TOP:	0.00	-0.2	-28.7	1	0.00	-1.2	-159.2	1
	BOT:	0.00	-0.2	-28.7	1	0.00	-1.2	-159.2	1
3	TOP:	0.00	-0.3	-27.1	1	0.00	-1.6	-155.2	1
	BOT:	0.00	-0.3	-27.1	1	0.00	-1.6	-155.2	1

I know there is nothing impossible.. Can somebody help me with this?
Till now I'm using Notepad++ text editor. Holding shift key I select vertically each column in the txt file and copy that column to excel.
So it's time confusing... It would be a good start for me.

Thanks for paying attention

Romkeris,

Give this a try:

Sub tgr()
    
    Dim LineIndex As Long
    Dim strTxtPath As String
    Dim arrLines() As String
    ReDim arrLines(1 To Rows.Count)
    
    strTxtPath = Application.GetOpenFilename("Text Files, *.txt")
    If strTxtPath = "False" Then Exit Sub
    LineIndex = 1
    
    Close #1
    Open strTxtPath For Input As #1
    Do While Not EOF(1)
        Line Input #1, arrLines(LineIndex)
        If InStr(1, arrLines(LineIndex), "top", vbTextCompare) > 0 _
        Or InStr(1, arrLines(LineIndex), "bot", vbTextCompare) > 0 Then
            LineIndex = LineIndex + 1
        End If
    Loop
    Close #1
    
    If LineIndex > 1 Then
        ReDim Preserve arrLines(1 To LineIndex - 1)
        With Range("A2").Resize(LineIndex - 1)
            .Value = Application.Transpose(arrLines)
            .TextToColumns ConsecutiveDelimiter:=True, Tab:=True, Space:=True
        End With
        With Range("A1:J1")
            .Value = Array("Element", "", "Asx (sq.cm/m)", "Mx (kNm/m)", "Nx (kN/m)", "Load N. (X)", "Asy (sq.cm/m)", "My (kNm/m)", "Ny (kN/m)", "Load N. (Y)")
            .Font.Bold = True
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .EntireColumn.AutoFit
        End With
    End If
    
End Sub

Thank You very much, tigeravatar. It works fine!!!

Now I also need other things.
1. Can you implement the code so that it changed periods to commas?

eg.

1

TOP:

0.00

0.1

-33.1

1

0.00

-1.0

-187.4

1

to

1

TOP:

0,00

0,1

-33,1

1

0,00

-1,0

-187,4

1

Romkeris,

Updated code:

Sub tgr()
    
    Dim LineIndex As Long
    Dim strTxtPath As String
    Dim arrLines() As String
    ReDim arrLines(1 To Rows.Count)
    
    strTxtPath = Application.GetOpenFilename("Text Files, *.txt")
    If strTxtPath = "False" Then Exit Sub
    LineIndex = 1
    
    Close #1
    Open strTxtPath For Input As #1
    Do While Not EOF(1)
        Line Input #1, arrLines(LineIndex)
        If InStr(1, arrLines(LineIndex), "top", vbTextCompare) > 0 _
        Or InStr(1, arrLines(LineIndex), "bot", vbTextCompare) > 0 Then
            arrLines(LineIndex) = Replace(arrLines(LineIndex), ".", ",")
            LineIndex = LineIndex + 1
        End If
    Loop
    Close #1
    
    If LineIndex > 1 Then
        Intersect(ActiveSheet.UsedRange, Columns("A:J")).Clear
        ReDim Preserve arrLines(1 To LineIndex - 1)
        With Range("A2").Resize(LineIndex - 1)
            .Value = Application.Transpose(arrLines)
            .TextToColumns ConsecutiveDelimiter:=True, Tab:=True, Space:=True
        End With
        With Range("A1:J1")
            .Value = Array("Element", "", "Asx (sq,cm/m)", "Mx (kNm/m)", "Nx (kN/m)", "Load N, (X)", "Asy (sq,cm/m)", "My (kNm/m)", "Ny (kN/m)", "Load N, (Y)")
            .Font.Bold = True
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .EntireColumn.AutoFit
        End With
    End If
    
End Sub

Sub snb()
   Open "G:\OF\results.txt" For Input As #1
    Open "G:\OF\results 001.csv" For Output As #2
     Print #2, Replace(Replace(Replace(Replace(Join(Filter(Filter(Split(Input(LOF(1), 1), vbCrLf & " "), ":"), "*", False), vbCrLf), Space(5), ","), Space(4), ","), Space(3), ","), Space(2), ",")
    Close #1
   Close #2
   
   Workbooks.Open "G:\OF\results 001.csv"
End Sub

Dependent of your international settings you have to replace the spaces with comma , or semicolon ;