How to reference split lines in my Code

Posted on 2006-03-24
Last Modified: 2010-04-23
Ok, I have this code which takes an incoming file.  The incoming file can have 3 different types of rows:

Example Header Row: 20060306                        HD000000000{000000000{00018AR102197                                
Example Maintenance Row: 2006030684324264293997849652    MTMASCOM0800                                    IAR10ncohen    CARD
Example Payment Row: 2006030252254800137999418589    1C000000098C  Agency commission   000000090D080{ B001S013      CARD

The code brings in the file and splits out the file at the beginning of each new header row if the row has 8 chars and a space. I've attempted to trap each row and to create an if statement to deterine the type of row so that I can insert each row into a table using my  InsertLineTable function:

                    While Not reader.Peek < 0
                        Dim Line As String = reader.ReadLine

                        If IsNumeric(Line.Substring(0, 1)) Then
                            Dim Parts() As String = Line.Split(" "c) ' split row into parts

                            If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file
                                ' First call function to insert the columns into table

                                InsertLineTable(Parts.ToString, "Header") 'Insert Header Row into Log Table

                                counter += 1
                                If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
                                CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
                                CurrentWriter = New IO.StreamWriter(CurrentFS)

                            ElseIf Parts(2).Substring(1, 2) = "MT" Then
                                InsertLineTable(Parts.ToString, "mnt") 'Insert Maintenance Row into Log Table
                            ElseIf Parts(2).Substring(1, 2) <> "MT" And Parts(2).Substring(1, 1) <> "AR" Then
                                InsertLineTable(Parts.ToString, "pmt") 'Insert Payment Row into Log Table
                            End If
...and so on

What I'm trying to do is trap 'MT' portion of the maintenance row or the same spot in a payment row.  Then depending if it's MT or not, then I know if it's a maintenance or payment row.  The problem I'm  having is my parts array.  I thought that Parts(2) would be starting at the next set of characters after the first set of characters with a space

so I though parts(2) started here but I"m wrong and need to know how I can trap that (I marked the place with 2 ||) and check if it's MT or not before I call my    InsertLineTable function:
                                                      | |
 2006030684324264293997849652    MTMASCOM0800                                    IAR10ncohen    CARD

how do I trap the parts of my line like this so I can check whether that position in the row is MT or not?
Question by:dba123
    LVL 34

    Accepted Solution

    is your file fixed-length or delimited?  I'm guessing it's fixed in which case you don't need the split function you just need to use the substring function.
    LVL 85

    Assisted Solution

    by:Mike Tomlinson
    When you split on " " (space) it treats EACH space as a delimiter.  So if you have two spaces in a row "  ", then you would have an empty item inbetween them.  If you have many spaces in a row then you get many empty elements in your resulting array returned from Split.


    (1) Replace multiple spaces with single spaces using a loop and then split like were before.
    (2) Use RegExs to parse it on white space.
    (3) Tread it as fixed width and parse using Substring() as BriCrowe suggests.
    LVL 96

    Assisted Solution

    by:Bob Learned
    If this data is positional (always stored in the same position), then you could just do a substring to pull out the data, once you determine which type of line you are dealing with.

    LVL 14

    Expert Comment

    Parts(2).Substring(1, 1) <> "AR"

    this will always evaluate true.
    LVL 1

    Author Comment

    >>>is your file fixed-length or delimited?
    Fixed...but there are about 3 different types of rows...they're not all the same format.  They're al 100 bytes but some rows have different spacing.  That's why I check a certain value 'MT' to determine what type of row it is then I split it out dependent on this paper I have that tells me what positiosn are what on that row so I can split into columns of my table. I will have 3 for each type of row...and I'll need to create a surrogate key for the Header row (signified by 'HD' in that type of row) and the header has related 'MT' and payment rows.

    The file is in this format

    Corresponding Detail rows (either maintenance and/or payment)
    Header Row
    Corresponding Detail Rows
    and so on.

    Here's the file:
    LVL 1

    Author Comment

    Basically I'll need probably 3 Insert Subs, one to insert to each table since the format is so different between a Header, Maintenance, and Payment row.

    So I'll probably have 3 Subs:

    LVL 1

    Author Comment

    >>>I'm guessing it's fixed in which case you don't need the split function you just need to use the substring function.
    yea, maybe what I do is pass the entire Line to my InsertLine Function, then in that function split the columns out using substring....sounds like that might be be the best approach.  But before that, use substring to determine if it's 'HD' or 'MT' or not 'MT' for payment rather than parts to check that value like I tried above in order to determine which  InsertLine sub to call later.
    LVL 1

    Author Comment

    Idle_Mind , thanks I was wondering about that point...thanks for explaining.
    LVL 1

    Author Comment

    BriCrowe and TheLearnedOne I think is the best route to go
    LVL 1

    Author Comment

    ok, I'll work on it now that I realize looking at the array Parts isn't the best option here...thanks all!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now