Modify a formatted text file for import! 1

[bud31047]

I have a report that was extracted from an application as a text file. I do not have access to the raw data used to create this text file so I need to make the file "import friendly".

Basically I need to take a report that has group formatting and write the values used for grouping to each subsequent line, until the original value changes. It is hard for me to describe so I hope that the link to the file works. The file itself is small < 700k.

The columns I need to write to the next line (if it is a valid record) are;
Route (Not available for every record)
Document NBR
Picker

If someone knows of a good tutorial, prior post, or wouldn't mind walking me through the process I would be very appreciative for the help.

 

[RiverGuy]

Let's break this down. You have a text file with a series of lines. Some lines you wish to keep, some lines you wish to discard and some lines you wish to modify. The first step would be to get an array of lines from your text file

Dim lines As String() = System.IO.File.ReadAllLines("C:\SomeFile.txt")

You now have an array called lines with one element per line of your text file.

I would suggest creating an ArrayList to store the lines you wish to keep and your modified lines.

Next, you can loop through your lines array, and apply business rules to decide whether or not to load the line into the ArrayList, and how to modify the text if needed. For example, let's say you wish to get rid of all of the page headers and add in missing document numbers.

For i As Integer = 0 To lines.Length - 1
  If lines(i).Contains("Page No") Then 'Page Header

  ElseIf lines(i).Contains("TRANSFER OUT ADJUSTMENTS ") Then 'Page Header Line 2

  ElseIf lines(i).Substring(72, 6).StartsWith("0") AndAlso IsNumeric(lines(i).Substring(72, 6)) Then 'This is a data line (PROD CODE found)
    If lines(i).Substring(7, 18).StartsWith("0000") Then 'This line has a Document Number
      MyArrayList.Add(lines(i))
    Else 'Need to add Document Number
      MyArrayList.Add(lines(i).Substring(0, 6) & GetPreviousDocumentNumber(lines, i) & lines(i).Substring(18, lines(i).Length - 18))
    End If
  End If
Next

Function GetPreviousDocumentNumber(lines As String(), CurrentPosition As Integer) As String
  For i As Integer = CurrentPosition To 0 Step -1
    If lines(i).Substring(7, 18).StartsWith("0000") Then 'You found a document number
       Return lines(i).Substring(7, 18)
    End If
  Next
End Function

Now this code is untested and you'll have to modify and add to it. But you get the idea. Create your business rules and manipulate the data, loading it into a new ArrayList. The function I created above takes your list of lines, and your current position, and searches backwards until a Document Number is found. You would have to create similar logic for your other elements.

Then, when you have your list of pristine data lines, you can write those out to a new text file with a method of your choosing. For example

        For Each s As String In MyArrayList
            System.IO.File.AppendAllText("C:\SomeNewTextFileName.txt", s & Environment.NewLine)
        Next

 

[bud31047]

RiverGuy,
Thanks for the roadmap. Let me work on this for a while and I will get back with you when I get lost.

Thanks again,