Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Reading From A File

Posted on 2003-03-17
Medium Priority
Last Modified: 2010-05-03

I have been trying to work on a project that requires me to read a file with information in it.  After reading the file, I am going to process the data and then write an output text file.  Now the file that I am working on as an input is not in any particular format.  But I do have the definitions for the mainframe extract. It is not delimited and also one row can go over a line; while some lines are only on one line...


AS030210034RESERVESIE01212003322       322       N    0000000FD0121200305565501212003055655012120030557080121200306000501212003060332              01212003061226      T390  5008            73K   02288052  06951549  FW           2905 DERRICK DR                                    NE23   E23   E23                           (MOVADO TR & GUNTHER)                               100NY001                                  

I have been trying to work on this and pretty much pulled out most of my hair; if not all of it.  

Any help is appreciate and thanks in advance for all your help,

Raj Antony V
Question by:RajAntonyV
  • 3
  • 3
  • 3
  • +3
LVL 10

Expert Comment

ID: 8154141
Can you define which parts of the file you want broken down info fields.. also are those spaces or tabs seperating each field?

Author Comment

ID: 8154262
Lets say hypothetically, I wanted the only the ones that had the asterick next to them.  Also, another thing of importance is that:

1.  I haven't seen a row delimiter on the extract.  So I don't know where to stop on extracting.  I was hoping the fields that started with "FI" designate that as a new line...

2.  I haven't seen any tabs or spaces as a column delimiter either.  Only have the field defintions to work with.  Is it possible to reach character to character or lets say from character 10 to character 12 in VB?

I have been trying to read the file as binary file access and haven't had any luck on it yet...

Sample field definitions are listed below.  If you need all of it please let me know...

           02  TSFR-CALL-DATA-REC.
*************** 04  TSFR-CALL-SEG-TYPE      PIC XX.
                 88  TSFR-FDES               VALUE 'FI'.
                 88  TSFR-PILC-HDR           VALUE 'PI'.
                 88  TSFR-PILC-CALL          VALUE 'PS'.
                 88  TSFR-PILC-TRLR          VALUE 'PU'.
                 88  TSFR-EMC                VALUE 'EI'.
             04  TSFR-CALL-DATA-AREA.
               06  TSFR-CALL-NR          PIC X(9).
               06  TSFR-CALL-AGENCY.
**************  08  TSFR-CALL-AGEN      PIC X.
                    08  TSFR-CALL-AGTYPE    PIC X.
               06  TSFR-CALL-DAREA       PIC X(4).
               06  TSFR-CALL-HDAREA      PIC X(4).
               06  TSFR-CALL-INC-DATE    PIC X(8).
               06  TSFR-CALL-ORIG-CALL   PIC X(10).
************** 06  TSFR-CALL-NBR-TYPE    PIC X(10).
               06  TSFR-CALL-911         PIC X.
               06  TSFR-CALL-JURISC      PIC XX.
               06  TSFR-CALL-DR.
                 08  TSFR-CALL-DR-YR     PIC XX.
                 08  TSFR-CALL-DR-NBR    PIC 9(7).
               06  TSFR-CALL-JURISC      PIC XX.
               06  TSFR-CALL-RC-DATE     PIC X(8).
************** 06  TSFR-CALL-RC-TIME     PIC X(6).
               06  TSFR-CALL-ET-DATE     PIC X(8).
               06  TSFR-CALL-ET-TIME     PIC X(6).
               06  TSFR-CALL-DI-DATE     PIC X(8).
               06  TSFR-CALL-DI-TIME     PIC X(6).
               06  TSFR-CALL-EN-DATE     PIC X(8).
               06  TSFR-CALL-EN-TIME     PIC X(6).
               06  TSFR-CALL-OK-DATE     PIC X(8).

Thanks and appreciate all your help.
LVL 10

Expert Comment

ID: 8154306
2) Yes you can do from char to char.. use the mid$() function, which you can specify the start, and length... so from char 10 to 12 would be mid$(string, 10, 2)

