Parsing a text file into a fixed width excel split via a macro

[markwilliams2004]

Hi,

I aplologize if this doesn't lay out on the screen properly. I've been meesing with it a bit to format but hope it gives the general idea.

Anyhow, I have a report that runs every month in in text that I want to parse via fixed width. The fixed width would be based on the spacing in the bolded section below and essentially ignore what happens to the top part (in Red) of the report. If the bolding does not come out, then the area is where the "Description" header begins on page 2.

The report is as follows:

RUN 8/01/2008 08:59 Grower Phase Summary by Group Page 1
xxxxxxxxxx
Terminal #: 34 User Id: 3 Name: markw

Summary Report


Print Report By
---------------
1=Fld/Eq Id, Phase

Analysis for : 1 Totals by : 5
1=Fields 2=Equipment 3=Pools 1=Phase,Group 5=Group Only
Selected Id's : N 2=Phase,Ctgy 6=Ctgy Only
Field Type : 3 3=Ph,Ctgy,Grp 7=Ctgy,Grp
1=Own 2=Outside 3=Both 4=Phase Only 8=None
Include : 3 Analysis by : 1
1=Active 2=Inactive 3=Both 1=Acres 4=Qty Yield
Include Source OV : N 2=Units 5=Equiv
Actual/Budget : 1 3=Fld Unts Yld 6=None
1=Actual 2=Budget New Page by : 1
Divide Totals by : 1 1=Major Sort Choice
1=Fields in each Phase 2=Continuous
2=Fields Included on Report

From Through
---- -------
Fiscal Period/Year : 5/2008 5/2008
Calendar Date : (Lowest) (Highest)
Field/Equip Id : (Lowest) (Highest)
Grower Id : (Lowest) (Highest)
CCVV Code : (Lowest) (Highest)
Crop Year : (Lowest) (Highest)
Equip Location : (Lowest) (Highest)
Equip Type : (Lowest) (Highest)
Phase Id : (Lowest) (Highest)
Phase Group : (Lowest) (Highest)
Phase Category : (Lowest) (Highest)
Source : (All)



























RUN 8/01/2008 08:59 Grower Phase Summary by Group Page 2
xxxxxxxxxx


Description Acres Qty/Acre Hrs/Acre Amt/Acre Quantity Hours Amount
----------- ----- -------- -------- -------- -------- ----- ------

06105 Field 506-Planted 5\1-13 Acres: 15.300

11 Equipment 15.30 0.588 0.000 2.07 9.000 0.000 31.62
19 Labor 15.30 0.588 0.000 1.24 9.000 0.000 18.90
24 Labor-Irrigation 15.30 0.588 0.000 0.70 9.000 0.000 10.71

Field Total: Expense 15.30 1.765 0.000 4.00 27.000 0.000 61.23

Again, I hope this is easy enough to work with and appreciate any feedback in advance.

 

[SkipVought]

Please repost your examples using TGML (search on this page for TGML) TT tag.

If you can view it in PREVIEW, and everything lines up properly, you have done your job.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

If you surround your table with [ignore][tt][/ignore] tags like this:
[ignore][tt]
Table
[/tt][/ignore]

Then your table will display the same in the post as it does in the window where you compose.

Click on Process TGML in Step 2 Options for more TGML tips.

As for your problem - the key is going to be finding where the data starts and only applying the text to columns at that point. It looks like a safe way to do that will be with doing a 'find' ([Ctrl] + [F]) for Description. I'd probably just delete all the rows above that one, then do the text to columns.

Turn on your macro recorder (Tools > Macro > Record New Macro) and manually do those steps. Then post back the code that is generated (You can use [ignore]

[/ignore] tags) and we can help clean it up.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[CautionMP]

markwilliams2004,
Try [tt]Test()[/tt] and see if it does what you want. Assumes your report is saved as [tt]C:\Report.txt[/tt] and you want the data placed in Sheet1.

Private Const mstrcParseStart As String = "#####*Acres:*"
Private Const mstrcParseStop As String = "*Field Total:*"
Private Const mstrcParseLine As String = "       |" & _
  "        Description                   |" & _
  "Acres|     Qty/Acre|    Hrs/Acre|      Amt/Acre|      Quantity|        Hours|" & _
  "Amount"

Sub Test()
  Dim rngOut As Range
  Set rngOut = Worksheets("Sheet1").Range("A2")
  ParseFromReport "C:\Report.txt", rngOut
  Set rngOut = Nothing
End Sub

