[RESOLVED] Parsing Help Please

[edihelp]

I am reading in a text file that contains multiple records in it. Each record when broken out and written to a new text file has to contain specific lines. The problem is finding a unique point to break up the records.

Here is an example of the text file.


0105159-5 SH100523110630100521
08MI9204000
08SU9255054B
13BP06106283AA POO1261181B
16EA
19NAFTA PILOT GROUP F 0000000000
20SF9255054C
20ST9204199
20MA9204199
27D G
28+00000000000ZZ100521
13BP06106283AA POO1261181B
16EA
19NAFTA PILOT GROUP F 0000000000
20SF9255054C
20ST9208481
20MA9208481
26DK99
27D G
28+00000000000ZZ100521
13BP06106283AA PONA
16EA
27Y Y
28+00000000008DM110307
28+00000000016DM110404
28+00000000024DM110502
28+00000000036DM110606


There are 2 records in this example.
The yellow section is the headerlines that needs to go into both files with each record.
The red section is one record.
The blue section is the second record.

Can't figure a way to separate these out. PLEASE HELP!!!!

[Runesmith]

What are the rules used in this report file? For example,
1. Is the header always 3 lines?
2. Does a record always begin with "13BP..." ?
3. Is the blue section just one record or is it actually 2 records?
4. Does a record always end in a line that begins with "28+..."?

Find those rules and you are halfway there.

[edihelp]

1. Actually there are 4 header lines. I left out the //STX line, but that should be included in the green section

2. Yes each record starts with a 13 line

3. The blue section is combined to 1 record because it is missing the lines starting with 20 which contains important information.

4. Each segment does not always end with 28+, there may be 29+ or even 30+.

The part that is getting me is that the first record (red section) doesn't have that combination like the second record (blue section) so it is throwing everything off.

[Runesmith]

Ok, then getting the header section separated is simple. You just need to take the first 4 lines of the text file.

So I assume the rule for a record would be: record must start with "13" line (can contain more than one "13"), must contain at least one "20", ends with the last "xx+" before the next 13. Also, "20" is never followed by a "+" (ie. no lines contain "20+").

So, the transition between 2 records happens if a "13" is detected and the previous block has at least one "13", at least one "20" and at least one "+".

Then the rest of the steps would be (in pseudo code),
- open text file, read off the first 4 lines - this is the header
- loop 1
- start new record, add header lines you read off
- set flags hasData to false, twentyDetected to false, PlusDetected to false, endOfFile to false
- loop 2
-read next line -> if end-of-file, then set endOfFile to true, exit loop 2
- if line starts with "13" and TwentyDetected and PlusDetected and hasData then exit loop 2
- add line to current record
- if line starts with "13" and not hasData then set hasData to true
- if 3rd character in the line is a "+" then set PlusDetected to true
- if line starts with "20" then set TwentyDetected to true
- end loop2
- if endOfFile and not HasData then exit loop1 (discard current record because it is empty)
- save current record
- if endOfFile then exit loop1
-end loop1
- you are done

[edihelp]

Thank you. I will try this out.

[edihelp]

I tried working through the psuedo code and the question comes up:

What do I do if there are mutiple lines with the "+"? Because after the first one all three flags are true and I exit the loop without getting the remaining lines for that record.

[cicatrix]

There's some logic behind these lines. What is it? It's difficult to look for a black cat in a dark room. Is it just a fragment and the next record follows the one you quotes or is it a complete data?

[edihelp]

This is a small portion of the text file. There can be numerous amount of records in one text file.

[cicatrix]

There must be something that delimits one record from another.
I suspect that this is it (according to your colors), each new record starts with lines like this:

13BP06106283AA POO1261181B

If records have variable length that there must be some other logic or otherwise the program that created this file won't be able to load it back (unless it's some kind of a log file).

[edihelp]

You are correct. Each new record does start with a "13" line. But the problem is that a record can have multiple "13" lines if the following segment does not contain "20" lines (blue section). My problem is that I don't know how to break up the records if they have one "13" line and "20" lines and not followed by a record with just a "13" line and no "20" lines. I hope you are following me.

[cicatrix]

Yes, but you should try to learn what is the logic behind all these characters.

Maybe you should look not for 13 but 13BP, or 13BP06 or even the whole line.

There are too many what-ifs if you don't know what this data means. Maybe 13 will change to 14 at some point further, etc. You really should try to get more information about the data format because right now it's meaningless and you can't process meaningless data.

[edihelp]