In order to successfully parse this file, you have to find something in common for the data you want to grab.  Either where it starts, a tab before, a new line char, etc... then you can start parsing it...
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 8154970
How are the records written to the file? Are they not perhaps fixed length records?

Email me the sample file and i'll code up some script for it. but you must let me know what fields are needed and what rules apply for those fields.

Author Comment

ID: 8155148


The sample records are a mainframe dump that is being FTPed to a new location, from where it must processed using VB, and then inserted into a SQL Server database.

All I have the the field defintion or what I got from the guy as the field definitions.  But I am not sure how to determine when the end of line occurs...  One thing I had to go on is maybe target the "DI" or "FI" as the beginning of a new line.

Sample File From MainFrame Data

DI030210039FFFIREFIRE01212003112       112       N    0000000FD0121200306270901212003062814012120030628250121200306300901212003063403              01212003063635      P394 2304            48R   02330980  06985684  FW           4701 GOLD SPIKE DR                               NK24   K23   Q26                           (MALCOLM BL & TAILHEAD)                 B13          3 1N 0 1                                 040950040950JOHNB/////SWDISP                                                                                                        345 567 78
FD030210039FF01212003112       00000000TAILD   00101349003062814040950RIS2             000   TEXT:CONSEFS///GENERAL \COMP:BUBBA/////SEDISP \PH:874 457 9847

              ***04  TSFR-CALL-SEG-TYPE      PIC XX.
                 88  TSFR-KEIR               VALUE 'FI'.
                 88  TSFR-LKE9-HDR           VALUE 'DI'.
                 88  TSFR-KE84-CALL          VALUE 'PS'.
                 88  TSFR-MDKE-TRLR          VALUE 'PU'.
                 88  TSFR-EMS                VALUE 'EI'.
             04  TSFR-CALL-DATA-AREA.
            ***06  TSFR-CALL-NR          PIC X(9).
               06  TSFR-CALL-AGENCY.
                 08  TSFR-CALL-AGEN      PIC X.
              ***08  TSFR-CALL-AGTYPE    PIC X.
               06  TSFR-CALL-DAREA       PIC X(4).
               06  TSFR-CALL-HDAREA      PIC X(4).
               06  TSFR-CALL-INC-DATE    PIC X(8).
               06  TSFR-CALL-ORIG-CALL   PIC X(10).
               06  TSFR-CALL-NBR-TYPE    PIC X(10).
            ***06  TSFR-CALL-800         PIC X.
               06  TSFR-CALL-JURISC      PIC XX.
               06  TSFR-CALL-DR.
                 08  TSFR-CALL-DR-YR     PIC XX.
                 08  TSFR-CALL-DR-NBR    PIC 9(7).
            ***06  TSFR-CALL-JURISC      PIC XX.
               06  TSFR-CALL-RC-DATE     PIC X(8).
               06  TSFR-CALL-RC-TIME     PIC X(6).
            ***06  TSFR-CALL-ET-DATE     PIC X(8).
            ***06  TSFR-CALL-ET-TIME     PIC X(6).
               06  TSFR-CALL-DI-DATE     PIC X(8).
               06  TSFR-CALL-DI-TIME     PIC X(6).
               06  TSFR-CALL-EN-DATE     PIC X(8).
               06  TSFR-CALL-EN-TIME     PIC X(6).
               06  TSFR-CALL-OK-DATE     PIC X(8).
               06  TSFR-CALL-OK-TIME     PIC X(6).
               06  TSFR-CALL-AR-DATE     PIC X(8).
               06  TSFR-CALL-AR-TIME     PIC X(6).
               06  TSFR-CALL-CL-DATE     PIC X(8).
               06  TSFR-CALL-CL-TIME     PIC X(6).
               06  TSFR-CALL-DISPO       PIC X(6).
               06  TSFR-CALL-RD          PIC X(6).
               06  TSFR-CALL-FD          PIC X(8).
               06  TSFR-CALL-ED          PIC X(8).
               06  TSFR-CALL-MAP.
                 08  TSFR-CALL-MAP-PAGE  PIC X(4).
                 08  TSFR-CALL-MAP-X     PIC X.
                 08  TSFR-CALL-MAP-Y     PIC X.
               06  TSFR-CALL-MAP-COORDS.
                 08  TSFR-CALL-COORD-X   PIC X(10).
                 08  TSFR-CALL-COORD-Y   PIC X(10).
               06  TSFR-CALL-CITY        PIC X(13).
               06  TSFR-CALL-LOC         PIC X(40).
               06  TSFR-CALL-APT         PIC X(7).
               06  TSFR-CALL-AREA        PIC XX.
               06  TSFR-CALL-GEO-FLAG    PIC X.
               06  TSFR-CALL-PUNIT       PIC X(6).
               06  TSFR-CALL-OFF1        PIC X(6).
               06  TSFR-CALL-OFF2        PIC X(6).
               06  TSFR-CALL-OFF3        PIC X(6).
               06  TSFR-CALL-OFF4        PIC X(6).
               06  TSFR-CALL-OFF5        PIC X(6).
               06  TSFR-CALL-OFF6        PIC X(6).
               06  TSFR-CALL-CROSS       PIC X(40).
               06  TSFR-CALL-BEAT        PIC X(4).
               06  TSFR-CALL-DUPE-TO     PIC X(9).
               06  TSFR-CALL-PRIORITY    PIC X.
               06  TSFR-CALL-IN-PROG     PIC X.
               06  TSFR-CALL-PRIOR       PIC X.
               06  TSFR-CALL-ACT-CU      PIC X.
               06  TSFR-CALL-PH-FLAG     PIC X.
               06  TSFR-CALL-OS-FLAG     PIC X.
               06  TSFR-CALL-OV-FLAG     PIC X.
               06  TSFR-CALL-ALRM-LVL    PIC X.
               06  TSFR-CALL-XREF.
                 08  TSFR-CALL-FIRE      PIC X(11).
                 08  TSFR-CALL-LAW       PIC X(11).
                 08  TSFR-CALL-EMS       PIC X(11).
               06  TSFR-CALL-ENT-DID     PIC X(6).
               06  TSFR-CALL-DISP-DID    PIC X(6).
               06  TSFR-CALL-REPORTING-PARTY.
                 08  TSFR-CALL-NAM       PIC X(72).
                 08  TSFR-CALL-ADR       PIC X(50).
                 08  TSFR-CALL-PHON      PIC X(10).
                 08  TSFR-CALL-ORIGIN    PIC X(6).

