Macro to extract data from a .txt file and copy to excel table.

[rgassis]

Hi all. I need some help for a macro to extract data from a .txt file and copy to excel table. In the sample `file.txt` attached, I can have several blocks like this:

1 1 DATA PROOF SAMPLE - PROP FROM CORP D-1807 - DATA FROM PROP1 NOVEMBER 30, 2022 DATA 9/17/18 PAGE 31
2 USER-SUPPLIED ASPECT RATIO - 12/23/88
3 0 SUBCASE 1 - FMETHOD = 30 - SPEED = 1 FT/SEC SUBCASE 1
4 0 VALUES SUMMARY
5 CONFIGURATIO N = DATASG2D XY-SYMMETRY = ASYMMETRIC XZ-SYMMETRY = ASYMMETRIC
6 POINT = 1 MACH NUMBER = 0.1000 DENSITY RATIO = 8.8730E-01 METHOD = PK
7
8
9 KRATIO 1./KRATIO SPEED RATIO MODULE COMPLEX EIGENVALUE
10 125.3487 7.9777470E-03 8.3333333E-02 -9.0001358E-03 9.1199364E+00 -2.5786402E-01 5.7302250E+01
11 1 DATA PROOF SAMPLE - PROP FROM CORP D-1807 - DATA FROM PROP1 NOVEMBER 30, 2022 DATA 9/17/18 PAGE 32
12 USER-SUPPLIED ASPECT RATIO - 12/23/88
13 0 SUBCASE 1 - FMETHOD = 30 - SPEED = 1 FT/SEC SUBCASE 1
14 0 VALUES SUMMARY
15 CONFIGURATIO N = DATASG2D XY-SYMMETRY = ASYMMETRIC XZ-SYMMETRY = ASYMMETRIC
16 POINT = 2 MACH NUMBER = 0.1000 DENSITY RATIO = 8.8730E-01 METHOD = PK
17
18
19 KRATIO 1./KRATIO SPEED RATIO MODULE COMPLEX EIGENVALUE
20 126.4484 7.9083624E-03 8.3333333E-02 -5.9999770E-03 9.1999509E+00 -1.7341432E-01 5.7804996E+01
21

I need a macro to run all lines and, every time it finds a "VALUES SUMMARY" (as at line 4), it gets the value of point (after "POINT =") at line 6, and also the values of speed, ratio and module, at line 10 (under "SPEED", "RATIO" and "MODULE" respectively, at line 9). Next, it will find another "VALUES SUMMARY" at line 14, however, at this time, these data are related to point 2 (as written at line 16), and it needs to get the new value of point (after "POINT ="), and also the values of speed, ratio and module, at line 20 (under "SPEED", "RATIO" and "MODULE" respectively, at line 19). Same thing for points 3, 4, 5, ... etc.
This way, it needs to assembly an Excel table like below as a result:

A B C D E F G H
1 POINT 1 POINT 2 POINT 3
2 SPEED RATIO MODULE SPEED RATIO MODULE SPEED RATIO
3 8.3333333E-02 -9.0001358E-03 9.1199364E+00 8.3333333E-02 -5.9999770E-03 9.1999509E+00 ???? ????
4 xxxxx yyyyy zzzzz xxxxx yyyyy zzzzz ???? ????
5 xxxxx yyyyy zzzzz xxxxx yyyyy zzzzz ???? ????

Any help would be appreciated. Thanks!

Ricardo

 

[vletm]

rgassis
You should give more details about Your challenge.

 

[rgassis]

rgassis
You should give more details about Your challenge.

