Do While ? 1

[Databaseguy]

I am sequentially reading through an EDI docuemnt. Here is a snippet of the raw data:

LIN153CB919531819VASOM00064UP658378239647
ZAQA77EA
LIN154CB919531822VASOM00065UP658378239654
ZAQA96EA
ZAQS1EA
LIN155CB919531818VASOM00066UP658378239661
ZAQA21EA
LIN194CB919531941VAYDM00250UP658378253551
ZAQA2779EA
ZAQP1056EA
ZAQS2EA

I am using the split function and reading the data into an array (myarray).For example, the first line becomes in myarray (LIN,153,CB,919531819,VA,SOM00064,UP,658378239747)
I then take the 7th element (the UPC) and write it to my db.


Now, my problem is the Lines starting with ZA. You'll notice that sometimes they have two or three entries, and sometimes only one. The 2nd element, QS , QP and QA have different meanings. How do I grab one, both values or all three values ?

This should be simple. I just can't see the forest cuase of trees.




Tyrone Lumley
augerinn@gte.net

 

[CajunCenturion]

After performing the spilt into an array, use the UBound function to get the number of elements in the array Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TheVampire]

Looks to me like you need to do two different splits.

Split it into one array, on the EA string that you have at the end of each line. ( This is assuming that the combination EA cannot appear anywhere else in the string of data )

Then take each element of that array, and split it using the other delimiter that you have.

This way you can look at the first element of the second split array, and see if it's a LIN or a ZA, and handle it accordingly.

Robert

 

[CajunCenturion]

Sorry, hit the return prematurely.

Then use a case statement on the 2nd array element to determine how to process the remaining elsments. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Databaseguy]

Maybe I didn't ask the question properly.

The first LIN line contains the product data. This is where I grab the UPC. After the LIN, there will be a QP, QA, or QS in the ZA segment, or a combination. That is, there will be one, two, or three ZA segments with QA, QS or QP qualifier. When I get to the nex LIN segment, that's a new product.

QA always comes first, then QS, then QP. I can't write to the DB when I hit QA, because it may be follwed by the other qualifiers. Same thing with QP, because it won't always be there.

I've tried:

Slect Case myarray(0)
Case "Lin"
strItem = myarray(7)
strLin = myarray(0)
Do While strLin <> &quot;LIN&quot;
input #1, mystring
myarray = split(mystring,strDelimiter)
Select Case myarray(1)
Case &quot;QA&quot;
intOnHand = CInt(myarray(2))
Case &quot;QS&quot;
intSold = CInt(myarray(2))
Case &quot;QP&quot;
intOrder = CInt(myarray(2))
End Select
Loop

But it just ignores me....



Tyrone Lumley
augerinn@gte.net

 

[ADoozer]

maybe this is another case for regular expressions LOL If somethings hard to do, its not worth doing - Homer Simpson

 

[harebrain]

It pays to be paranoid about case (i.e., upper and lower) when making comparisons. You also have to make sure that each iteration of your loop reads another input. Try something like:

Input #1, mystring
Do While Not EOF(#1)
	myarray = split(mystring, strDelimiter)
	If UCase$(myarray(0)) = "LIN" Then
		strItem = myarray(7)
	else
	Select Case UCase$(myArray(1))
      Case "QA"
        intOnHand = CInt(myarray(2))
      Case "QS"
        intSold = CInt(myarray(2))
      Case "QP"
        intOrder = CInt(myarray(2))
		End Select
	End If
	Input #1, mystring
Loop

 

[vb5prgrmr]

&quot;It pays to be paranoid about case&quot;...
Option Compare Text

Good Luck

 

[CajunCenturion]

You might try something like the following:

WriteDB = False
Input #1, mystring
Do While Not EOF(1)
   myarray = Split(mystring, strDelimiter)
   If UCase(myarray(0)) = "LIN" Then
      If (WriteDB = True) Then

         <Write to the Database>

      End If
      strItem = Trim(myarray(7))
      intOnHand = 0
      intSold = 0
      intOrder = 0
      WriteDB = True
   Else
      Select Case UCase(myarray(1))
         Case "QA"
            intOnHand = Val(myarray(2))
         Case "QS"
            intSold = Val(myarray(2))
         Case "QP"
            intOrder = Val(myarray(2))
      End Select
   End If
   Input #1, mystring
Loop

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Databaseguy]

