Data Transferring from COBOL to MS Access via VB


Application Security Testing: An Integral Part of DevOps

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.

03 EMP-ID PIC 9(6).
03 EMP-NAME PIC X(30).

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 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.


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


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.


  • 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

  • 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.

  • 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


    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

  • 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

  • You must have javascript enabled in order to post comments.

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

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.