I understand the data. The "13" line will always be the "13" line. In fact there will always be:

"01"
"08MI"
"08SU"
"13BP" and so on.

The only things that change are the information that follows the line number. But as I stated before a record must contain:

"13"
"20ST"
"28"

That is why the blue section has two "13" lines because "20" line is missing in second segment.

[Runesmith]

I tried working through the psuedo code and the question comes up:

What do I do if there are mutiple lines with the "+"? Because after the first one all three flags are true and I exit the loop without getting the remaining lines for that record.

No it doesn't. The code closes the current record only if:
Hasdata is true (which becomes true when the first 13 is encountered) AND
TwentyDetected is true (which means at least one line starting with 20 is in the current record) AND
PlusDetected is true (which means at least one line with a "+" in 3rd position is in current record) AND
another "13" is found.

There are 4 conditions for closing - not just the 3 flags.

- if line starts with "13" and TwentyDetected and PlusDetected and hasData then exit loop 2

This fulfills all the criteria for starting a new record, if I understand your data correctly. It should not exit just for (HasData AND PlusDetected AND twentyDetected).

[edihelp]

What could be used to go through the loop (an incrementer)?

[Runesmith]

What could be used to go through the loop (an incrementer)?

Since there is no fixed length to the text file, I guess the loop would be an infinite loop that only ends when the EndOfFile is reached when you attempt to read the next line.

[kleinma]

I am still trying to figure out where the 'yellow' section is ???

[Runesmith]

I am still trying to figure out where the 'yellow' section is ???

Hmm...it seems to have turned to green for some reason. Those are the lines above the red section.

[Cavar]

I found it easier to load the file into a line array and loop it in reverse. Even though it's processed in reverse order, the records are kept in the correct order after parsing.

See my code below, which should parse the file as you intended, except reading in the header. You should be able to read that easily enough.

The code puts each record into an array that you can process even more if you like or need to.

This is a basic app and doesn't do ANY error prevention. I'll let you do that.

I also don't promise there aren't any bugs in this code.

Imports System.IO
Imports System.Collections.Generic

Public Class Form1
    Private Sub btnParse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParse.Click
        Dim filedata() As String
        Dim record As String = String.Empty
        Dim has20 As Boolean = False
        Dim startofrecord As Boolean = False
        Dim records As New ArrayList()

        filedata = File.ReadAllLines("c:\input.txt")

        For idx As Integer = filedata.Length - 1 To 0 Step -1
            startofrecord = False

            If filedata(idx).Substring(0, 2) = "20" Then
                has20 = True
            End If

            If filedata(idx).Substring(0, 4) = "13BP" Then
                startofrecord = True
                If record <> "" Then record = Environment.NewLine + record
                record = filedata(idx) + record

                If has20 Then
                    records.Insert(0, record)
                    has20 = False
                    record = String.Empty
                End If
            End If

            If Not startofrecord Then
                If record <> "" Then record = Environment.NewLine + record
                record = filedata(idx) + record
            End If
        Next
    End Sub
End Class

[techgnome]

It's an EDI document... the first two numbers of each row will tell you what the record type is for that line. I hate EDI files... they can be unweidly to deal with....

What I'm seeing in the OP, there is one header record, and THREE transaction records (marked by "13") ... the lines between the 13 lines are the detail records for each transaction. Really, you should be reading the EDI documentation for the format. It will tell you EXACTLY what the 13, 16, 19, 10, 26, 27, 28, etc records mean.

13BP06106283AA POO1261181B
16EA
19NAFTA PILOT GROUP F 0000000000
20SF9255054C
20ST9208481
20MA9208481
26DK99
27D G
28+00000000000ZZ100521

I'm not sure what the issue is... the "natural" break point is the 13 records. you shouldn't need to run it in reverse. It's simple:
1) Initialize all your variables.
2) Loop until you hit the first 13 record...
3) Begin another loop...
4) Read the data from the 13 and all records in between, storing the data you need in variables
5) Until you hit either EOF or another 13 record... then write the data to where it needs to go.

And the line header is the left two characters... so you should be looking for 13, not 13BP ... in fact, that makes is easier as you can then do a Select Case on the LEFT 2.

-tg

[Cavar]

It's an EDI document... the first two numbers of each row will tell you what the record type is for that line. I hate EDI files... they can be unweidly to deal with....