Thanks for all your help and please let me know if you need anything else...

Raj Antony V
LVL 10

Assisted Solution

aeklund earned 668 total points
ID: 8155712

I think we are still unclear on how you want the data structured in the final database...

You have provided the source file, but how is it broken down? example:

The following snippet:
                88  TSFR-KEIR               VALUE 'FI'.
                88  TSFR-LKE9-HDR           VALUE 'DI'.
                88  TSFR-KE84-CALL          VALUE 'PS'.
                88  TSFR-MDKE-TRLR          VALUE 'PU'.
                88  TSFR-EMS                VALUE 'EI'.

How should this be put into a database according to your business rules?

field1 = "88"
field2 = "TSFR-KEIR"
field3 = "VALUE 'FI'."


field1 = "88  TSFR-KEIR               VALUE 'FI'."

you see what I mean?  We cannot guess for you, you have to define how you want this file parsed so that the data is usefull to you in a database format.  If you can define this "map" so to speak, then it will be easy for one of us EE to come up with some logic for you.
LVL 77

Expert Comment

ID: 8157675
They are fixed-width records without a delimiter. The field lengths are contained in the brackets following the PIC X, or the number of Xs if there are no brackets.
The '88' (constants) have an implicit length from their defined values.
You could use the 88 values to find the beginning of the record, but I think that you should just use this as a check against your calculations.
Note: If it's not 88, the first number on the line denotes a hierarchy level. The field size is only defined at the lowest level.

