Data Transferring from COBOL to MS Access via VB

Environments: Visual Basic, COBOL, database

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.



Comments

  • wheloltabotly PumeSonee Phobereurce 418031

    Posted by TizefaTaNaday on 06/03/2013 12:59pm

    DexAgisse airjordan5retrofireredforsale.holidaygiving.org SkinaviekNive airjordan5retrolaney.holidaygiving.org Jilaglirurf

    Reply
  • I need some more help

    Posted by salmanstar on 10/14/2004 07:55am

    i m also COBOL programmer but new to VB. the same task is given to me and i want to convert a simple cobol generated flat file in Access mdb using one button clik in vb. kindly give me some detail mean, Open , read, and then conversion code of command1.click thanks waitng for ur good respons

    Reply
  • cobol files conversion

    Posted by Legacy on 11/24/2003 12:00am

    Originally posted by: livio guerra

    fairly simple as long as you are dealing with LINE SEQUENTIAL files, i.e. the equivalent of a TEXT FILE.
    the problem arises if you have to read isam files, because each and every cobol dialect used to have its own proprietary IS format.
    so far I have not been able to find specifications on the net.

    Reply
  • Cobol Vb

    Posted by Legacy on 11/23/2003 12:00am

    Originally posted by: George1111

    I normally deal with Cobol (or other fixed length formats) by using the TYPE Statement.
    
    

    This allows you to define NAMES for each of the parts of the record rather than working with the MID statement, and thereby address the data just as you would under Cobol.
    This also allows you to copy "Cobol Copy Books" into your code and retain the use of the same variable names (if this is helpfull).

    Big Note:
    Cobol Names often are like "ACCOUNT-NUMBER", "BALANCE-DUE"
    Change the - to a _ so you get
    "ACCOUNT_NUMBER", "BALANCE_DUE" otherwise Basic will attempt to do a subtraction!

    EXAMPLE (Defined in a Module)

    Type BillRecord '110 BYTES LONG
    BCardNumber As String * 20
    BTimeCostDate As String * 24
    BTime As String * 10
    BCharge As String * 12
    BHourRate As String * 12
    BRef As String * 30
    BCRLF As String * 2
    End Type

    THEN USING THE RECORD IN YOUR PROGRAM

    Dim BillRec As BillRecord

    BillRec.BCardNumber = CardNumber
    BillRec.BTimeCostDate = DTPicker1.Value
    BillRec.BTime = txtElapsedTime.text
    BillRec.BCharge = txtChargeAmount.text
    BillRec.BHourRate = txtRatePerHour.text
    BillRec.BRef = cboBillReference.text
    BillRec.BCRLF = vbCrLf

    FileSize = FileLen(App.Path & "\Billing.Dat") ' Returns file length (bytes).
    NextRec = (FileSize / 110) + 1
    Filenum = FreeFile
    Open App.Path & "\Billing.Dat" For Random Shared As #Filenum Len = 110
    Put #Filenum, NextRec, BillRec
    Close #Filenum

    Reply
  • Few errors

    Posted by Legacy on 11/20/2003 12:00am

    Originally posted by: Dinesh Asanka

    Few errors have introduced when converting this document.
    
    will correct it soon

    Sorry for any inconvenice

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • IBM Worklight is a mobile application development platform that lets you extend your business to mobile devices. It is designed to provide an open, comprehensive platform to build, run and manage HTML5, hybrid and native mobile apps.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds