Data Parsing Problem (1 Viewer)

ray705

Registered User.
Local time
Today, 07:36
Joined
Feb 3, 2003
Messages
45
Every couple of years I have a project that makes me wish I was back on a mainframe. I long for the days of COBOL and the ability to redefine a record in any number of ways. For those who only know PC programming here is what I want to be able to do.
In COBOL, you could tell the compiler to look at a record in various ways.
Code:
01 AAAA	PIC X(80).
01 BBBB REDEFINES AAAA.
   03 BB1	PIC X(20).
   03 BB2	PIC 9999 OCCURS 15.
01 CCCC REDEFINES AAAA.
   03 CC1	PIC XXXX.
   03 CC2	PIC 9999 OCCURS 8.
   03 CC3	PIC XXXX.
   03 CC4	PIC 9999 OCCURS 8.
   03 CC5	PIC X(8).

I currently have a couple of reports that come from an AS400 that I need to extract data from and put into an Access database. If this was a one time thing, I have a product called Monarch that will pull the data for me but this will be an ongoing process that I need to turn over to the end users. I need to make the import as transparent as possible so that the user doesn’t even have to think about it.
The report I have looks like this:
Code:
STAR2091 REPORT#: LISTPATN1A         **       PATIENT FILE       **         07/20/2006  12:50 PM PAGE     1
FAMILY PRACTICE                                                                            USER: CCC7330
-----------------------------------------------------------------------------------------------------------
    ACCOUNT # PATIENT NAME                SEX MTS STU? BIRTHDATE PCAR SETUP DATE  LAST-DIAG  PHYS RPHY  STA
    MED REC # SOC SEC NUM  CLOC  NPP  REL EMP?    WORK PHONE     EMPL LAST ACTIV  APPT NO-SHOW MISC DATA
-----------------------------------------------------------------------------------------------------------
        11100 BBBBBBB, CRISTOPHER H        M   S      10/19/1995  315 10/21/1996  V20.2      5555        A
            1 111-11-1111  CA     Y    C                               4/29/2005  2/22/2000  2
        11100 CCCCCCCC, RHONIE             F   M       5/20/1973  315  1/22/1996  V72.31     5555        A
            2 222-22-2222  CA     Y    S          (316)555-4482        3/29/2005  4/11/2001  4
        12222 DDDDDDDD, MICHELLE R         F   S      11/21/1995   77 12/02/1996  314.01     5555 7777   A
            3 333-33-3333  CA     Y    C                               3/22/2004 11/03/1998  5
        12222 DDDDDDDD, VIOLA R            F   S       1/25/1994   77  5/07/1997  462        5555 7777   A
            4 444-44-4444         Y    C                              12/10/2003  8/27/1999  7
        33369 EEEEEEE, JACOB D             M   S       7/26/1988   79  8/14/1997  558.9      5555        A
            3              CA     Y    C                               2/02/2006  3/01/2006  3
        44459 FFFFFFF, SEMA                F   S       7/08/1998   76  8/01/2000  V70.3      5555 7777   A
            3                     Y    C                               7/21/2005

                              ***** ***** *****  **********  ***** ***** *****
                                      PRINTED PATIENT RECORDS:   233
                                NOT QUALIFIED PATIENT RECORDS: 35410
                                        TOTAL PATIENT RECORDS: 35643
                              ***** ***** *****  END REPORT  ***** ***** *****
                                 DR LANDERS PATIENT LIST
STAR2091 REPORT#: LISTPATN1A         **       PATIENT FILE       **         07/20/2006  12:50 PM PAGE    17
FAMILY PRACTICE                                                                            USER: CCC7330
-----------------------------------------------------------------------------------------------------------
                    **********************************************************************
                    REPORT SEQUENCE: A  ACCOUNT #
                 SUB-ENTITY # RANGE:            ALL
                    ACCOUNT # RANGE:          1  -  999999999
                     MED REC# RANGE:            ALL
                  SOC SEC NUM RANGE:            ALL
                   SETUP DATE RANGE:            ALL
                    BIRTHDATE RANGE:            ALL
               CHART LOCATION RANGE:            ALL
                 PRIM CARRIER RANGE:            ALL
                EMPLOYER CODE RANGE:            ALL
               PRIM PHYSICIAN RANGE:       5555  -  5555
                REF PHYSICIAN RANGE:            ALL
               DATE OF LAST ACTIVITY            ALL
                 APPT NO-SHOW RANGE:            ALL
             APPT NUM NO-SHOW RANGE:            ALL
                            ACK NPP: ALL
                             STATUS: A
                       FOOTING TEXT: DR XXXXXX PATIENT LIST
                    **********************************************************************
                                 DR XXXXXX PATIENT LIST

This is the output I need:
Code:
ACCT   SEQ  PATIENT                 SSN          SEX   MS   DOC
11100   1   BBBBBBB, CRISTOPHER H   111-11-1111   M    S    5555
11100   2   CCCCCCCC, RHONIE        222-22-2222   F    M    5555
12222   3   DDDDDDDD, MICHELLE R    333-33-3333   F    S    5555
12222   4   DDDDDDDD, VIOLA R       444-44-4444   F    S    5555
33369   3   EEEEEEE, JACOB D                      M    S    5555
44459   3   FFFFFFF, SEMA                         F    S    5555
The SEQ and SSN fields are on separate lines. The data is in fixed locations but I have two different record types. Not to mention all the page header and footer information I need to skip.
I have tried using user defined type records but once I’ve done the Get record into a type record how do I assign it a different record type?
I tend to revert back to my batch processing days and parse everything out one line at a time.
Code:
    strCheck = Left$(AllData, 10)
    If strCheck = "WESLEY MED" Or strCheck = "AUDIT TRAI" Or strCheck = "For acct  " Or strCheck = "          " Then
       GoTo VL_Exit
       End If
    
    sgl2 = Val(Mid$(AllData, 28, 10))
    If sgl2 > 1000000 Then
       Call NewEmpl
       GoTo VL_Exit
       End If

Maybe I was a batch programmer for too long and just can’t think in any other terms. I’m hoping there is a better way of solving this problem.
 

KenHigg

Registered User
Local time
Today, 08:36
Joined
Jun 9, 2004
Messages
13,327
So do you simply get one of these at a time in a simple .txt file?
 

ray705

Registered User.
Local time
Today, 07:36
Joined
Feb 3, 2003
Messages
45
Yes, the file is .txt format.
No, the file is multiple pages. The current file I have is 17 pages.
 

KenHigg

Registered User
Local time
Today, 08:36
Joined
Jun 9, 2004
Messages
13,327
Hum - That's a hum - dinger... Have been able to get it into Access at all? Is one page 1 record or 1 field or how do you have it?
 

ray705

Registered User.
Local time
Today, 07:36
Joined
Feb 3, 2003
Messages
45
I have posted an example of the report in the first post. The second section of 'code' is the report.
 

Bodisathva

Registered User.
Local time
Today, 08:36
Joined
Oct 4, 2005
Messages
1,274
I would take the "one line at a time" approach and use the read() and readLine() methods of the TextStream object. Use the readLine(x) where x is the number of lines in the header, you are now ready to read the records. Use the While Not EOF condition and split the read up into parts for each record. looks like 5 chars for the account number plus 13 chars for the padding, so you would use acctNum = trim(inFile.read(18)) . Once you have isolated all of the various parts, you can place them into the db any way you see fit.
 

ray705

Registered User.
Local time
Today, 07:36
Joined
Feb 3, 2003
Messages
45
That is basically what I have done before. I was hoping there was a better solution.
 

Users who are viewing this thread

Top Bottom