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

 

[petermeachem]

And they aren't for being bleeding rude either harebrain. If my children behaved like that they would be soundly told off and lose some appropriate privilege.

http://www.therockalltimes.co.uk

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[Databaseguy]

I gave CajunCenturion a star for the isDirty tip. I am used to always having a constant Element in my raw documents that triggers the DBWrite, or a constant number of detail segments. The varying number of detail segments is what threw me. Please excuse my obvious ignorance.

I must also point out that comments like:

and if he can't grasp how this works he may need to reconsider his choice of occupation.


And

I just wonder if Databaseguy has actually read all of the posts in this thread

Are completely uncalled for. I am not a programmer and do not profess to be. Writing code is simply one of the tools I use to get my job done.

As far as the EDI discussion, it's an old technology with a lot of major customers and suppliers that have legacy systems. I would like to see some standardization. I've read that eventually XML may take EDIs place.

Lastly, If anyone is curious, here is how the final routine ended up

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 strCustomer As String ' Cust No
Dim strDelimiter As String ' Delimiter
Dim strDep As String 'Department No
Dim blnDirty As Boolean
Dim strItem As String 'UPC
Dim intOnHand As Integer 'Qty in Stock
Dim intOrder As Integer 'Qty on Order
Dim intSold As Integer ' Qty Sold
Dim myString As String 'string for holding line
Dim dtmWeek As Date 'Date
Dim strWeek As String 'String to convert to datestrDelimiter = ""
'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
blnDirty = False
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
        'If first time through
        If blnDirty = True Then
        Call writeTodbTable(dtmWeek, strCustomer, strDep, strItem, intOrder _
     , intOnHand, intSold, rst852)
        strItem = myarray(7) 'UPC
        intOrder = 0 ' reset varible so it doesn't carry
        intOnHand = 0 ' reset varible so it doesn't carry
        intSold = 0 ' reset varible so it doesn't carry
        Else
        strItem = myarray(7) 'UPC
        blnDirty = True ' Reset for next time throgh
        End If
    Case "ZA" 'Item detail
        Select Case myarray(1)
            Case "QA" 'On Hand
                intOnHand = CInt(myarray(2))
            Case "QS" ' Sold
                intSold = CInt(myarray(2))
            Case "QP" 'On Order
                intOrder = CInt(myarray(2))
        End Select
     Case "CTT" 'Last DB write
        Call writeTodbTable(dtmWeek, strCustomer, strDep, strItem, intOrder _
        , intOnHand, intSold, rst852)
        intOrder = 0 ' reset varible so it doesn't carry
        intOnHand = 0 ' reset varible so it doesn't carry
        intSold = 0 ' reset varible so it doesn't carry
     End Select
Loop
Close #1
err_hand:
Debug.Print Err.Number & " " & Err.DESCRIPTION
End Sub

----------------------------------------------------

Public Sub writeTodbTable(dtmWeek As Date, strCustomer As String, strDep As String, _
strItem As String, intOrder As Integer, intOnHand As Integer, intSold As Integer, _
rst852 As ADODB.Recordset)
'Writes data passed in from ReadMervyns852 and writes to underlying table
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 Inventory On Hand
    .Fields(6) = intSold 'Qty Sold
.Update
End With
End Sub

Tyrone Lumley
augerinn@gte.net

 

[MichaelRed]

without regard to the various rabble rousing commentary, there are a few minor points.

in the 'final' code posted, [strDelimiter] is never actually set. It "looks like" the operation was 'lost' as the end of the declaration line for [strWeek]. However even the setting is not a generally useful way to do this. Since the various 'industry groups' are free to define their own delimiters, the onbly pratical approach is to set this as a Chr(X) so the users can actually figure it out.

The use of the constant (1) as a file handle is, at best, suspect. It defies generally accepted (and Ms. recommended) practice of the use of free file.

changing the value of the input arg (strfile) is generally not recommended, and concatenation of the callling arg with a fixed string and itself will cause some sonfusion to many, at least from a dcumentation perspective.

EDI documents CAN be quite large. I have generated documents of over a thousand lines, so the line by line reading can become a time factor, particularlly if you need to process a (large / significant) number of them on a regular basis.

Since you are aware of the (potential) use of the split function, you might want to look into the possability (actuallity) of being able to read the entire document as a single string and use aplit to seperate this into the individual "lines" then (still) use split as you currently do to generate the individual 'fields' of the line.

While I applaud the effort of 'learning by doing', you appear to have not reviewed the potential of using search to garner existing information / techniques from previoously posted threads.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Databaseguy]

Michael Red, thank you for your input and expertise.

Yes, I forgot to include:

strDelimiter = "" in the final code.

All of the examples I have seen for input use #1. When I open a file for write, I always use the FreeFile, and my assumption was for read # 1 was OK. However, upon further research , I found documentation that states It's better practice to a variable and assign an available file handle with the FreeFile function

Your suggestion that I use strDelimiter = chr(7) is
something I used to do, and will start doing again for better documentaion.

As for the concoctenation of the input argument and the file I want to open, how else would you suggest this is done ? I do not want to hard code the document being read.

Line by line vs the whole string. I have considered using something like

varWholeThing = Input(Lof(#intFileNumber),#intFileNumber). Then split by vbCLF, then by the delimiter.

Is this akin to your suggestion about reading the whole document first ?

I will implement all of your suggestions and again, thank you for sharing your expertise.

Lastly, I am remiss for not searching the forum for similair topics. I belong to other message boards/forums (some not computer related) and I know what's it's like to be asked something for the umpteenth millionth time by a "newbie" . My apologies to all.


Tyrone Lumley
augerinn@gte.net

 

[MichaelRed]

As for the concoctenation of the input argument and the file I want to open, how else would you suggest this is done ? I do not want to hard code the document being read.

I would simply provide a local variable in the procedure as the concatenated path and filename, or, perhaps, the entire path and filename could be placed in the arg.


varWholeThing = Input(Lof(#intFileNumber),#intFileNumber). Then split by vbCLF, then by the delimiter.


yes. again, advanced search will show some specific routines for this operation. One "whole procedure" I have posted is 'basGetFile', however there are others, whose names I do not recall, which illustrate the process of getting an entire file and breaking it into lines and fields using split. To the best of my memory, all (except basGetFile) will need modification to suit your specific circumstance (having variable number of fields within the record).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places