How to reference split lines in my Code

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?
Who is Participating?
Brian CroweDatabase AdministratorCommented:
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.
Mike TomlinsonMiddle School Assistant TeacherCommented:
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.
Bob LearnedCommented:
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.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

this will always evaluate true.
dba123Author Commented:
>>>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:
dba123Author Commented:
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:

dba123Author Commented:
>>>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.
dba123Author Commented:
Idle_Mind , thanks I was wondering about that point...thanks for explaining.
dba123Author Commented:
BriCrowe and TheLearnedOne I think is the best route to go
dba123Author Commented:
ok, I'll work on it now that I realize looking at the array Parts isn't the best option here...thanks all!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.