Data Transferring from COBOL to MS Access via VB

I recently got a project to convert COBOL data to MS Access. I was asked to do this by using Visual Basic.

If you don't know COBOL, let me describe the data format for you.

COBOL data is a flat data file with fixed length; the data has only Alpha and Numeric as basic data types. I have listed a sample COBOL data file.

01 EMP-RECORD.
03 EMP-ID PIC 9(6).
03 EMP-NAME PIC X(30).
03 EMP-JOIN-DATE.
05 EMP-JOIN-DATE-YY PIC 9(4).
05 EMP-JOIN-DATE-MM PIC 9(2).
05 EMP-JOIN-DATE-DD PIC 9(2).
03 EMP-BASIC-SALARY PIC 9(12)V99.

The lines of code above show the COBOL's basic data type. Let me briefly describe those formats. I think you are clever enough to understand that X is for alpha numeric and 9 is for numeric.

01 EMP-RECORD

01 indicates the Level. That is, EMP-ID (03 Level) comes under the EMP-RECORD (01 Level).

03 EMP-ID PIC 9(6)

03 says that it comes under Level 01, which is EMP-Record. EMP-ID is the field name. PIC 9(6) says that this field is numeric with a length of 6.

03 EMP-NAME PIC X(30).

It is same as earlier except for X(30). X(30) says that it is an alpha numeric field, 30 characters in length.

03 EMP-JOIN-DATE.
05 EMP-JOIN-DATE-YY PIC 9(4).
05 EMP-JOIN-DATE-MM PIC 9(2).
05 EMP-JOIN-DATE-DD PIC 9(2).

Above is the common way of storing the dates in COBOL. As you can see, dates are basically numeric types of data.

03 EMP-BASIC-SALARY PIC 9(12)V99.

This is the way that you save numeric data in COBOL. PIC9(12)V99. is used to store a length of 14 numeric data with 2 decimal places.

After having identified the COBOL data types, let me describe what my task was.

I was given a text file that has simple text values that have to be converted to the MS Access files. Sample data follows.

100101BILL GATES                            1980040100000005000075
100102SAMUEL HART                           1981010100000003500000
100103JOE ROBERTS                           1982043000000001500000

Above is a text file that was given to me for the data conversion. As you can observe, this is a tedious task of separating data. The first 6 characters are the Employee id; the next 30 are the employee name. Likewise, we have to identify the fields depending on the COBOL file format.

After opening the text file from the open command and separating it to the fields, the next task is to write that data in the MS Access table.

Writing EmpID and EMPName is NOT a big task.

MSACCESS.Fileds("EmpID")   = Mid(strEmpRecord,1,6)
MSACCESS.Fileds("EmpName") = Mid(strEmpRecord,7,30)

Then, we will come to the date and the amount, which you cannot write to the access directly. Dates are always problems for us, aren't they?

For the date, first you have to take year, month, and date to separate the variables. Then, do the following.

MSACCESS.Fileds("JoinDate") = CDate(strYear & "./." strMonth
                                            & "./." & strDate & 
"./.")

Even though it sounds fine, I have come across many occasions where the date is incorrect. This might happen as COBOL itself does not validate for the correct date. Therefor, the following will be the correct way of doing it.

If IsDate(CDate(strYear & "./." strMonth & "./." & strDate & 
"./.")) then
MSACCESS.Fileds("JoinDate") = CDate(strYear & "./." strMonth
                                            & "./." & strDate
                                            & "./.")
End If.

Next, we have to convert the numeric value. First, we can get that to the numeric variable.

fltBasicSal = Val(Mid(strEmpRecord,1,12))

Now, for the employee whose salary is 50000.75. But, you will get 5000075 in the fltBasicSal variable. So, you have to separate it.

fltBasicSal = int(fltBasicSal / 100) + ( fltBasicSal
            - int(fltBasicSal / 100) * 100 ) / 100

fltBasicSal will be 50000.75, which is the Basic Salary we need.

Then, you can simply pass that to the MS Access filed; that will be the end of our task. This will be helpful when you are converting text files' data to the database format.