loop through text files using VBA and extract information

greenkevin86 · Feb 4, 2020

I work for a sheet metal manufacturer and would like to use VBA to loop through a directory of text files (that are created as setup sheets when machine programs are generated) and extract meaningful information to Excel for further analysis. All of the text files are generally structured the same and below is a sample. I would like to:
1) Extract the Part Number, Prog Number, Blank Size, Material, Parts/Sheet, and Total Time (under Time Calculations) and place in a table on one sheet.
2) Extract all the tooling information into a table on a separate sheet. There would be 3 columns: a) prog number (which would repeat for each row of data within the same text file but would link each tool to the program), b) the tool # (e.g. T311), and c) the description (e.g. Sqr 0.531)

smozgur · Feb 4, 2020

@greenkevin86 - welcome to the MrExcel board!

If possible, please copy and paste the actual file content as text in code tags.
Simply copy and paste the file content, select it in the editor and click on the <rich/> button on the editor toolbar to wrap with CODE tags. I am sure providing the real text content would be much easier than the screenshot to answer your question as it requires parsing.

juddaaaa · Feb 4, 2020

Try this out. It's set up to ask you for a file

VBA Code:

Sub ReadFile()

    Dim myFile As String, text As String, textline As String
    Dim Setup As Object: Set Setup = CreateObject("Scripting.Dictionary")
    Dim re As Object: Set re = CreateObject("VBScript.RegExp")
    Dim match As Object
    Dim itm As Variant
    
    myFile = Application.GetOpenFilename()
    With re
        .Pattern = "([A-Za-z]\d+)\s\/([A-Za-z]+\s[0-9.]+)"
    End With
    
    Open myFile For Input As #1
    
    Do Until EOF(1)
        Line Input #1, textline
        
        If InStr(1, textline, ":") Then
            Setup.Add _
                key:=LCase(Trim(Split(textline, ":")(0))), _
                Item:=Trim(Split(textline, ":")(1))
        End If
        
        Set match = re.Execute(textline)
        
        If match.Count > 0 Then
            With match(0)
                Setup.Add _
                    key:="Tool " & .SubMatches.Item(0), _
                    Item:=.SubMatches.Item(1)
            End With
        End If
    Loop
    
    With Sheet1.Range("A" & GetNextRow(Sheet1, "A")).Resize(1, 6)
        .Value = Array(Setup("part number"), _
                       Setup("prog number"), _
                       Setup("blank size"), _
                       Setup("material"), _
                       Setup("parts/sheet"), _
                       Setup("total time"))
    End With
    
    For Each itm In Setup.Keys
        If Left(itm, 4) = "Tool" Then
            With Sheet2.Range("A" & GetNextRow(Sheet2, "A")).Resize(1, 3)
                .Value = Array(Setup("prog number"), _
                               Split(itm, " ")(1), _
                               Setup(itm))
            End With
        End If
    Next itm
    
    Close #1

End Sub

Function GetNextRow(sht As Worksheet, col As String) As Long

    GetNextRow = sht.Cells(sht.Rows.Count, col).End(xlUp).Offset(1).Row

End Function

Book1

A

B

C

D

E

F

G

1

Part Number

Prog Number

Blank Size

Material

Parts/Sheet

Total Time

2

TP3120

TP3120_0_1

120.000 X 48.000

0.060 Mild Steel

4

1.000 minutes 42.000 seconds

3

4

5

6

7

Sheet1

Book1

A

B

C

D

E

1

Prog Number

Tool

Description

2

TP3120_0_1

T311

Sqr 0.531

3

TP3120_0_1

T144

Rct 2.000

4

5

6

Sheet2

greenkevin86 · Feb 4, 2020

Thank you both for your replies; I will try the sample code at work tomorrow and let you know. Here is a sample of actual file content (not the exact file as the image I posted):

Rich (BB code):

========================= Setup Sheet ===========================
                                  DATE - 02-03-20 TIME - 11:13

COMPANY NAME : 
PART NUMBER  : EN02
PROG NUMBER  : EN02_0_2
REVISION     : 01 03/29/19

======================== Punch Info =============================
Programmer  : RTW

               ** Units are **SSPB_UNIT_DESC**
Blank Size  : 48.000 X 48.000
Material    : 0.048 Mild Steel
Parts/Sheet : 14

Clamps      : **SSPB_CLP1_STR** - **SSPB_CLP2_STR** - **SSPB_CLP3_STR**
Machine     : AV358K

========================== Notes ================================
**SSPB_SETUP_HISTORY**

==================== Time Calculations ==========================
SHEET LOAD   : 5.000 seconds
UNLOAD SHEET : 5.000 seconds
REPOSITION   : 0 repos  0.000 seconds
TRAVEL TIME  : 3343.862 **SSPB_UNIT_DESC**  200.632 seconds
STRIKE TIME  : 1183 hits  469.200 seconds
TRAP DOOR    : 0 doors 0.000 seconds
SLUG REMOVAL : 0 slugs 0.000 seconds
AUTO INDEX   : 79 changes 15.800 seconds
TOOL CHANGE  : 23 changes 46.000 seconds

TOTAL TIME   : 12.000 minutes 21.000 seconds
         
(===============   TOOLING SHEET INFORMATION   ================)
(---------------------------------------------------------------)
(STAT/TOOL DESCRIPTION         X     Y    ANGLE INDEXED  #HITS  )
(---------------------------------------------------------------)
(T354 /Rad 0.250              0.250  0.250  0.00     N     68   )
(T314 /Rnd 0.140              0.140  0.140  0.00     N     20   )
(T328 /Rnd 0.250              0.250  0.250  0.00     N     8    )
(T204 /Rnd 0.203              0.203  0.203  0.00     N     72   )
(T305 /Rnd 0.219              0.219  0.219  0.00     N     36   )
(T319 /Rnd 0.391              0.391  0.391  0.00     N     8    )
(T242 /Rnd 0.343              0.343  0.343  0.00     N     16   )
(T115 /Rct 70mm x 10mm        2.756  0.394  0.00     Y     178  )
(T357 /Rnd 0.437              0.437  0.437  0.00     N     8    )
(T218 /Rnd 0.500              0.500  0.500  0.00     N     8    )
(T338 /Rnd 0.687              0.687  0.687  0.00     N     4    )
(T336 /Rnd 1.000              1.000  1.000  0.00     N     2    )
(T139 /Obr 1.000 x 0.094      1.000  0.094  0.00     N     6    )
(T117 /HEX 0.354              0.354  6.000  0.00     N     8    )
(T146 /Sqr 0.203              0.203  0.203  0.00     N     4    )
(T141 /Sqr 0.265              0.265  0.265  0.00     N     8    )
(T348 /Sqr 0.343              0.343  0.343  0.00     N     8    )
(T258 /WP EXT 0.888           0.888  0.888  0.00     N     4    )
(T229 /WP ROLLOVER 1.055      1.055  1.055  0.00     N     4    )
(T201 /Rct 1.000 x 0.250      1.000  0.250  0.00     Y     453  )
(T144 /Rct 2.000 x 0.250      2.000  0.250  0.00     Y     246  )
(T350 /KO 0.875               0.865  0.875  270.00   N     14   )
(+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++)

juddaaaa · Feb 4, 2020

Tested my code with your sample data and I'm pleased to say all is working fine.

Here's the result:

Book1

A

B

C

D

E

F

G

H

I

J

K

1

Part Number

Prog Number

Blank Size

Material

Parts/Sheet

Total Time

2

EN02

EN02_0_2

48.000 X 48.000

0.048 Mild Steel

14

12.000 minutes 21.000 seconds

3

4

5

Sheet1

Book1

A

B

C

D

E

1

Prog Number

Tool

Description

2

EN02_0_2

T354

Rad 0.250

3

EN02_0_2

T314

Rnd 0.140

4

EN02_0_2

T328

Rnd 0.250

5

EN02_0_2

T204

Rnd 0.203

6

EN02_0_2

T305

Rnd 0.219

7

EN02_0_2

T319

Rnd 0.391

8

EN02_0_2

T242

Rnd 0.343

9

EN02_0_2

T115

Rct 70

10

EN02_0_2

T357

Rnd 0.437

11

EN02_0_2

T218

Rnd 0.500

12

EN02_0_2

T338

Rnd 0.687

13

EN02_0_2

T336

Rnd 1.000

14

EN02_0_2

T139

Obr 1.000

15

EN02_0_2

T117

HEX 0.354

16

EN02_0_2

T146

Sqr 0.203

17

EN02_0_2

T141

Sqr 0.265

18

EN02_0_2

T348

Sqr 0.343

19

EN02_0_2

T201

Rct 1.000

20

EN02_0_2

T144

Rct 2.000

21

EN02_0_2

T350

KO 0.875

22

23

24

25

26

27

28

Sheet2

greenkevin86 · Feb 4, 2020

That is great! Next step will be to automatically loop through each file in a directory to parse the data at the end of each table. For example if all text files were stored on a shared drive with the path F:\Quality\Files To Parse, do you know how to automate looping through each file?

juddaaaa · Feb 4, 2020

On second look, I think the regular expression pattern may need a little tweak

greenkevin86 · Feb 5, 2020

I tested at work and here are the results:

1) it worked perfectly on the sample file I provided for EN02_0_2
2) I tested it on this txt file and it extracted the information on the first sheet but did not extract the tooling information. Probably because the tooling sheet information structure/tags are slightly different than the original example I provided. If the code has to be tweaked for each machine (as defined by each test in this reply), that is fine since there are only 5 machines and I could manually parse the outputs together since that is manageable:

Rich (BB code):

========================= Setup Sheet ===========================
                DATE - 05-16-17 TIME - 16:03

Company Name    :
Part Number     : 47-8762-304
Program Number  : 47-8762_T_4
Revision        : 07 5/16/17
======================== Punch Info =============================
Programmer  : RTW
** Units are  **
Material Type      : 304 Stainless Steel
Material Thickness : 0.120  -  11 Gage
Blank Size  : 120.000 X 48.000
Parts/Sheet : 2
Clamp 1 : 4
Clamp 2 : 10
Clamp 3 : 16
Clamp 4 : 22

Clamp on LONG SIDE side
!! PROGRAM CONTAINS 0 REPOSITION(S) !!


Machine : Trumpf Trumatic 5000 Rotation

========================== Notes ================================


==================== Time Calculations ==========================
SHEET LOAD   : 20.000 seconds
UNLOAD SHEET : 20.000 seconds
REPOSITION   : 0 repos  0.000 seconds
STRIKE TIME  : 336 hits  67.900 seconds
AUTO INDEX   : 9 changes 0.900 seconds
TOOL CHANGE  : 10 changes 50.000 seconds
SLUG REMOVAL : 0 slugs 0.000 seconds
TRAP DOOR    : 0 doors 0.000 seconds
TRAVEL TIME  : 2670.110   37.625 seconds

TOTAL TIME   : 3.000 minutes 16.000 seconds
        