Private Sub ParseFromReport(Filename As String, ByRef Destination As Range)
  Dim blnCapture As Boolean
  Dim intFile As Integer
  Dim strBuffer As String
  intFile = FreeFile
  blnCapture = False
  Open Filename For Input As #intFile
  Do While Not EOF(intFile)
    Line Input #intFile, strBuffer
    If strBuffer Like mstrcParseStart Then
      blnCapture = True
    ElseIf strBuffer Like mstrcParseStop Then
      blnCapture = False
    ElseIf Trim$(strBuffer) = "" Then
      'Blank line, so nothing
    ElseIf blnCapture Then
      BarParse mstrcParseLine, strBuffer, Destination
      Set Destination = Destination.Offset(1)
    End If
  Loop
  Close #intFile
End Sub

Private Sub BarParse(Pattern As String, LineText As String, Destination As Range)
  Dim lngStartPos As Long
  Dim intColumn As Integer
  Dim straColumns() As String
  straColumns = Split(Pattern, "|")
  lngStartPos = 1
  For intColumn = LBound(straColumns) To UBound(straColumns)
    Destination.Offset(, intColumn) = Trim(Mid(LineText, lngStartPos, Len(straColumns(intColumn))))
    lngStartPos = lngStartPos + Len(straColumns(intColumn))
  Next intColumn
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[markwilliams2004]

Dang...that is pretty good. The only thing I really need to capture is this line which is currently excluded:

06105 Field 506-Planted 5\1-13 Acres: 15.300

Awesome though! If you can help point me in the direction to resolve this piece then I am greatly appreciative.

Thank you.

 

[CautionMP]

markwilliams2004,
Do you need to capture it as it's own line in the output worksheet

[tt]6105 Field 506-Planted 5\1-13
11 Equipment 15.3 0.588 ...
19 Labor 15.3 0.588 0 ...[/tt]

or does the data from this line need be included with it's children (normalized structure)?

[tt]6105 Field 506-Planted 5\1-13 15.300 11 Equipment 15.3 0.588 ...
6105 Field 506-Planted 5\1-13 15.300 19 Labor 15.3 0.588 0 ...[/tt]

CMP

 

[markwilliams2004]

The second option would be ideal. Can you do that?

I think I need to up my VBA skills....

 

[CautionMP]

markwilliams2004,
Pretty easy to do:
[ol][li]Need to add a pattern for the 'header' line in the global declarations.[/li]
[li]Create a varibale to hold the header line and store it.[/li]
[li]Add the header line to the output line when parsed to the worksheet.[/li][/ol]

[Gray]Private Const mstrcParseStop As String = "*Field Total:*"[/Gray]
[b]Private Const mstrcParseHeader As String = "06105  |" & _
  "Field 506-Planted 5\1-13                                     |" & _
  "Acres:|         15.300"[/b]
[Gray]Private Const mstrcParseLine As String = "       |        Description                   |" & _
  "Acres|     Qty/Acre|    Hrs/Acre|      Amt/Acre|      Quantity|        Hours|" & _
  "Amount"[/Gray]

[Gray]  Dim blnCapture As Boolean
  Dim intFile As Integer
  Dim strBuffer As String, [/Gray][b]strHeader As String[/b]
  [Gray]intFile = FreeFile
  blnCapture = False
  Open Filename For Input As #intFile
  Do While Not EOF(intFile)
    Line Input #intFile, strBuffer
    If strBuffer Like mstrcParseStart Then
      blnCapture = True[/Gray]
      [b]strHeader = strBuffer[/b]
    [Gray]ElseIf strBuffer Like mstrcParseStop Then
      blnCapture = False
    ElseIf Trim$(strBuffer) = "" Then
      'Blank line, so nothing
    ElseIf blnCapture Then[/Gray]
      BarParse [b]mstrcParseHeader & "|" & [/b]mstrcParseLine, [b]strHeader &[/b] strBuffer, Destination
      [Gray]Set Destination = Destination.Offset(1)
    End If
  Loop
  Close #intFile[/Gray]

Hope this helps,
CMP

 

[SkipVought]

I'm guessing that what you have, after you eliminate the top portion is...
[tt][blue]
RUN 8/01/2008 08:59 Grower Phase Summary by Group Page 2
xxxxxxxxxx

Description Acres Qty/Acre Hrs/Acre Amt/Acre Quantity Hours Amount
----------- ----- -------- -------- -------- -------- ----- ------[/blue]
[/tt]
repeated for each PAGE NUMBER

and a unique...
[tt][blue]
06105 Field 506-Planted 5\1-13 Acres: 15.300[/blue]
[/tt]
for each FIELD; basically one FIELD per PAGE GROUP.

IMHO, you'd be better off trying to find the SOURCE DATA for the program that produced the REPORT.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

I agree Skip. That was my first approach but getting to the source data here is at best difficult.

I have tried connecting to the source tables to utilize SQL to query off of but neither I nor the IT person can get to the proper table so I have been forced to utilize the reports out of this outdated system.

The good news is we have made an ERP selection and should begin work on that transformation over the next several months which will take us off of this 15 year old system that has not been updated and thus leaves this awful void. So I'm hoping these issues are temporary

Thank you all for your help as always.