What I'm seeing in the OP, there is one header record, and THREE transaction records (marked by "13") ... the lines between the 13 lines are the detail records for each transaction. Really, you should be reading the EDI documentation for the format. It will tell you EXACTLY what the 13, 16, 19, 10, 26, 27, 28, etc records mean.

13BP06106283AA POO1261181B
16EA
19NAFTA PILOT GROUP F 0000000000
20SF9255054C
20ST9208481
20MA9208481
26DK99
27D G
28+00000000000ZZ100521

I'm not sure what the issue is... the "natural" break point is the 13 records. you shouldn't need to run it in reverse. It's simple:
1) Initialize all your variables.
2) Loop until you hit the first 13 record...
3) Begin another loop...
4) Read the data from the 13 and all records in between, storing the data you need in variables
5) Until you hit either EOF or another 13 record... then write the data to where it needs to go.

And the line header is the left two characters... so you should be looking for 13, not 13BP ... in fact, that makes is easier as you can then do a Select Case on the LEFT 2.

-tg

Well, based on the OP there are only 2 records in the data. There may be 3 "13" records, but the 2nd and 3rd are part of the same record because the 3rd doesn't have any "20" records.

The way you explained it is simple, but it doesn't seem like it's going to give him what he wants. It'll give him 3 records. *shrug* Maybe I missed something, which is very possible.

C

[techgnome]

I've worked with EDI files before... if a 13 record denotes a transaction header, IT ALWAYS does... Often some record types are optional... which might be why the thrid transaction doesn't have any 20 records... to be honest, I don't think any of us can provide any valuable advise since we don't know what we're dealing with.

Based on my experience, the 13 looks like it denotes a transaction header... then further info in the header record denotes what follows... PONA looks like it would be a file trailer record (providing either a summary or transaction totals, record counts, etc).

There isn't enough info in the posting to know what is really going on.

-tg

[Cavar]

There isn't enough info in the posting to know what is really going on.

-tg

Agreed!

I deal with parsing of BAI Bank Reconciliation files, which is another complex file layout with a repeating pattern of record types with optional record types.

Not having enough info will just have you spinning your wheels when trying to parse it.

C

[techgnome]

HA! I can actually do better... Try dealing with Utilities (gas, water, electricity).... across multiple states... each state has it's own interpretation as to what should be in the EDI files... and even in the same state, it can vary since 90% of the information is "optional" ... or the spec simply state "you have to have this" ... but doesn't say HOW to represent "this" ... so one utility will use "this" while another will use "ThIs" ... and a third will send "1" for this "2" for that and "3" for the other... glad it's not something I deal with any longer.

-tg

[Tom Sawyer]

I think the easiest thing to do is to break it up by the 13 lines and put them into an array. If there are special considerations which come in after that, such as if it doesn't have a 20 line, it's part of the record before it, deal with that afterwards.

[Cavar]

HA! I can actually do better... Try dealing with Utilities (gas, water, electricity).... across multiple states... each state has it's own interpretation as to what should be in the EDI files... and even in the same state, it can vary since 90% of the information is "optional" ... or the spec simply state "you have to have this" ... but doesn't say HOW to represent "this" ... so one utility will use "this" while another will use "ThIs" ... and a third will send "1" for this "2" for that and "3" for the other... glad it's not something I deal with any longer.

-tg

*head implodes* LOL

That's insaaane!

C

[Cavar]

I think the easiest thing to do is to break it up by the 13 lines and put them into an array. If there are special considerations which come in after that, such as if it doesn't have a 20 line, it's part of the record before it, deal with that afterwards.

That would certainly be a very easy way to do it.

[techgnome]

but not correct. I'm telling you record type 13 denotes a new record... the significance of that record depends on some other data on that line (PONA for instance vs POO (insert your own joke there)) ... Trust me, EDI is never "that simple" ... it's anything but.

what I can tell you is that they don't work in retro... once you have a new record marker, that's it... anything that follows is a different transaction/record ...

-tg

[Tom Sawyer]

but not correct. I'm telling you record type 13 denotes a new record... the significance of that record depends on some other data on that line (PONA for instance vs POO (insert your own joke there)) ... Trust me, EDI is never "that simple" ... it's anything but.

what I can tell you is that they don't work in retro... once you have a new record marker, that's it... anything that follows is a different transaction/record ...

-tg

Type 13 should indicate a new record. There's no guarantee that this isn't an update to a pre-existing system where multiple records are combined into one because that's the way that the company's internal systems are processing it.

It's a dumb way to do things, to be sure, but I think we've all had to deal with similarly inane business logic from time to time.

[techgnome]