I`ve detailed my problem and included sample files.

 

[Marc L]

Hi, just ask to the text file creator to build a delimited text file (csv, tab, … whatever) rather than this 70's style print file …​

​

 

[rgassis]

Hi Marc. Unfortunately, it is not possible. It is a standard output file from a software. I can not simply ask for some different format.
Thanks.

 

[Marc L]

So this so called 'software' must have an export feature like any application of this century rather than this previous century print …​

​

 

[vletm]

rgassis
Where can find Your named the 1st keyword "VALUES SUMMARY" from Your sample file?
... Do You mean "VALUES SUMMARY" which has two ... spaces between those words.
Is that file.txt really ... a standard output file, from somewhere without any changes ( = raw file )?

Here one sample based Your files ... press [ Do It ] -button.

 

[rgassis]

So this so called 'software' must have an export feature like any application of this century rather than this previous century print …​

​

As I told you, unfortunately, there's not...
That's the only output format I have.

 

[rgassis]

rgassis
Where can find Your named the 1st keyword "VALUES SUMMARY" from Your sample file?
... Do You mean "VALUES SUMMARY" which has two ... spaces between those words.
Is that file.txt really ... a standard output file, from somewhere without any changes ( = raw file )?

Here one sample based Your files ... press [ Do It ] -button.

Hi vletm
Thank you for your support. Yes, it is "VALUES SUMMARY" with two spaces between those words. And, yes, that file.txt is a part of a standard output file, but I just replaced some keywords that could represent some kind of "sensitive" information that I could not share.
I can not open the code you sent, is it protected?

Thanks!

 

[Marc L]

Do you need to keep the scientific notation like 1.10E+02 or normal numbers like 110 ?​

​

As I could share a 'one shot' VBA beginner starter demonstration you should well elaborate your project​

in order there is nothin' to guess as I won't guess anything - as a reminder this is very not a mind readers forum ! -​

so you will have to fit it yourself for what you misexplained or forgot …​

​

 

[Marc L]

I can not open the code you sent, is it protected?

Yes but easily watchable in a second as the protection is so easy to bypass like I already post it​

but vletm erased my previous content ! ​

​

As my demonstration should be lighter with less than 30 codelines and keeping your expected layout …​

​

 

[vletm]

rgassis
You wrote that I need some help for a macro to extract data...
I sent a sample file, that You could check, if output would be something which could use.
Where do You need that code for checking?
You wrote but I just replaced some keywords that could represent some kind of "sensitive" information that I could not share.
... all above ... just ... could just mess everything.

 

[rgassis]

Do you need to keep the scientific notation like 1.10E+02 or normal numbers like 110 ?​

​

As I could share a 'one shot' VBA beginner starter demonstration you should well elaborate your project​

in order there is nothin' to guess as I won't guess anything - as a reminder this is very not a mind readers forum ! -​

so you will have to fit it yourself for what you misexplained or forgot …​

​

Hi Marc
No, I don't need to keep the scientific notation.
As I tried to explain, for this specific problem, I need to read data from that kind of output file. Unfortunately, I don't have much control over the output format and, the same file brings a lot of different data used other purposes by other analysts, but not by me. The part of the information I need to extract is not perfectly printed in a simple table as I would prefer, so, I need to find a way to do it. I don't want to make trickles or expect someone read my mind.
I tried to explain as best as possible. Sorry, if I couldn't!

 

[rgassis]

Thank you!

 

[rgassis]

rgassis
You wrote that I need some help for a macro to extract data...
I sent a sample file, that You could check, if output would be something which could use.
Where do You need that code for checking?
You wrote but I just replaced some keywords that could represent some kind of "sensitive" information that I could not share.
... all above ... just ... could just mess everything.

Understood!
Thank you!

 

[Marc L]

To be sure which number layout you prefer, Point 2 or 3 :​

​

Another point :​

each time you will use the import VBA procedure the text file is always named as file.txt​

and always saved in the same folder than the Excel workbook ?​

​

 

[rgassis]

rgassis
Where can find Your named the 1st keyword "VALUES SUMMARY" from Your sample file?
... Do You mean "VALUES SUMMARY" which has two ... spaces between those words.
Is that file.txt really ... a standard output file, from somewhere without any changes ( = raw file )?

Here one sample based Your files ... press [ Do It ] -button.

Hi vletm

Your file works very well! Thank you very much!
I didn't want to bother you. I asked about the code only by my interest to learn how to do it.
Anyway, it works as I needed.
Thanks for your support!

 

[rgassis]

To be sure which number layout you prefer, Point 2 or 3 :​

​

View attachment 82099

Another point :​

each time you will use the import VBA procedure the text file is always named as file.txt​

and always saved in the same folder than the Excel workbook ?​

​

Hi Marc
Point 2 layout is better.
Yes, the text file is always named as file.txt and always saved in the same folder than the Excel workbook.

Thanks!

 

[Marc L]

According to your attachment :​

• save the Excel workbook as binary format .xlsb via the SaveAs Excel command.
  
  
• The one shot starter Excel basics VBA demonstration to paste only to the Sheet1 worksheet module :

Sub Demo1()
    Dim R&, V, F&, C%
        R = 3
        UsedRange.Offset(R).Clear
        V = ThisWorkbook.Path & "\file.txt":  If Dir(V) = "" Then Beep: Exit Sub
        F = FreeFile
        Open V For Binary As #F
        V = Split(Input(LOF(F), #F), vbCrLf)
        Close #F
    With Application
       .ScreenUpdating = False
    For F = 0 To UBound(V) - 4
        If V(F) Like "       POINT = *" Then
            C = Val(Mid(V(F), 16))
            If C = 1 Then R = R + 1 Else C = 1 + (C - 1) * 3
            F = F + 4
            Cells(R, C).Resize(, 3) = Evaluate("{" & Replace(.Trim(Mid(V(F), 36, 53)), " ", ",") & "}")
        End If
    Next
        With UsedRange.Rows("3:" & R):  .Borders.Weight = 2:  .HorizontalAlignment = xlCenter:  End With
       .ScreenUpdating = True
    End With
End Sub

You can launch it via Alt F8 keys combo or to be allocated to a Form button or without any button via a worksheet event …​

Do you like it ? So thanks to click on bottom right Like !​

​

 

[rgassis]

According to your attachment :​

Thank you very much!​

 

[p45cal]

Another approach with Power Query.
No macros needed, but there is one to find your text file, put it into cell B1 and update the table (button at cell G2). You can, instead manually update the file name in B1 then right click the table below and choose Refresh.

 

[rgassis]

Another approach with Power Query.
No macros needed, but there is one to find your text file, put it into cell B1 and update the table (button at cell G2). You can, instead manually update the file name in B1 then right click the table below and choose Refresh.

Thank you, p45cal!