I'd be tempted to create a user type from the definition file by it into the declarations section of a module. Toy could replace the 'PIC(' with 'As String *',  the ')' with nothing and the '-' as '_'. I've done a bit of it so:
Type CobolRecord
    TSFR_CALL_DAREA       As String * 4
    TSFR_CALL_HDAREA      As String * 4
    TSFR_CALL_INC_DATE    As String * 8
    TSFR_CALL_ORIG_CALL   As String * 10
    TSFR_CALL_NBR_TYPE    As String * 10
End Type

This will ensure that you can define a variable as this type
Dim cr as CobolRecord
'Open the file in binary mode and to use the Get statement:

Do Until EOF(f)
  Get #f,,cr
LVL 77

Expert Comment

ID: 8157768
Sorry, I was a bit misleading about the 88 values.
The 88 values are permitted alternatives, used here to define the record type. The length is in the actually define at the 04 level above, so you might want your programme to check that each record begins with one of the 5 values specified.

Author Comment

ID: 8170238

The problem with doing that is there is differences in the length and definitions between the first line and the next line. See the first line is longer than the second line and they have a different definition compared to the first line.

DF030210034GGGGGGGGGG01212003322       322       N    0000000FD0121200305565501212003055655012120030557080121200306000501212003060332              01212003061226      T390  5008            73K   02288052  06951549  FW           2905 DEREK DR                                    NE23   E23   E23                           (Throckmorton TR & James)                               100NY001
FS030210034GG01212003322       00000000DISPATCH00301412003055708040950TDSE             000   E23,

FU030210034FFE23   E23   E23                           012120030557080121200306000501212003060332                                          01212003061120              01212003061120                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                N
Right now, when I use a "Line Input#" it reads the different lines as a single line; starting with the first line and going all the way to end of the last line.

I don't think it is seeing the end of line for each line and reading them as separate lines.  There is supposed to be an end of line character ({CL}{RF}) ( I believe) for each line when mainframe dumps it.

Anyway, any help is appreciated and thanks in advance for your help,

Raj Antony V


Assisted Solution

CD-Softy earned 664 total points
ID: 8175975
It appears that you have a file that contains header and data sections. Before you can succesfully read in this type of data, you must get the file specification from the system developers. From this you might establish the length of the header info, then strip out the data section into a UDT. Most files of this type have a fixed length header, which will give you info about the data records that follow. I would try to get this info first.
LVL 77

Accepted Solution

GrahamSkan earned 668 total points
ID: 8180030
CD-Softy may be right.

However, reading with Line Input assumes that it is a text file composed of text lines, each terminated with a carriage return and/or a line-feed character.

I've tried to define a user type using the whole of the second listing which I assumed to be the whole thing, but I've noticed a couple of anomalies.
First, the two postings of the file definition do not match, for instance the names for the '88' values differ. Secondly the definition :
              06  TSFR-CALL-JURISC      PIC XX.
appears twice. I don't think that would compile.

