×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

export data from Cobol data files

export data from Cobol data files

export data from Cobol data files

(OP)
Hello
I have a problem. I need to export data from Cobol data files (dat, inx) to anything else (txt, cvs, excel access, whatever). I need to do this using C# and .NET platform. But I cannot open or read this files (I tried as flat file with fixed row size and separator, I tried different data readers like SiberDataViewer or even simple notepad) if open file I can see only strange characters. I wonder I anyone know some ODBC driver or data reader which will be able to open and read this files (free or with demos, trials to check if it works witch my dat,inx files before I buy it). About files – I know that they contain information about daily incomes/outcomes, guests, rooms, services etc. in hotel chain. Hotels use Cobol application called ReHot which store data in dat(data), inx(indexes) files. Software vendor of course won't help and application cannot export data to different files only print them on fiscal printer.
I will be very glad for any help/advice/suggestion you can offer me to solve this problem.
Piter

 

RE: export data from Cobol data files

It sounds like some kind of VSAM implementation (DAT = records themselves; IDX = index to the records).  The first thing that would be useful would be to identify the compiler/development environment that this program was created in, which might shed some light on the files involved.

The files are in simple binary code, unless some encryption hasn't been applied.  Which means it would be useful if you had the source describing the record format of these files.  If I read your post right in saying that "the vendor won't help", that probably means all you have is the executable, which means you won't have this.  

The vendor would have likely made this closed-source, which probably means there are EULA considerations involved.  Speaking of which, I'm not finding anything when I google the terms you used.

Anyhow, unless you have the record format of the file (Is this on the vendor website?), you're pretty much out of luck.

----------
Measurement is not management.

RE: export data from Cobol data files

(OP)
Vendor name is gip (unfortunately web site is in Polish so you wont be able to read it but I checked and there is only description of the software for customers, nothing for programmers :) )

Also I don't have any record format of these files. If I had this format probably I wouldn't need yours help. But right now I still need :/

RE: export data from Cobol data files

Well you can try loading the DAT file up in a hex editor and then start making guesses based on what you see, relating it to the known data from within the program (like text strings).  Then test by writing the data to see if the record is identical to what the program writes.  Trial and error, more or less.

Sorry that there isn't more help, but that's really all you can do without having the record definition.

----------
Measurement is not management.

RE: export data from Cobol data files

Another possiblity might be to somehow divert the printed reports to a file instead of the printer. Then you would have to parse the print lines as data.
 

RE: export data from Cobol data files

Are you sure this isnt an ebcdic format file also?
Thats a possibility..

If its mainframe or mini based, its probably ebcdic, you can use ftp to download local and then it should convert ebcdic back to ascii..
 

Gilbert M. Vanegas
Senior Programmer Analyst
County of San Bernardino - ISD
 

RE: export data from Cobol data files

no. if it is from a mainframe, downloading the file to ascii will most likely just make it unusable.

If you can determine what is the COBOL vendor/version used on those programs, then it will make it easier to determine which tool can help you do it yourself.

The vendor SHOULD give you this information, as well as the FILE layout of all your files (but not necessaraly the relation between each one).

The other option is as mrregan mentioned, to print all type of extracts into a flat file, and then process that file using your language of choice

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: export data from Cobol data files

Are you sure, that the application is written in COBOL?
I remember, that e.g. Foxpro index files had suffixes *.INX too.

At the bottom of
http://www.gip.com.pl/download/ReHot.pdf?tl=5
they write about daily and mothly reports in Excel format and about storing the statistics reports (daily and monthly) in central a SQL database.

To identify the format of the mentioned data file, it would be best, if you can post elsewhere an sample *.DAT file to download.   

RE: export data from Cobol data files

(OP)
Thx for all your advices I checked most of them (still checking :)).
"At the bottom of
http://www.gip.com.pl/download/ReHot.pdf?tl=5
they write about daily and mothly reports in Excel format and about storing the statistics reports (daily and monthly) in central a SQL database."
-    Yes but this is not a ReHot feature but extra paid addon :/ and I don't know if my client has it (checking right know)
Meanwhile I attached some files maybe one of you will be able to open it

