Read Text File and Copy Job Data

PingJu

New Member
Joined
Sep 21, 2017
Messages
8
Hello, I am trying to read a text (01202_WIP_DATA.txt) file with over 459,000 Rows of Data, The data is broken up into Project Numbers, with corresponding activity codes and costs. how can I have the User select the file to read, and then get data in VBA?
Then, On Worksheet("WIP") in Cells "A1" I will Manually insert a Job number (530004) that I want to pull data from the Txt file. (I will have roughly 40 of these down my spreadsheet) Row 38 will be my next project.
In Column "B4" thru "B30" , I will have Descriptions, and "C4" thru "C30" I will have Costs Codes: I want to pull the data for all of these cost Codes. Any Help would greatly be appreciated!

WIP.png


The TXT File Data:

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 1
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530004 01 - OFFICE PROJECT 1 PM1: 21781 Generic 1 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
199.00|INV UNDER $500 | | | | | | | | | 458| 458| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
EQUIPMENT :Subtotal | | | | | | | | 458| 458| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
245.00|Parts & Supplie| | | | | | | | | 52| 52| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC DUCT :Subtotal | | | | | | | | 52| 52| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
441.00|DDC Controls (C| | | | | | | | | 13,139| 13,139| 20,942|
464.00|Programming (Co| | | 40| | | | | | | | 2,480|
465.00|Commissioning | | | 172| | | | | | | | 8,600|
466.00|Training (Contr| | | 24| | | | | | | | 1,200|
468.00|Graphics | | | 40| | | | | | | | 2,000|
469.00|Supervision (Co| | 5.0 | 48| | | 64.43| | 322| 63| 385| 2,400|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SYS OPN/BAL :Subtotal | 5.0 | 324| | | 64.43| | 322| 13,202| 13,524| 37,622|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 324| | | 51.48| | 16,680| 20,942| 37,622| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 324| | | 51.48| | 16,680| 20,942| 37,622| 37,622|
| % Compl.| | | 1.5 % | | | | | 1.9 % | 63.0 % | 35.9 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
503.00|CONTROLS DESIGN| | 124.0 | 64| | | 56.59| | 7,018| 1,550| 8,568| 3,328|
520.00|PANEL ASSEMBLY | | 32.0 | 56| | | 53.77| | 1,721| 400| 2,121| 2,688|
599.00|Total Misc. Lab| | | | | | | | | | | 1,600|
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 2
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530004 01 - OFFICE PROJECT 1 PM1: 21781 Generic 1 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ENGINEERING :Subtotal | 156.0 | 120| | | 56.02| | 8,738| 1,950| 10,688| 7,616|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 120| | | 50.13| | 6,016| 1,600| 7,616| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 120| | | 50.13| | 6,016| 1,600| 7,616| 7,616|
| % Compl.| | | 130.0 % | | | | | 145.3 % | 121.9 % | 140.3 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
667.00|Freight & Trans| | | | | | | | | 112| 112| 6,785|
683.00|Miscellaneous | | | | | | | | | 557| 557| |
694.00|Sales / Use Tax| | | | | | | | | 1,297| 1,297| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
RENTAL/OTHER :Subtotal | | | | | | | | 1,966| 1,966| 6,785|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | 6,785| 6,785| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | 6,785| 6,785| 6,785|
| % Compl.| | | | | | | | | 29.0 % | 29.0 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
Total, Phase 1 | | | | | | | | | | | |
| Actual | 161.0 | 444| | | 56.28| | 9,061| 17,626| 26,687| 52,023|
| | | | | | | | | | | | |
| | | |<-------------- Budget --------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 444| | | 51.12| | 22,696| 29,327| 52,023| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 444| | | | | 22,696| 29,327| 52,023| |
| % Compl.| | | 36.3 % | | | | | 39.9 % | 60.1 % | 51.3 % | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 3
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
199.00|INV UNDER $500 | | | | | | | | | 2,595| 2,595| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
EQUIPMENT :Subtotal | | | | | | | | 2,595| 2,595| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
245.00|Parts & Supplie| | | | | | | | | -764| -764| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC DUCT :Subtotal | | | | | | | | -764| -764| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
441.00|DDC Controls (C| | | | | | | | | 99,271| 99,271| 109,854|
460.00|Install Control| | 2,948.0 | 995| | | 68.86| | 203,004| 36,916| 239,920| 79,600|
463.00|Controls TI (Co| | 4.0 | | | | 39.34| | 157| 50| 207| |
464.00|Programming (Co| | 87.5 | 88| | | 73.38| | 6,421| 1,094| 7,514| 7,040|
465.00|Commissioning | | 445.0 | 800| | | 50.30| | 22,384| 5,563| 27,947| 64,000|
466.00|Training (Contr| | | 8| | | | | | | | 640|
468.00|Graphics | 25.0| 168.0 | 80| | | 71.28| | 11,975| 2,100| 14,075| 6,400|
469.00|Supervision (Co| | 66.0 | 80| | | 81.42| | 5,373| 825| 6,198| 6,400|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SYS OPN/BAL :Subtotal 25.0| 3,718.5 | 2,051| | | 67.05| | 249,315| 145,818| 395,133| 273,934|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 1,920| | | 80.00| | 153,600| 95,000| 248,600| |
|APPROVED C/Os | | | 131| | | 80.00| | 10,480| 14,854| 25,334| |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 2,051| | | 80.00| | 164,080| 109,854| 273,934| 273,934|
| % Compl.| | | 181.3 % | | | | | 151.9 % | 132.7 % | 144.2 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
501.00|Design Engineer| | 4.0 | | | | 58.89| | 236| 50| 286| |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 4
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
503.00|CONTROLS DESIGN| | 16.0 | 80| | | 56.59| | 906| 200| 1,106| 6,400|
530.00|Project Enginee| 4.0| 43.0 | | | | 63.13| | 2,715| 538| 3,252| |
551.00|Project Manager| | | 40| | | | | | | | 4,000|
599.00|Total Misc. Lab| | | | | | | | | | | 3,000|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ENGINEERING :Subtotal 4.0| 63.0 | 120| | | 61.20| | 3,856| 788| 4,643| 13,400|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 120| | | 86.67| | 10,400| 3,000| 13,400| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 120| | | 86.67| | 10,400| 3,000| 13,400| 13,400|
| % Compl.| | | 52.5 % | | | | | 37.1 % | 26.3 % | 34.7 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
618.00|Consulting Engi| | | | | | | | | 30| 30| |
628.00|MISC SUBCONTRAC| | | | | | | | | 2,425| 2,425| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SUBCONTRACTS :Subtotal | | | | | | | | 2,455| 2,455| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
666.00|Rentals - Misce| | | | | | | | | 1,254| 1,254| |
667.00|Rentals, Freigh| | | | | | | | | 1,705| 1,705| |
683.00|Miscellaneous | | | | | | | | | 41| 41| |
694.00|Sales / Use Tax| | | | | | | | | 10,348| 10,348| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
RENTAL/OTHER :Subtotal | | | | | | | | 13,347| 13,347| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
709.00|Miscellaneous | | 10.0 | | | | 41.32| | 413| 125| 538| |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 5
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC OTHER :Subtotal | 10.0 | | | | 41.32| | 413| 125| 538| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
TOTAL |NCTC0220 | | | | | | | | | | | 142,801|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ADDTNL EST COST :Subtotal | | | | | | | | | | 142,801|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | 16,000| 126,801| 142,801| |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | 16,000| 126,801| 142,801| 142,801|
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
Total, Phase 1 | | | | | | | | | | | |
| Actual 29.0| 3,791.5 | 2,171| | | 66.88| | 253,584| 164,363| 417,947| 430,135|
| | | | | | | | | | | | |
| | | |<-------------- Budget --------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 2,040| | | 80.39| | 164,000| 98,000| 262,000| |
|APPROVED C/Os | | | 131| | | | | 10,480| 14,854| 25,334| |
|REVISIONS/PENDING | | | | | | | 16,000| 126,801| 142,801| |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 2,171| | | | | 190,480| 239,655| 430,135| |
| % Compl.| | | 174.6 % | | | | | 133.1 % | 68.6 % | 97.2 % | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 6
COST DIST B-4
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Try the following macro on a small txt file for you to review the results. I don't know how long it will take excel to read and process a file of more than 4500,000 lines.
The result will be on the "WIP" sheet according to the format you put in the image.
Perhaps the name of the "Project Name" and "Project Manager" data are not complete, since I do not know the pattern to identify where that data begins and ends.

VBA Code:
Sub read_txt_files()
  Dim ff As Long, i As Long, j As Long, lngFileLen As Long
  Dim arrLines As Variant, a As Variant, b As Variant, c As Variant
  Dim pickFile As String, strWholeFile As String, jobPhase As String, job1 As String
  Dim dic As Object
  '
  With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select txt file"
    .Filters.Clear
    .Filters.Add "txt files", "*.txt"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If Not .Show Then Exit Sub
    pickFile = .SelectedItems.Item(1)
  End With
  '
  ff = FreeFile
  lngFileLen = FileLen(pickFile)
  strWholeFile = Space(lngFileLen)
  Open pickFile For Binary Access Read As #ff
    Get #ff, , strWholeFile
  Close #ff
  arrLines = Split(strWholeFile, vbCrLf)
  '
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("WIP").Range("A1:O" & Sheets("WIP").Range("A" & Rows.Count).End(3).Row + 40).Value2
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" And IsNumeric(a(i, 1)) Then
      dic("" & a(i, 1)) = i + 3
    End If
  Next
  '
  ReDim b(1 To UBound(arrLines), 1 To 4)
  For i = 0 To UBound(arrLines)
    If Left(arrLines(i), 10) = "Job/Phase:" Then
      j = j + 1
      jobPhase = arrLines(i)
      job1 = Split(jobPhase, " ")(1)
      b(j, 1) = job1
      b(j, 2) = Mid(jobPhase, 11 + Len(job1) + 2, InStr(13, jobPhase, ":") - (11 + Len(job1) + 2))
    ElseIf IsNumeric(Left(arrLines(i), 1)) Then
      j = j + 1
      b(j, 1) = job1
      b(j, 4) = arrLines(i)
    End If
  Next
  '
  For i = 1 To UBound(b)
    If b(i, 2) <> "" Then
      j = dic(b(i, 1)) - 3
      If a(j, 2) = "" Then
        a(j, 2) = Left(b(i, 2), InStrRev(b(i, 2), " ") - 1)
        a(j, 3) = Mid(b(i, 2), InStrRev(b(i, 2), " ") + 1)
      End If
    End If
    If b(i, 4) <> "" Then
      j = dic(b(i, 1))
      c = Split(b(i, 4), "|")
      
      a(j, 2) = c(1)
      a(j, 3) = c(0)
      a(j, 5) = c(2)
      a(j, 6) = c(3)
      a(j, 7) = c(4)
      a(j, 8) = c(5)
      a(j, 9) = c(6)
      a(j, 10) = c(7)
      a(j, 11) = c(8)
      a(j, 12) = c(9)
      a(j, 13) = c(10)
      a(j, 14) = c(11)
      a(j, 15) = c(12)
      
      dic(b(i, 1)) = dic(b(i, 1)) + 1
    End If
    
  Next
  Sheets("WIP").Range("A1").Resize(UBound(a), 15).Value = a
End Sub
 

PingJu

New Member
Joined
Sep 21, 2017
Messages
8
Dante, Thank you so much for the Help! Any thought on the below issue?

I am getting a Run-time error '9': Subscript out of range

a(i,1) = <Subscript Out Of Range>

Set dic = CreateObject("Scripting.Dictionary")
a = Sheets("WIP").Range("A2:O" & Sheets("WIP").Range("A" & Rows.Count).End(xlUp).Row + 40).Value2
For i = 1 To UBound(a, 1)
If a(i, 1) <> "" And IsNumeric(a(i, 1)) Then
dic("" & a(i, 1)) = i + 3
End If
Next

debugging stops on the following line:

ReDim b(1 To UBound(arrLines), 1 To 4)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
You modified something in the macro, because I don't have any line with this statement: a(i, 1) =
Again, on which line do you have the error?
And what does the error message say?
You can put an image of what you have on your "WIP" sheet and an image of the data in your txt file.
 

PingJu

New Member
Joined
Sep 21, 2017
Messages
8

ADVERTISEMENT

Hi Dante, sorry, after reading my question, it is a bit confusing. the error Message I get is Run-time error '9': Subscript out of range.

When I click on the Debug, it stops on the following line:

ReDim b(1 To UBound(arrLines), 1 To 4)

debug.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
But you did not put an image of your sheet nor did you put an image of the data from the txt file you are testing.
You can do the test again, use a txt file that has 3 projects. Those project numbers should be on your sheet.

I simplified the code a bit.
Make the small test with the following code:

VBA Code:
Sub read_txt_files()
  Dim ff As Long, i As Long, j As Long, lngFileLen As Long
  Dim arrLines As Variant, a As Variant, c As Variant
  Dim pickFile As String, strWholeFile As String, jobPhase As String, job1 As String, job2 As String
  Dim dic As Object
  '
  With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select txt file"
    .Filters.Clear
    .Filters.Add "txt files", "*.txt"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If Not .Show Then Exit Sub
    pickFile = .SelectedItems.Item(1)
  End With
  '
  ff = FreeFile
  lngFileLen = FileLen(pickFile)
  strWholeFile = Space(lngFileLen)
  Open pickFile For Binary Access Read As #ff
    Get #ff, , strWholeFile
  Close #ff
  arrLines = Split(strWholeFile, vbCrLf)
  '
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("WIP").Range("A1:O" & Sheets("WIP").Range("A" & Rows.Count).End(3).Row + 40).Value2
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" And IsNumeric(a(i, 1)) Then
      dic("" & a(i, 1)) = i + 3
    End If
  Next
  '
  For i = 0 To UBound(arrLines)
    If Left(arrLines(i), 10) = "Job/Phase:" Then
      jobPhase = arrLines(i)
      job1 = Split(jobPhase, " ")(1)
      job2 = Mid(jobPhase, 11 + Len(job1) + 2, InStr(13, jobPhase, ":") - (11 + Len(job1) + 2))
      j = dic(job1) - 3
      If a(j, 2) = "" Then
        a(j, 2) = Left(job2, InStrRev(job2, " ") - 1)
        a(j, 3) = Mid(job2, InStrRev(job2, " ") + 1)
      End If
    ElseIf IsNumeric(Left(arrLines(i), 1)) Then
      j = dic(job1)
      c = Split(arrLines(i), "|")
      a(j, 2) = c(1)
      a(j, 3) = c(0)
      a(j, 5) = c(2)
      a(j, 6) = c(3)
      a(j, 7) = c(4)
      a(j, 8) = c(5)
      a(j, 9) = c(6)
      a(j, 10) = c(7)
      a(j, 11) = c(8)
      a(j, 12) = c(9)
      a(j, 13) = c(10)
      a(j, 14) = c(11)
      a(j, 15) = c(12)
      dic(job1) = dic(job1) + 1
    End If
  Next
  '
  Sheets("WIP").Range("A1").Resize(UBound(a), 15).Value = a
End Sub
 

PingJu

New Member
Joined
Sep 21, 2017
Messages
8

ADVERTISEMENT

Donte, here are the images:
WIPSHeet.png


txtfile.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Time.
In your first example, the amounts were in the first position.
But now in your image the amounts have spaces on the left side
So the macro will not work.
You could upload some txt file to the cloud to really see how the data is.
An example with 5 projects is enough, don't upload a file of 400,000 records.

1586897105321.png


You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 

PingJu

New Member
Joined
Sep 21, 2017
Messages
8
Dante, all your help is greatly appreciated. my apologies for the Text file format that I dropped into the Thread.

Here is the Link:

This is Sample Data of roughly 5 projects.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,086
Messages
5,622,615
Members
415,914
Latest member
Uriel Castillo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top