see the documentation in doc/adarep2excel.htm
code is running on unix and windows as well.
comments are welcome.
sos-adarep2excel(20060913-154342).zip (103 KB)
:shock: Interesting, a Java program posted into the Natural open source area 
Just mentioning it so people don’t download it and expect to load the source into Natural Studio.
yes, it is written in Java. Sorry for that, but it’s a little bit complex to create a BIFF8-Format-File (Excel) with Natural.
For us it’s very useful to have this report in excel for dba’s and developers, too. that’s why i think it might be useful for other natural/adabas dba’s/developers.
I did not test it, but i think, it should be running on every platform, on which Java (and Adabas) is available.
You could use open systems Natural to create an Excel file, there is an example of working with Excel files posted earlier.
The main difference would be that Excel needs to be installed on the same machine as the Natural program since it just does API calls and probably takes more time.
On the other hand, parsing the ADAREP can be done very easily in Natural where it’s a bit more difficult in Java.
I think you are absolutely correct, it is useful to have the Adarep in Excel format, and I really like to see the tools & utilities that others create, I appreciate you sharing it!
yes, you are right. it is possible to create excel-files using the object-model of excel (via excel.tlb) and the com-functionality of Natural (for Windows). but: 1) it is only available on Windows. 2) you must have at least an excel.tlb installed and 3) imho its very hard to code with natural, because you don’t have “intelli-sense” 
The Java-solution is running on Unix without windows and excel. (the excel-file is published by an apache-server on the unix-system).
of course is it possible to parse the AdaRep in Natural. Below is a solution. It is not a class-approach. That’s why it is not comparable to the Java-Solution.
* * <docu>
* <project> PPS </project>
* <name> CADAFL00 </name>
* <type></type>
* <tac></tac>
* <jobname></jobname>
* <title> Adabas-Utilies: AdaReport-Parser
* </title>
* <description>
* <para>
* This program scans an Adabas-Report (created using the keyword "summary"
* and "contents")
* </para>
* <para>
* The Layout of the AdaRep-Listings is different between the Adabas-Version
* <= 3.3 and >= 5.x.
* This Parser checks the Adabas-Version and analyse the report
* according to
* the Adabas-Version.
* </para>
* </description>
* <keywords>
* <keyword>AdaRep</keyword>
* </keywords>
* <categories>
* <category>Utility</category>
* <category>Adabas</category>
* <category>Parser</category>
* </categories>
* <date> 2006-09-21 -
* (c) 21.09.2006 by sglo111 </date>
* <author> sglo111 </author>
* <changes>
* <change who="sglo111" when="2006-09-21">
* <who>sglo111</who>
* <when>2006-09-21</when>
* <what>
* created
* </what>
* </change>
* </changes>
* </docu>
* Define Data
local using oADADbas /* ADABAS-Database(-Report)
local
1 #Buffer (a132)
1 Redefine #Buffer
2 #Allocations (a80)
2 Redefine #Allocations
3 #Filler1 (a1)
3 #strContainerFile (a10)
3 #strDeviceType (a6)
3 #intExtentsFrom (a11)
3 #intExtentsTo (a12)
3 #intNumberOfBlocks (a13)
3 #intBlockSize (a11)
3 #intTotalSize (a15)
*
1 Redefine #Buffer
2 #ExtentsAndPadding (a80) /* Block 2
2 Redefine #ExtentsAndPadding
3 #FileNo (n4)
3 Filler01 (a1)
3 #FileName (a16)
3 #strLoadDate (a12)
3 #strTopISN (a14)
3 #intIndexLevel (N8)
3 #intExtentsNI (N3)
3 #intExtentsUI (n3)
3 #intExtentsA (n3)
3 #intExtentsD (n3)
3 #intPadA (n3)
3 #intPadD (n3)
3 #strFlags (a4)
*
1 Redefine #Buffer
2 #FileAllocation (a80) /* Block 3
2 Redefine #FileAllocation
3 #FileNo2 (n4)
3 Filler11 (a1)
3 #FileName2 (a16)
3 #AllocatedBlocksNI (n8)
3 #AllocatedBlocksUI (n7)
3 #AllocatedBlocksAC (n7)
3 #AllocatedBlocksDS (n10)
3 #UnusedBlocksNI (n9)
3 #UnusedBlocksUI (n7)
3 #UnusedBlocksDS (n10)
*
1 #intWhichPart (i2)
1 #flgIsSeparator (l)
1 #isBlockActive (l)
1 #intTopISN (N10)
1 #strA (a30/1:99)
1 #i (i4)
1 #flgIsV5 (l) /* Report-layout 4 AdabasVersion >= 5
1 #flgLogRecords (l) init <true>
end-define
*
format ps=60 ls=132
Define work File 01 '/tmp/adarep.txt' Type 'ASCII-COMPRESSED'
* fine work File 01 '/tmp/adarep-v3.txt' Type 'ASCII-COMPRESSED'
*
read work file 1 #Buffer
if #isBlockActive Then
Perform CheckSeparator
if #flgIsSeparator then
#isBlockActive := false
escape top /* read next record
end-if
*
Decide on first Value of #intWhichPart
value 0
Perform ProcessLineOfBlock0
*
value 1 /* Process SizeTable
if #Buffer = ' ' Then
escape Top /* read next record
end-if
Perform ProcessLineOfBlock1
value 2 /* Extents-Table
if #Buffer ne ' ' then /* end of block 2 not marked by dashed-line
Perform ProcessLineOfBlock2
else
#isBlockActive := false
escape top /* read next record
end-if
*
value 3 /* File-Allocation-Block (V5: allocated Blocks)
Perform ProcessLineOfBlock3
*
value 4 /* V5 only: unused Blocks
Perform ProcessLineOfBlock4
none value
ignore
end-decide
else
Perform CheckSeparator
if #flgIsSeparator then
add 1 to #intWhichPart
#isBlockActive := true
escape top
end-if
if #intWhichPart = 0 Then
Perform ProcessLineOfBlock0
end-if /* #intWhichPart = 0
end-if
end-work
Define Subroutine CheckSeparator
if substr(#Buffer, 1, 10) = '----------' Then
#flgIsSeparator := True
else
#flgIsSeparator := False
end-if
End-Subroutine /* CheckSeparator
Define Subroutine WriteBuffer
* write #intWhichPart #buffer (al=80)
ignore
End-Subroutine /* Write-Buffer
*
Define Subroutine ProcessLineOfBlock0
separate #Buffer left justified into #strA(*) with delimiters '$, $'
giving number in #i
/* write #i
Decide for first condition
when substr(#Buffer,1,17) = '%ADAREP-I-STARTED'
#strAdaVersion := #strA(05)
if substr(#strAdaVersion, 1, 2) = '5.' Then
#flgIsV5 := True
end-if
#strReportDate := #strA(2)
#strReportTime := #strA(3)
when substr(#Buffer,1, 13) = 'DATABASE NAME'
#strDatabaseName := #strA(3)
when substr(#Buffer,1, 11) = 'DATABASE ID'
#lngDatabaseID := val(#strA(3))
when substr(#Buffer,1, 23) = 'MAXIMUM NUMBER OF FILES'
#lngMaximumNumberOfFiles := val(#strA(5))
when substr(#Buffer,1, 19) = 'ACTUAL FILES LOADED'
#lngActualNoOfFilesLoaded := val(#strA(4))
when none
ignore
end-Decide
end-Subroutine /* ProcessLineOfBlock
Define Subroutine ProcessLineOfBlock1
if #flgLogRecords Then
write #intWhichPart
#strContainerFile
#strDeviceType
#intExtentsFrom
#intExtentsTo
#intNumberOfBlocks
#intBlockSize
#intTotalSize
end-if
end-Subroutine /* ProcessLineOfBlock1
*
Define Subroutine ProcessLineOfBlock2
*
separate #Buffer left justified into #strA(*) with delimiters '$ $'
giving number in #i
#FileNo := val(#strA(1))
#FileName := #strA(2)
#strLoadDate := #strA(3)
#strTopISN := #strA(4)
#intIndexLevel := val(#strA(5))
#intExtentsNI := val(#strA(6))
#intExtentsUI := val(#strA(7))
#intExtentsA := val(#strA(8))
#intExtentsD := val(#strA(9))
#intPadA := val(#strA(10))
#intPadD := val(#strA(11))
#strFlags := #strA(12)
examine #strTopISN for ',' and delete
#intTopISN := val(#strTopISN)
*
if #flgLogRecords Then
write #intWhichPart
#FileNo
#FileName
#strLoadDate
#intTopISN
#intIndexLevel
#intExtentsNI
#intExtentsUI
#intExtentsA
#intExtentsD
#intPadA
#intPadD
#strFlags
end-if
*
end-Subroutine /* ProcessLineOfBlock2
Define Subroutine ProcessLineOfBlock3
*
if #flgIsV5 then
separate #Buffer left justified into #strA(*) with delimiters '$ $'
giving number in #i
#FileNo2 := val(#strA(1))
#FileName2 := #strA(2)
for #i = 3 to 6
examine #strA(#i) for ',' and delete
end-for
#AllocatedBlocksNI := val(#strA(3))
#AllocatedBlocksUI := val(#strA(4))
#AllocatedBlocksAC := val(#strA(5))
#AllocatedBlocksDS := val(#strA(6))
if #flgLogRecords Then
write
#intWhichPart
#FileNo2
#FileName2
#AllocatedBlocksNI
#AllocatedBlocksUI
#AllocatedBlocksAC
#AllocatedBlocksDS
end-if
else
if #flgLogRecords Then
write
#intWhichPart
#FileNo2
#FileName2
#AllocatedBlocksNI
#AllocatedBlocksUI
#AllocatedBlocksAC
#AllocatedBlocksDS
#UnusedBlocksNI
#UnusedBlocksUI
#UnusedBlocksDS
end-if
end-if
end-Subroutine /* ProcessLineOfBlock3
Define Subroutine ProcessLineOfBlock4
*
if #flgIsV5 then
separate #Buffer left justified into #strA(*) with delimiters '$ $'
giving number in #i
#FileNo2 := val(#strA(1))
#FileName2 := #strA(2)
for #i = 3 to 5
examine #strA(#i) for ',' and delete
end-for
#UnusedBlocksNI := val(#strA(3))
#UnusedBlocksUI := val(#strA(4))
#UnusedBlocksDS := val(#strA(5))
if #flgLogRecords Then
write
#intWhichPart
#FileNo2
#FileName2
#UnusedBlocksNI
#UnusedBlocksUI
#UnusedBlocksDS
end-if
else
/* Error: Block4 not possible with AdaVers <5
ignore
end-if
end-Subroutine /* ProcessLineOfBlock4
end
:idea: With Natural 6.2 you can create, write & read CSV (comma-seperated values) files which can be accessed by EXCEL.
tx for the posting,
it was never a big issue to create csv-files out of natural, that’s not a question of version 6.2.?. we are doing this since 10 years or more, even on ibm-host. this enhancement of natural is imho not so useful …
But: why should i create csv-files, when i can have pure excel?
It would be usefull, because many many other programs and programming languages got a CSV-plugin as well.
See Comma-separated values - Wikipedia
BTW: Did you implement the “real thing” (i.e. RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files )?
Yep you can always create any format by assembling it from the pieces. But now it is implemented in Natural. Means you don’t need to assemble it before the WRITE or disassemble it after the read.
DEFINE WORK FILE 1 #NAME TYPE ‘CSV’
WRITE WORK 1 VARIABLE #VALUE1 #VALUE2 #VALUE3
…
READ WORK 1 #VALUE1 #VALUE2 #VALUE3
A sample program is in SYSEXV(V62WFCSV).
Ok, it is no pure EXCEL but on the other hand it is easy to use 
BTW from mainframe I use Natural/Entire Connection to create EXCEL files.
Not bad! CSV means more than doing one COMPRESS … WITH DELIMITERS … You have to code a proper quotation logic as well. Now SAG is responsible for that
Lukas: have you ever tried to create a csv-file this way with headings and an alternate separator-character? i use 6.2.2 and i get a core-dump from the natparm-utility (hp-ux ia64) if i try to define properties for CSV. 
btw: why must i change the natparm, if i want to have headers or an alternate sep-char?
This functionality is not on top of my whish-list for “workFiles” in natural. for example: the workfile-number actually is a constant, i dream of a variable, so i can use it as parameter for a subprogram-call. Another wish is a function that gives me a “free”-workfile-number (like in VB). It is not possible to make a workaround for the programmer for this missing functionality. Making “easy things easier” is not helpful. Making “impossible things possible” should be the policy of development or enhancement of natural.
just my 2 cents … :lol:
Matthias: yes, we did implement the “real thing”. The CSV-Files are imported by oracle, oo, access, excel and others. Never a problem occurred.
some years ago i wrote a generator (in VB :shock: ), which is able to create on a mouse-click based on an lda/pda/view the natural-code-snippet to create or read csv.
I set header off in the NATPARM and use
WRITE WORK 1 VARIABLE ... The “VARIABLE” has the effect that I can use different lines to write, i.e. first I write my own header line and then the other lines.
The separator character can only defined in Natparm. If you get a dump, it looks like a bug I have heard (but not verified) that there was a problem like this. So maybe install newest version and retry. If this doesn’t help, send a request to Software AG.
On the other hand, to set the separator in the program, specify the work file number as variable and providing functionality to return the first free number sounds all useful. I would recommend that you open a C/E request for it.
Do you know the user exit USR2011N? With this exit you can save the settings of a work file and reset it later. Thus if you have a subprogram which wants to use a work file but does not know which one is free, it can save the settings of any work file, use its number and restore the settings in the end.