http://www.box.net/shared/vce1r4rohh

Piter

RE: export data from Cobol data files

The 'dat' files appear to contain mostly comp numeric
fields, not binary. Comp numeric fields store some number of digits in half the space of the picture, for example: 10 digits in 5 bytes. Impossible to say what the values mean or where they start and stop. The inx files are meaningless to the data in question.  

RE: export data from Cobol data files

I tried convert the DAT-files using DataTools contained in the NetExpress Free edition, but without success.
Then I tried to analyze the file in HEX. The most frequent are characters 00 and the characters 0F occure regularly. So it seems that 0F could be field and/or record delimiter.

RE: export data from Cobol data files

In the previous post I examined the file Bp081005.dat
The file seems to have a record of the length 774 bytes and seems to contain 277 records.
If you multiply record length with number of records, i.e 774*277 = 214398, what exactly is the file length of Bp081005.dat.

RE: export data from Cobol data files

(OP)
Thx I will check it and see if I can use this info :)
Maybe I wwill find size of the collumns
Piter

RE: export data from Cobol data files

This is not Micro Focus indexed file format, must be something else.  

The X'0F' is in a way a delimiter.  This is the last byte of a packed decimal field.  The 0 is the last (rightmost) digit of the value, and the F is the sign (none).  The first half-byte could be any decimal digit, the second half-byte could be any hex value > 9.  C, D, and F are most common.  B & D are treated as negative, F is unsigned but treated as positive, A and E are uncommon but would be treated as positive.

RE: export data from Cobol data files

> Maybe I will find size of the collumns
The column (or field) separator seems to be the hex character 0F so for column length you only need count the bytes between 2 separators.

RE: export data from Cobol data files

(OP)
I checked it and nothing... I even read whole file to byte table and try to read anything like this

FileStream fs = File.OpenRead(@"C:\file.DAT");
BinaryReader br = new BinaryReader(fs);
byte[] by = new byte[br.BaseStream.Length];
by = br.ReadBytes(Convert.ToInt32(br.BaseStream.Length));
            for (i = 0; i < by.Length-10;i++)
            {                
                richTextBox1.Text +="\n"+i.ToString()+ " -> "+ BitConverter.ToInt16(by, i) + " "
                    + BitConverter.ToInt32(by, i) + " "
                    + BitConverter.ToInt64(by, i) + " "
                    + BitConverter.ToChar(by, i) + " "
                    + BitConverter.ToDouble(by, i) + " "

And so on.
And found nothing.  
But thx you all for suggesions. If somenoe come up witch sth else let me know.
 

RE: export data from Cobol data files

some guesses:
BP081005.DAT: lrl=774, key=X(10)=supplier or customer code
KR081005.DAT: lrl=857, key=X(02)=country code

RE: export data from Cobol data files

(OP)
what do you mean by 'key'?

RE: export data from Cobol data files

piterb,
COBOL packed data types (or decimal) COMP-3 are BCD-encoded. i.e. every digits is encoded in a nibble (4 bits).
For example (taken from a COBOL book):

CODE

PIC 9(4) VALUE 5678 COMP-3
is stored as: 05 67 8F (Hex)

PIC S9(4) VALUE 5678 COMP-3
is stored as: 05 67 8C (Hex)

PIC S9(4) VALUE -5678 COMP-3
is stored as: 05 67 8D (Hex)

So for example, if I look at the first 2 records of your file Bp081005.dat in HEX-view, I see this (... means continuation)

CODE

1 record
'PKE SA    
...
00 36 00 0F
...

2 record
'KPMG AUDYT
...
00 19 50 0F
...
The 1. record seems to have 1. field a character field, which contains 'PKE SA' and a packed numeric field with the value 36000.
The 2. record seems to have 1. character field which contains 'KPMG AUDYT' and a packed numeric field with value 19500.

The problem is, that all fields (character and numeric) are merged together to one fixed length record of 774 bytes.

But I don't know really how the packed values with decimal point are represented, i.e. I am not sure if the above packed field
00 36 00 0F
has a value of 36000 or 360.00.

If you want to encode the packed decimals you also need to unpack the binary representation into the nibbles and then process them.
You can find some inspiration how to do it e.g. in Python here
http://mail.python.org/pipermail/python-list/2000-April/031051.html
and how to do it in Perl here
http://www.perlmonks.org/?node_id=696456
I'm sure you will find something for Java or C# too.

RE: export data from Cobol data files

(OP)
BP081005.DAT in fact has 277 records but records don't have the same columns or 0F is not column delimiter
, = 0F
1record – 259b, 5b, 5b, 5b, 5b, 5b, 453b, 5b, 5b, 5b, 5b, 5b, = 774b
2record – 259b, 5b, 5b, 5b, 5b, 5b, 25b, 5b, 5b, 5b, 5b, 5b, 25b, 5b, 5b, 5b, 5b, 5b, 25b, 5b, 5b, 5b, 5b, 5b, 283b, 5b, 5b, 5b, 5b, 5b,  = 744b
3,4,5,6,8,13,18... like 1
7,9,10,11,12,14,16,18,19... like 2
15record – 259b, 5b, 5b, 5b, 5b, 5b, 81b, 5b, 5b, 5b, 5b, 5b, 341b, 5b, 5b, 5b, 5b, 5b, = 774
And few more record types and different records appears without any regularity or repeatable structure but all rec begins with 259b, 5b, 5b, 5b, 5b, 5b,
Ok I will continue my digging and let you know if something comes up.

mikrom
I will check your idea too Thx
 

RE: export data from Cobol data files

You are right: hex F is not a general field separator.
It is because only unsigned COMP-3 datatype ends with F.
But the record can contain several data: character fields,  numeric fields (unpacked) and packed numeric fields.
The only thing I can see in the record is, that first field is character field with length 10. Then come probably some numeric fields but with value zeros (that can be unpacked numeric fields) and then come some numeric fields terminated by hex F, that would be the unsigned packed decimals. And all that is merged together.
E.g. the structure can have a form:

CODE

01 Bp081005-RECORD PIC X(774).
01 Bp081005-RECORD-RED REDEFINES Bp081005-RECORD.
   05 FLD01  PIC X(10).
   05 FLD02  PIC 9(10).
   ...
   05 FLD10  PIC 9(10) COMP-3.
   ...

Here PIC X(10) is chareacter field, where each char is 1 byte, PIC 9(10) is numeric fields, where each digit takes 1 byte and PIC 9(10) COMP-3 is packed numeric field where each digit takes a 1/2 byte.
 
Therefore IMHO the only way to decode the fields in such a record exactly, is to know the COBOL-source-structure of the record. The structures of tables are stored in the COBOL source files - so called "copybooks".
Try to find something like Bp081005.cbl or Bp081005.cpy


If you don't have the source, the only way remains is to experiment.  

RE: export data from Cobol data files

First, break the data into columns based on having each packed decimal field in its own column and the surrounding ascii strings each in its own column.

Compare the results above with reports generated by the software to further split the ascii string columns and to find the decimal point for the packed decimal strings.  

If there are any columns which are neither ascii strings nor packed decimal fields, try interpreting them as 1) fixed-point binay of two, four or eight bytes (other byte lengths are possible but less common) or 2) floating-point binary of four or eight bytes.

Ascii strings may contain text data or numeric data.  If an ascii numeric string is preceded or followed by a "+" or "-", that is part of the string and is the sign.  An ascii numeric string may begin or end with a character which is a numeric value with a sign "overpunch".  This "overpunch" would convert a non-zero character to a letter and a zero character to "{" or "}"

RE: export data from Cobol data files

(OP)
mikrom
I know about copybooks but unfortunately I don't have any .cbl or .cpy files only a lot of .cob files. I am wondering if the structure can be stored in application source code

webrabbit
Nice idea but the problem is that application generates reports based on few files not just one so I cannot compare one file to report to guess file content:/

RE: export data from Cobol data files

piterb,
Ok, .COB is suffix for cobol source files too.
The table structure is eiher hardcoded in the COBOL program which reads the table, or more often it is copyied into the program from a copybook.

