EDIFACT How to group, spilt, sort and sum up values from a EDI string into variables in VB.NET

I am new to VB.Net 2008. I have a tricky task to resolve, it is regarding extracting characters (values) from a long string, the extracted values from the text shall be summed up and sorted by keywords, reformatted and saved into a CSV file.

It looks something like this but much longer :

UNH+RAM6957'COMPANY1BY500C10'ZEW+REQEST6957'COMPANY2SL200C20'COMPANY1SL300C10'ZEW

The values are seperated by ' .

As first step I splitted the string to make it readable, I used the function like:

Dim LineOfText As String
  Dim i As Integer
  Dim aryTextFile() As String

    LineOfText = p_EDI
    aryTextFile = LineOfText.Split("'")

      For i = 0 To UBound(aryTextFile)
        Console.WriteLine((aryTextFile(i)))
      Next i

Now the result looks like:

UNB+UNOA:1+CCP:ZEW+STE:ZEW+100901:1200+2010917283
UNH+M000001+ORDRSP:D:96A:UN:EGT102
BGM+02G::ZEW+NOMRES24364+34
DTM+Z05:0:805
DTM+137:201009011000:203
DTM+Z01:201009090400201009100400:719
RFF+AHI:GSCOMPANY1
NAD+ZSO+CCP::ZEW
NAD+ZSH+GSSTATKRAFT::ZEW
TDT+41G++70
LOC+Z11+:::TTF
LIN+1+23
LOC+Z11+:::TTF
QTY+Z05:0:KW1
DTM+2:201009090400201009100400:719
NAD+ZUS+GSBNP::ZEW
LIN+2+23
LOC+Z11+:::TTF
QTY+Z05:0:KW1
DTM+2:201009090400201009100400:719
NAD+ZUS+GSBPA::ZEW

So far so good:

Now I have to extract the date and time from the header: The line looks like:

**DTM+137**:201009011000:203 should look like
DTM+137:2010.09.01-10:00:203 and store it into a 'incomming_DTM' variable for example

Now the message period would be interresting to know: The line looke like:

**DTM+Z01**:201009090400201009100400:719 the output should look like:
DTM+Z01 2010.09.09-04:00, 2010.09.10-04:00 and store it into 'period_DTM' variable

As next step I need to parse the next lines until it reaches the KEYWORD LIN Like:

LIN+1+23
LOC+Z11+:::TTF
QTY+Z05:0:KW1
DTM+2:201009090400201009100400:719
NAD+ZUS+GSBNP::ZEW
NAD+ZSH+COMPANY1RPH N001::ZEW (P Character in word -> SELL QTY:0 KW/h)

LIN+2+23
LOC+Z11+:::TTF
QTY+Z05:0:KW1
DTM+2:201009090400201009100400:719
NAD+ZUS+GSBPA::ZEW
NAD+ZSH+COMPANY1RRH N001::ZEW (R Character in word -> BUY QTY:0 KW/h) 

and store the KEYWORDS "QTY" "DTM" "NAD+ZSH" and its following Characters into variables.

THEN I need to parse until it reaches the next LIN Keyword and store the keywords there into vaiables again. The goal of this complicated exercise is, to sum up values of QTY and NAD+ZSH+COMPANY1RVH and NAD+ZSH+COMPANY1RPH

If we have a closer look at the last zwo charaters in COMPANY1RRH and COMPANY1RPH we see RH and PH, RH means buy and PH means sell. Maybe it is possible to store BUY or SELL into a Contract vaiable for each LIN?

I need开发者_如何学编程 to sum up all sells and buys which where found in the string. Every LIN marks a time period of one hour, so we have probably 24 series per string which contains 24 LIN every LIN have a Time period, BUY or SELL keywords and a Quantity.

Can someone please help me with this task?

As first step, storing the keywords and its follwoing characters into variables would be a very good start. It might be very good to do that probably until the parser reaches the LIN, then store the found values into a CSV file or Array?, then parse until the next LIN and so on...

I would like to create a CSV file out of the results like: So the CSV should contain 24 records one per every hour per every LIN..

Dim csvData = Now & "," & "TRADED_QTY" & "," & DTM+Z01 & "," & "N" & "," & QTY & "," & "KWH/h" & "," & Contract

Console.WriteLine(csvData)
 Creating the CSV File with True Flag -> Append data to CSV.
   Dim csvFile As String = "C:\Test.csv"
     Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(csvFile, True)

Any ideas are highly welcome, I consider this as very complex task espacial as I am really new to VB.NET.

Thank you in advance!

I see "EDI" in your code. If this is an EDI format, then you should have, or be able to get, some kind of EDI specification. Likely, it will be a fixed-length specification, meaning that "Value X is characters 1 to 9", "Value Y is characters 10 to 11", "Value Z is character 12", etc.

Here is one possible approach to parse out the KEYWORDS as first step:

 Dim EDI As Object
    EDI = dataReader(0)
    'Convert EDI Object into a string and write it to the console.

    Dim p_EDI As String = Convert.ToString(EDI)

    'Create LineBreaks after every " ' "
    Dim LineOfText As String
    Dim i As Integer
    Dim aryTextFile() As String

    LineOfText = p_EDI
      aryTextFile = LineOfText.Split("'")

    'Starting with IF clause to find keywords
      For Each line As String In aryTextFile
       Console.WriteLine(line)
         If line.StartsWith("UNB") Then
           Dim foundUNB_Data = p_EDI.IndexOf("UNB")
   'Start at that position and extract UNB + 27 characters 
                      Dim UNBData = EDI.Substring(foundUNB_Data, 30)
                      Console.WriteLine(UNBData)


  ElseIf line.StartsWith("LIN") Then
     .
      .

        ElseIf line.StartsWith("QTY") Then
          .
            .
            End If
                Next

Any further ideas are highly welcome.. Thank you.