OK, my bad for not posting the whole code. I sent you guy's off on a tangent. IF the QP was always present, the following would work

Public Sub ReadMervyns852(strFile As String)
'----------------------------------------------
'852 Reader                                   -
'Tyrone Lumley                                -
'24 Mar 2003                                  -
'Reads 852 EDI document and populates table   -
'----------------------------------------------
On Err GoTo err_hand
Dim rst852 As New ADODB.Recordset
Dim dtmWeek As Date 'Date
Dim strWeek As String 'String to convert to date
Dim strDep As String 'Department No
Dim strItem As String 'UPC
Dim intOrder As Integer 'Qty on Order
Dim intOnHand As Integer 'Qty in Stock
Dim intSold As Integer ' Qty Sold
Dim myString As String 'string for holding line
Dim strCustomer As String ' Cust No
Dim strDelimiter As String ' Delimiter
strDelimiter = ""
'Open undelying table
rst852.Open "tblTarget852", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Set document variable
strFile = "\\dellserver\users\Public\782002\GEDeskTop 624 On\" & strFile
'Open the file
Open strFile For Input As #1
Do While Not EOF(1)
    'Get the string
    Input #1, myString
    'Split it into an array
    myarray = Split(myString, strDelimiter)
    Select Case myarray(0)
    Case "GS" 'Customer number
        strCustomer = myarray(2)
    Case "XQ" ' date of report
        strWeek = myarray(2)
        strWeek = Mid(strWeek, 3, 2) & "/" & Right _
        (strWeek, 2) & "/" & Left(strWeek, 4)
        dtmWeek = CDate(strWeek)
    Case "N9" 'Department No
        If myarray(1) = "DP" Then
        strDep = myarray(2)
        End If
    Case "LIN" ' UPC and item info
        strItem = myarray(7) 'UPC
    Case "ZA"
    Select Case myarray(1)
        Case "QA"
            intOnHand = CInt(myarray(2))
        Case "QS"
            intSold = CInt(myarray(2))
        Case "QP"
            intOrder = CInt(myarray(2))
            'If QP Was always present, this would work
             With rst852
                .AddNew
                .Fields(0) = dtmWeek 'Date
                .Fields(1) = strCustomer 'custID
                .Fields(2) = strDep 'Department
                .Fields(3) = strItem 'Item
                .Fields(4) = intOrder 'OnOrder
                .Fields(5) = intOnHand 'Ending 

Inv             .Fields(6) = intSold 'Qty Sold
                .Update
            End With
        End Select
    End Select
Loop
Close #1
err_hand:
Debug.Print Err.Number & " " & Err.DESCRIPTION
End Sub

What I need to do is when I am in the ZA segment, find out of the next line in the sequntial file is &quot;LIN&quot;. If it is, that is when I write the variables to the underlying table and reset the variables. (Except for Customer, Department and date, which remain constant). If not, I set the Qty Sold and Qty on Order variables (if present)

Here is an abbreviated version of the whole raw document

ISA00 00 086111470200 126195987909 0303230941U004010000018170P>
GSPD61114702006195987909200303230941269X004010
ST852003211921
XQH2003031620030322
N9DP919
N9IA0375600
N1RLMERVYN92MRV
LIN1CB919530546VASOM6037UP499195305463
ZAQA1EA
LIN15CB919531204VAS0M3682UP658378211179
ZAQA8EA
ZAQS1EA
LIN209CB919532006VAFZM00019UP658378255425
ZAQA280EA
ZAQP198EA
ZAQS2EA
LIN210CB919532007VAFZM00012UP658378255456
ZAQA286EA
ZAQP192EA
ZAQS2EA
CTT210
SE464003211921
GE1269
IEA1000001817

The expected output would be
6111470200,919,3/16/2003,499195305463,1,0,0
6111470200,919,3/16/2003,658378211179,1,8,0
6111470200,919,3/16/2003,658378255425,280,198,2
6111470200,919,3/16/2003,658378255456,286,192,2





Tyrone Lumley
augerinn@gte.net

 

[CajunCenturion]

Databaseguy - you may have missed my post since we were posting at the same time. Although my suggestion does not incorporate all of the options that are in play, the technique shown show still get the job done. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[harebrain]

Your problem is that your control break is &quot;LIN&quot;, not &quot;QP&quot;. Rearrange your code like this:

    Case "LIN" ' UPC and item info
             With rst852
                .AddNew
                .Fields(0) = dtmWeek 'Date
                .Fields(1) = strCustomer 'custID
                .Fields(2) = strDep 'Department
                .Fields(3) = strItem 'Item
                .Fields(4) = intOrder 'OnOrder
                .Fields(5) = intOnHand 'Ending Inv             
		.Fields(6) = intSold 'Qty Sold
                .Update
            End With
        strItem = myarray(7) 'UPC
    Case "ZA"
    Select Case myarray(1)
        Case "QA"
            intOnHand = CInt(myarray(2))
        Case "QS"
            intSold = CInt(myarray(2))
        Case "QP"
            intOrder = CInt(myarray(2))
        End Select
    End Select

Just make sure to add a test so that the first &quot;LIN&quot; doesn't trigger the update because there wouldn't have been a previously collected order to save.

HTH,
David

 

[Databaseguy]

@ Harebrain

Thx.

I was thinking along those lines. Two problems.

1. The first &quot;LIN&quot; segment. What do I do with it ? I should write to the DB when I run across the &quot;LIN&quot; qualifier, but not the first time (as you mentioned) I could do something like one of the previous posters suggested and use a &quot;dirty&quot; variable.

2. When I'm at the end of the document, there will not be another &quot;LIN&quot; to trigger my database write. There is , however, a &quot;CTT&quot; at the end of the document. Perhaps I could use that to trigger the DB write ?

Right now, I'm actually considering looping through the document three times. Yuck !

Tyrone Lumley
augerinn@gte.net

 

[CajunCenturion]

<sign> Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[harebrain]

1. Yes, as CajunCenturion (who I think meant to write <sigh>) demonstrated, a &quot;first-time&quot; (&quot;dirty,&quot; call it what you will) variable is necessary.

2. Yes, your &quot;CTT&quot; line is another control break and should prompt you to trigger a write, which as you describe it will be the final write.

3. Read through the document three times? In the name of God, man, don't do that! Look, it's not that hard: EDI was designed for sequential processing--once through and you're done.

Go back to statements 1 and 2 here and think about how this works. Try not to make it more difficult than it really is.

HTH,
David

 

[strongm]

>EDI was designed for sequential processing

Possibly so. But frankly it was designed by idiots.

 

[CajunCenturion]

Would you buy the following:

<sigh> + <groan> = <sign>

OK, it was a typo.

I just wonder if Databaseguy has actually read all of the posts in this thread. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MichaelRed]

do an advanced search with &quot;EDI&quot; as the keyword. both Ms. A, & VB fora have a number of threads / posts which detail soloutions to this and other issues with respect to the reading (and writting) EDI 'documents'.

strongm, I agree, but they were not JUST idiots, they were even incompetent at being that! My (high school age) child has looked at some of this stttuuuuuffffffff from one of my 'jobs' and remarked that the whole schene looks like it was derived by a group of cave men during a pitched battle for the cave.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[harebrain]

strongm and MichaelRed,

How about foregoing the &quot;EDI is a piece of crap&quot; heat? If you've read Databaseguy's posts, EDI clearly is not his problem.

Pure and simple, he does not understand how to process flat files that contain control records and detail records. Many legacy systems depend on similar file structures, and if he can't grasp how this works he may need to reconsider his choice of occupation.

You're not helping: stay on subject.

 

[strongm]

And I'll repeat that these forums are for fostering dialog, not a free technical support site, which means that threads can quite legitimately wander from the headline topic on occassion.