(*MSG===============   TOOLING SHEET INFORMATION   ================)
(*MSG---------------------------------------------------------------)
(*MSGSTAT/TOOL DESCRIPTION         X     Y    ANGLE INDEXED  #HITS  )
(*MSG---------------------------------------------------------------)
(*MSGT20  /RND 0.219              0.219  0.219  0.00     Y     8    )
(*MSGT0301/RND 0.313              0.313  0.313  0.00     Y     16   )
(*MSGT05  /RND 0.562              0.562  0.562  0.00     Y     12   )
(*MSGT07  /RND 0.625              0.625  0.625  0.00     Y     16   )
(*MSGT06  /RND 0.787 [20mm]       0.787  0.787  0.00     Y     16   )
(*MSGT11  /SQR 0.343              0.343  0.343  0.00     Y     8    )
(*MSGT18  /SQR 0.437              0.437  0.437  0.00     Y     40   )
(*MSGT08  /RCT 0.250 X 1.750      1.750  0.250  0.00     Y     32   )
(*MSGT23  /RCT 0.250 X 1.000      1.000  0.250  0.00     Y     80   )
(*MSGT24  /RCT 0.250 X 2.000      2.000  0.250  0.00     Y     61   )
(*MSG+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++)

3. I tested it on this txt file and I get the error "Tun-time error '457'. This key is already associated with an element of this collection.

Rich (BB code):

========================= Setup Sheet ===========================
                                  DATE - 12-26-19 TIME - 11:08

Company Name    :
Part Number     : EN0114-04-T5
Program Number  : EN0114-04-T5_B_1
Revision        : '01 12/26/19
NC File Name    : F:/MANUFACTURING/CNC_PROGRAMS/HANDSHAKE/STRIKER/AMADA_PEGA357/EN0114-04-T5_B_1.TXT

======================== Punch Info =============================
Programmer  : LAM

               ** Units are mm **

Material Type      : Mild Steel
Material Thickness : 0.06  -  16 Gage

Blank Size  : 3048.00 X 1220.00

Parts/Sheet : 2

Clamp 1 : 100.0mm
Clamp 2 : 550.0mm

Machine : AP357

========================== Notes ================================


==================== Time Calculations ==========================
SHEET LOAD   : 5.00 seconds
UNLOAD SHEET : 5.00 seconds
REPOSITION   : 0 repos  0.00 seconds
STRIKE TIME  : 1172 hits  468.80 seconds
AUTO INDEX   : 2 changes 0.40 seconds
TOOL CHANGE  : 12 changes 24.00 seconds
SLUG REMOVAL : 0 slugs 0.00 seconds
TRAP DOOR    : 0 doors 0.00 seconds
TRAVEL TIME  : 46962.78 mm  110.94 seconds

TOTAL TIME   : 614.14 seconds
TOTAL TIME   : 10.00 minutes 14.00 seconds
        
(===============   TOOLING SHEET INFORMATION   ================)
(---------------------------------------------------------------)
(STAT/TOOL DESCRIPTION         X     Y    ANGLE INDEXED  #HITS  )
(---------------------------------------------------------------)
(T345 /RND 3.6mm [0.141]      3.600  3.600  0.00     N     12   )
(T313 /RND 5.0mm [0.197]      5.000  5.000  0.00     N     4    )
(T349 /RND 9.0mm [0.354]      9.000  9.000  0.00     N     10   )
(T306 /RND 18.0mm [0.709]     18.000 18.000 0.00     N     10   )
(T331 /SQR 8.3mm [0.327]      8.300  8.300  0.00     N     40   )
(T256 /RCT 5.0mm x 15.0mm     15.000 5.000  0.00     Y     2    )
(T220 /RCT 5.0mm x 30.0mm     30.000 5.000  0.00     Y     24   )
(T228 /RCT 5.0mm x 50.0mm     50.000 5.000  0.00     N     982  )
(T210 /RCT 5.0mm x 50.0mm     50.000 5.000  270.00   N     65   )
(T219 /RCT 5.0mm x 80.0mm     80.000 5.000  0.00     N     19   )
(T351 /RAD 10mm [0.394] - 4 CO4.648  0.250  0.00     N     4    )
(+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++)

4. I tested it on this txt file and I get the error "Tun-time error '457'. This key is already associated with an element of this collection.

Rich (BB code):

========================= Setup Sheet ===========================
                                  DATE - 03-20-19 TIME - 15:13

Company Name    :
Part Number     : JPH60-128-001
Program Number  : JPHR60-128-0001_A_16
Revision        : 00 3/11/19
NC File Name    : F:/MANUFACTURING/CNC_PROGRAMS/HANDSHAKE/STRIKER/AMADA_PEGA244/JPHR60-128-0001_A_2.TXT

======================== Punch Info =============================
Programmer  : LAM

               ** Units are mm **

Material Type      : 304 Stainless Steel
Material Thickness : 0.075  -  14 Gage

Blank Size  : 2000.000 X 1016.000

Parts/Sheet : 16

Clamp 1 : 400.0mm
Clamp 2 : 950.0mm

Machine : AP244

========================== Notes ================================


==================== Time Calculations ==========================
SHEET LOAD   : 5.000 seconds
UNLOAD SHEET : 5.000 seconds
REPOSITION   : 2 repos  8.000 seconds
STRIKE TIME  : 418 hits  167.600 seconds
AUTO INDEX   : 13 changes 2.600 seconds
TOOL CHANGE  : 17 changes 34.000 seconds
SLUG REMOVAL : 0 slugs 0.000 seconds
TRAP DOOR    : 0 doors 0.000 seconds
TRAVEL TIME  : 57905.223 mm  136.530 seconds

TOTAL TIME   : 358.730 seconds
TOTAL TIME   : 5.000 minutes 58.000 seconds
        
(===============   TOOLING SHEET INFORMATION   ================)
(---------------------------------------------------------------)
(STAT/TOOL DESCRIPTION         X     Y    ANGLE INDEXED  #HITS  )
(---------------------------------------------------------------)
(T308 /RND 14mm [0.551]       14.000 14.000 0.00     N     32   )
(T218 /RND 9.0mm [0.354]      9.000  9.000  0.00     N     24   )
(T303 /RND 16mm [0.630]       16.000 16.000 0.00     N     4    )
(T314 /RAD 10mm [0.394] - 4 CO4.648  0.250  0.00     N     30   )
(T302 /RND 20.0mm [0.787]     20.000 20.000 0.00     N     10   )
(T205 /RCT 5.0mm x 30.0mm     30.000 5.000  0.00     Y     24   )
(T217 /RCT 5.0mm x 15.0mm     15.000 5.000  0.00     Y     6    )
(T204 /RCT 5.0mm x 50.0mm     50.000 5.000  90.00    N     44   )
(T201 /RCT 5.0mm x 80.0mm     80.000 5.000  0.00     N     244  )
(+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++)

5. I tested on the following txt file and it extracted the information on sheet 1 correctly. Tooling information is not applicable for this machine as it is a laser so the code worked properly.

Rich (BB code):

========================= Setup Sheet ===========================
                                  DATE - 01-31-20 TIME - 09:49

COMPANY NAME :
PART NUMBER  :
PROG NUMBER  : 112349-02-10G-1-1
REVISION     :  01/31/2020

======================== Profile Info =============================
Programmer  : RTW

               ** Units are mm
Blank Size  : 3048.0000 X 1224.0000
Material    : 3.4160 Galvanneal
Parts/Sheet :

Machine     : SALVAGNINI L3 LASER

========================== Notes ================================



==================== Time Calculations ==========================
SHEET LOAD   : 5.0000 seconds
UNLOAD SHEET : 5.0000 seconds
SLUG REMOVAL : 0 slugs 0.0000 seconds
PIERCING     : 475 pierces  950.0000 seconds
TRAVEL TIME  : 48276.0140 mm  120.6900 seconds
BURNING      : 61511.5685 mm  153.7789 seconds

TOTAL TIME   : 20.0000 minutes 34.0000 seconds

loop through text files using VBA and extract information

greenkevin86

New Member

Excel Facts

smozgur

BatCoder

juddaaaa

Board Regular

greenkevin86

New Member

juddaaaa

Board Regular

greenkevin86

New Member

juddaaaa

Board Regular

greenkevin86

New Member

Similar threads

Forum statistics

Share this page

loop through text files using VBA and extract information

New Member

Excel Facts

BatCoder

Board Regular

New Member

Board Regular

New Member

Board Regular

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock