We help IT Professionals succeed at work.

How to reference split lines in my Code

dba123
dba123 asked
on
Medium Priority
289 Views
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?
Comment
Watch Question

Database Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mike TomlinsonHigh School Computer Science, Computer Applications, Digital Design, and Mathematics Teacher
CERTIFIED EXPERT
Top Expert 2009
Commented:
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.

Either...

(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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008
Commented:
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.

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

this will always evaluate true.

Author

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 tables..one 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

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

Here's the file: http://www.webfound.net/example_mnt_file_incoming.txt

Author

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:

InsertLineHeaderTable
InsertLineMaintenanceTable
InsertLinePaymentTable

Author

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.

Author

Commented:
Idle_Mind , thanks I was wondering about that point...thanks for explaining.

Author

Commented:
BriCrowe and TheLearnedOne I think is the best route to go

Author

Commented:
ok, I'll work on it now that I realize looking at the array Parts isn't the best option here...thanks all!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.