The length of the user type is 559, which is longer than any of the sample records.
Here it is:

    TSFR_CALL_SEG_TYPE      As String * 2
    '88                  TSFR_KEIR Value               'FI' * 1
    '88                  TSFR_LKE9_HDR Value           'DI' * 1
    '88                  TSFR_KE84_CALL Value          'PS' * 1
    '88                  TSFR_MDKE_TRLR Value          'PU' * 1
    '88                  TSFR_EMS Value                'EI' * 1
    TSFR_CALL_NR          As String * 9
    TSFR_CALL_AGEN      As String * 1
    TSFR_CALL_AGTYPE    As String * 1
    TSFR_CALL_DAREA       As String * 4
    TSFR_CALL_HDAREA      As String * 4
    TSFR_CALL_INC_DATE    As String * 8
    TSFR_CALL_ORIG_CALL   As String * 10
    TSFR_CALL_NBR_TYPE    As String * 10
    TSFR_CALL_800         As String * 1
    'TSFR_CALL_JURISC      As String * 1 'Instance 1
    TSFR_CALL_DR_YR     As String * 1
    TSFR_CALL_DR_NBR    As String * 7 'PIC 9 *  7
    TSFR_CALL_JURISC      As String * 1 'Instance 2
    TSFR_CALL_RC_DATE     As String * 8
    TSFR_CALL_RC_TIME     As String * 6
    TSFR_CALL_ET_DATE     As String * 8
    TSFR_CALL_ET_TIME     As String * 6
    TSFR_CALL_DI_DATE     As String * 8
    TSFR_CALL_DI_TIME     As String * 6
    TSFR_CALL_EN_DATE     As String * 8
    TSFR_CALL_EN_TIME     As String * 6
    TSFR_CALL_OK_DATE     As String * 8
    TSFR_CALL_OK_TIME     As String * 6
    TSFR_CALL_AR_DATE     As String * 8
    TSFR_CALL_AR_TIME     As String * 6
    TSFR_CALL_CL_DATE     As String * 8
    TSFR_CALL_CL_TIME     As String * 6
    TSFR_CALL_DISPO       As String * 6
    TSFR_CALL_RD          As String * 6
    TSFR_CALL_FD          As String * 8
    TSFR_CALL_ED          As String * 8
    TSFR_CALL_MAP_PAGE  As String * 4
    TSFR_CALL_MAP_X     As String * 1
    TSFR_CALL_MAP_Y     As String * 1
    TSFR_CALL_COORD_X   As String * 10
    TSFR_CALL_COORD_Y   As String * 10
    TSFR_CALL_CITY        As String * 13
    TSFR_CALL_LOC         As String * 40
    TSFR_CALL_APT         As String * 7
    TSFR_CALL_AREA        As String * 1
    TSFR_CALL_GEO_FLAG    As String * 1
    TSFR_CALL_PUNIT       As String * 6
    TSFR_CALL_OFF1        As String * 6
    TSFR_CALL_OFF2        As String * 6
    TSFR_CALL_OFF3        As String * 6
    TSFR_CALL_OFF4        As String * 6
    TSFR_CALL_OFF5        As String * 6
    TSFR_CALL_OFF6        As String * 6
    TSFR_CALL_CROSS       As String * 40
    TSFR_CALL_BEAT        As String * 4
    TSFR_CALL_DUPE_TO     As String * 9
    TSFR_CALL_PRIORITY    As String * 1
    TSFR_CALL_IN_PROG     As String * 1
    TSFR_CALL_PRIOR       As String * 1
    TSFR_CALL_ACT_CU      As String * 1
    TSFR_CALL_PH_FLAG     As String * 1
    TSFR_CALL_OS_FLAG     As String * 1
    TSFR_CALL_OV_FLAG     As String * 1
    TSFR_CALL_ALRM_LVL    As String * 1
    TSFR_CALL_FIRE      As String * 11
    TSFR_CALL_LAW       As String * 11
    TSFR_CALL_EMS       As String * 11
    TSFR_CALL_ENT_DID     As String * 6
    TSFR_CALL_DISP_DID    As String * 6
    TSFR_CALL_NAM       As String * 72
    TSFR_CALL_ADR       As String * 50
    TSFR_CALL_PHON      As String * 10
    TSFR_CALL_ORIGIN    As String * 6
End Type


Expert Comment

ID: 8532169
Hi RajAntonyV,
This old question (QID 20553792) needs to be finalized -- accept an answer, split points, or get a refund.  Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.

Expert Comment

ID: 8982928
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in a week or two.  I would appreciate any comments by the experts that would help me in making a recommendation.
It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:

Cleanup Volunteer

Expert Comment

ID: 9033998
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

 -->Split between aeklund and CD-Softy and GrahamSkan

Please leave any comments here within the next seven days.


Cleanup Volunteer

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question