Then search in you *.COB-files first for something like this:

CODE

       INPUT-OUTPUT SECTION.
       ...
       FILE-CONTROL.
       ...
           SELECT BP081005
               ASSIGN TO DATABASE-BP081005
                   ORGANIZATION IS INDEXED
       ...
or alternative to DATABASE-BP081005 it could be DISK-BP081005 etc...

If you find that, than search in the same source for FD (File Description)

CODE

       DATA DIVISION.
       ...
       FILE SECTION.
       ...
       FD  BP081005.
You will probably find something like this

CODE

       FD  BP081005.
       01  BP081005-REC.
           COPY DDS-ALL-FORMATS OF BP081005.
(note: DDS-ALL-FORMATS is AS/400 specific, but in your case it will be only COPY something)

Then search in your *.COB files for the copybook named BP081005.COB
In this source file you will find the COBOL data record structure of the table BP081005.

RE: export data from Cobol data files

For R/M Cobol (Liant) .COB fiels are the compiled code, not the source code.

RE: export data from Cobol data files

Ok webrabbit,
I don't use RM COBOL, so I (wrongly) thought that it compiles to EXE like other languages (on Windows)  

RE: export data from Cobol data files

Piterb,
Your problem was interesting for me and therefore I tried to decode the COBOL binary.
As a tool I used Python, therefore I posted the program details and the results in this Python thread:
http://www.tek-tips.com/viewthread.cfm?qid=1510638&amp;page=1

They seems to use frequently for storing of sums of money packed decimals of length 11 digits - in cbl_data.cfg identified as P:11. However, from the binary we don't know where the decimal point is. This knows only the original COBOL program. But I guess If that are sums of money, they have 2 decimal places. So, if you set in the function parse_cbl_data() the optional argument format_packed=0, then you will obtain the packed numbers in raw format at 11 digits: 99999999999. The default is option format_packed=1, which gives you then 999999999.99

The results data seems to look like this (compare that with your reports)

CODE

ABB           0    600      0     500 000 000 000        982.00    300.00    65.00
ENERGO        0    500    500     300 000 000 000        960.00    270.00    39.00
              2    404    803     430 000 000 000        28003.00    2730.00    2760.30
WASA          0    100    300     300 000 000 000        540.00    0.00    39.00
KWB KONIN     0    400    400     400 000 000 000        480.00    0.00    13.00
UNICO         0    500    500     500 000 000 000        1660.00    420.00    195.20
ELEKTROWN     0    500    700     400 000 000 000        930.00    0.00    52.00
INST.CHEM.    0    100    100     100 000 000 000        160.00    0.00    0.00
GMINA         0    401    300     880 000 000 000        120.00    0.00    26.00
BIOENERGIA    0    500    500     500 000 000 000        900.00    0.00    1434.90
DPK           0    700    900     390 000 000 000        750.00    270.00    26.00
ECOLAB        0    100    100     100 000 000 000        180.00    0.00    13.00
PRUF          0    800    200                   -        2895.00    750.00    0.00
ASKOM         0    300    300     300 000 000 000        380.00    30.00    26.00
FOSTER        0      0      0                   -        0.00    0.00    39.00
KLIMATECH     0      0      0                   -        0.00    0.00    0.00
STAL-KRAFT    0    100    100     100 000 000 000        180.00    0.00    13.00
But some zoned numbers I leaved in a big chunks, because I cannot interpret them. This will need from you further effort: in manual adjustment of the config file cbl_data.cfg according to the printed reports from original COBOL system.

It's a first kick only - maybe I maked mistake elsewhere...  

RE: export data from Cobol data files

(OP)
Thank you all for help I stopped solving this problem because of deadline we have to make a deal with provider they give us piece of software which change this dat files into txt rapports and we process this.
But when finish this I probably came back here :)
Thank you once again
 

RE: export data from Cobol data files

I haven't looked at the data file as yet, but you might try using "Record Edit" which is a data file editor that can accept COBOL records and data types.  You can also build a record description within the application which will give you a way to browse and export the file.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close