It goes back to "we don't know what we are dealing with. The OP hasn't given us enough info or context." If we are dealing with what I really suspect is an EDI file, then the 13 record DOES denote a new record. Not "should" but "does". And frustrating as EDI can be... the rules are strict... it has to be.

-tg

[Tom Sawyer]

It goes back to "we don't know what we are dealing with. The OP hasn't given us enough info or context." If we are dealing with what I really suspect is an EDI file, then the 13 record DOES denote a new record. Not "should" but "does". And frustrating as EDI can be... the rules are strict... it has to be.

-tg

The rules for creating an EDI file need to be strict, but the rules for processing them can be as loose as the people consuming the file want them to be. I agree with you that I cannot see any valid rationale behind how the two 13 records can be merged into one, but that doesn't mean that somewhere along the line, someone went and built a system which does that for stupid reasons and they now need them lumped together in order to interface with that system.

If that's what he has to work with, then that's what he has to work with and there's not much option beyond trying to hammer a round peg into a square hole.

[cicatrix]

If you said that this is EDI format you'd saved a lot of time.
Here's a complete description of EDI file format:

http://www.slik.co.nz/HTML_help/edi_file_format.htm

I think that using this information will be easier than to invent your own interpretation.

[edihelp]

You are correct the 13 line is suppose to be a start of a new record, but each record has to contain a 20 line because information is need from the 20 line that is why they need to be merged together.

And yes this is EDI. I am new with dealing with it and it makes me want to scream everyday. I am thankful for all the help and comments, but I am still lost on what to do.

[techgnome]

Unfortunately I'm not sure how to tackle that problem, short of firing it back to the person that sent it and tell them to play by the rules.

Actually, maybe what you need to do is when you hit a 13 record, look for "PONA" and if you find it, ignore the 13 record, like it never existed. That would keep you within the "current record".

-tg

[edihelp]

If only.... If only. Each time they send in the EDI it is not the same. Sometimes they may have PONA and sometimes they have the same corresponding PO as the record above.

[techgnome]

That's the kind of thing that sends me to the looney bin. Seriously... if the source is internal, then you need to grab them by the neck and find out why they keep messing with it. If it's an external source, then you need to get a hold of them and find out what's up with the ever changing file... and why the EDI Standards aren't being followed.

-tg

[edihelp]

That's what I was wondering. When I was hired I researched EDI and it is suppose to be standard, but it is nothing standard about how they are sending their info. One transaction I may have just "28" lines. Another time I may have 29 and 30 lines and maybe in the same file I might have a variation. It is hard to break them down into records to push through my system.

So it is not me? I was beginning to feel so defeated.

[techgnome]

No... it's not you... granted EDI is roughly standardized based on the industry... and a lot of what is done may be optional... but a record break, should be a record break.

There has to be some kind of documentation... clearly they are basing their output on somekind of specification... maybe you could read through it to make some sense out of what they are doing.

-tg

[Runesmith]

None of the so-called "standards" stay standards, I'm afraid. I run in to similar problems daily whenever I need to extract data from the medical devices in our hospital for my research projects. HL7, MIB... none of those "standards" are interpreted the same by different manufacturers.

Just follow the pseudo-code I gave you. It parses this file the way you want it. If you need help converting the pseudo-code to vb.net, just give a shout.

[edihelp]

I need help converting this psuedo-code into vb.net

Then the rest of the steps would be (in pseudo code),
- open text file, read off the first 4 lines - this is the header
- loop 1
- start new record, add header lines you read off
- set flags hasData to false, twentyDetected to false, PlusDetected to false, endOfFile to false
- loop 2
-read next line -> if end-of-file, then set endOfFile to true, exit loop 2
- if line starts with "13" and TwentyDetected and PlusDetected and hasData then exit loop 2
- add line to current record
- if line starts with "13" and not hasData then set hasData to true
- if 3rd character in the line is a "+" then set PlusDetected to true
- if line starts with "20" then set TwentyDetected to true
- end loop2
- if endOfFile and not HasData then exit loop1 (discard current record because it is empty)
- save current record
- if endOfFile then exit loop1
-end loop1
- you are done


I am still a little unsure about the incrementers of each loop.

[stanav]

After 39 posts in this thread, we still haven't seen a single line of code that you wrote. Provided that many options/suggestions have been given to you (even some sample code), you should at least give it a shot. We're here to help you but if you rely on us to write the full working code for you so that you can just copy and paste, isn't that too much of an expectation?