Define Data from Spreadsheet

Define Data from Spreadsheet 1

1.      Starting from the Menus. 1

1a.     Find the spreadsheet 1

1b.    Locate the Spreadsheet Data. 3

2.      Starting from Import Conversion Spreadsheet 5

3.      Start [Create Definition] 7

4.      Continue the Jazz Definition: Fields and Groups. 8

4a.    Preamble. 9

4b.    The First Field. 9

4c.    Jazz Formats. 10

4d.    Spreadsheet Codes and Jazz Types – more detail 11

4e.    Some situations where Jazz gets it wrong. 12

4f.     Other Issues. 13

5.      Review the Jazz Definition. 16

 

This form is invoked

·         From the menu New/Data/Define Data from Spreadsheet

·         From the menu New/Data Conversion/Define Data from Spreadsheet

·         From [Get Input File Definition] or [Get Output File Definition] from the form Import Conversion Spreadsheet. 

 

Its function is to read an Excel spreadsheet and generate a Jazz data definition from it.  The definition should be reviewed and probably edited before use.

1.                Starting from the Menus. 

1a.     Find the spreadsheet

When first seen the form looks like this: -

 

Click [Find] for a Windows Explorer dialog to locate and open the spreadsheet.  When a spreadsheet is selected more controls appear on the form: -

 

and the selected spreadsheet is displayed: -

 

By the way, the spreadsheet might be displayed underneath other windows.  Click the Excel icon in the task bar to bring it to the front, and arrange your screen so that you can see both it and the Jazz form.  If your PC has an extension screen it is especially convenient to have the spreadsheet open in one screen and Jazz in the other.

1b.    Locate the Spreadsheet Data

If there is a row containing column headings, like row 2 in the example above then Jazz can probably figure out where to find everything it needs, so the first thing to do is to tell Jazz where to find this column.  If there is no suitable row in your spreadsheet then you’ll need to fill in all the locations yourself.  Enter First Data Row as a number, and the other values as single letters, A to Z.

 

If a Heading Row number is given, then Jazz calculates the locations like this: -

·        First Data Row.   Heading Row + 1

·        Field Name.          This is the first column in the Heading Row that contains “name”.  This is column A.  Note that “name” could be “Name”, “NAME”, or any other variation: the test is not case-sensitive.  Also the test just looks for “name”, this might be part of a longer value (“Field Name” in this example), or even part of a word – perhaps “COBOLNAMEVALUE”.

·        Level.   There might be a separate column containing the level numbers, or they may prefix the name column, as here.  Jazz tests to see if the first data value in the Name column is preceded by a number: if so then the Level column is the same as the field name.  Otherwise Jazz looks for a column containing “level”.

·        Occurs, Redefines, Type, Length, and Position are all similarly found by searching the heading line for a title containing the relevant word.  In the case of Occurs and Redefines only the length underlined is needed.

 

Values for Field Name, Level, Type, and Length are required.  Occurs, Redefines, and Position are optional.

 

In this example we simply enter 2 in Heading Row, and Jazz responds: -

 

Check the column assignments.  If the columns don’t follow the rules above Jazz won’t have sorted it our correctly.

 

Before going on with [Create Definition], you may like to set these checkboxes: -

[  ]  Replace Jazz Definition.  Check this if your spreadsheet is going to define “MyJazzRecord.jzc”, and this already exists. If you don’t check this and, at the end of the process Jazz finds that MyJazzRecord.jzc does exist then you will be prompted about whether you want to overwrite it.

[  ]  Define 2 records.  Click this if the spreadsheet contains two sets of columns and you want to generate both.   

[  ]  Replace 2nd Jazz Definition.  This functions like “Replace Jazz Definition”.

[  ]  Show Jazz on SprdSht.  Check this if you want the spreadsheet to be updated to put the definition into Jazz format, including adding extra lines for END GROUP statements. 

 

Processing continues with Step 3.

2.      Starting from Import Conversion Spreadsheet

We start this process from Import Conversion Spreadsheet.  Suppose that we import a spreadsheet like this that contains the record layouts in a COBOL-like notation, and we don’t yet have Jazz definitions of these records: -

 

Because the Jazz definitions don’t exist [Process Spreadsheet] is disabled and we can’t continue to generate a conversion program: -

We need to attach Jazz file definitions to the spreadsheet, which we do by clicking the [Get Input File Definition] and [Get Output File Definition].  When we do this we are offered options: -

and we click [Create Data from Spreadsheet] to read the definitions within the imported spreadsheet.  We are starting with a spreadsheet that has been formatted with an appropriate template, and so Jazz already knows where to find the heading and data rows, and which columns to use. 

 

Before continuing with [Create Definition], you may like to set these checkboxes: -

[  ]  Replace Jazz Definition.  Check this if your spreadsheet is going to define “MyJazzRecord.jzc”, and this already exists. If you don’t check this and, at the end of the process Jazz finds that MyJazzRecord.jzc does exist then you will be prompted about whether you want to overwrite it.

[  ]  Show Jazz on SprdSht.  Uncheck this if you don’t want the spreadsheet to be updated to put the definition into Jazz format.  Here the default is checked, unlike the default when Define from Spreadsheet is invoked form the general Jazz menu.

3.      Start [Create Definition]

Until you’re familiar with this process leave the [P] Step check box checked.

 

Click [Create Definition].  Jazz responds by highlighting the first data line: -

 

The first line must be an 01 level, and it will become the name of the file. 

 

The spreadsheet, like a COBOL record layout, doesn’t include file type information so Jazz asks us what file type to use.  Default is VB, possible values are V, VB, F, FB, U, VSAM, XIO, and WORK.  Values are not sensitive to case: for example VB, vb, Vb, and vB are all equivalent: -

 

If click [OK] with this value, or any other valid value except XIO and WORK, then the file name must be valid according to IBM’s rules.  AMBS-R2 is not, so next we see: -

 

Jazz has suggested a file name, but if this is not the name we want we can change it. Click [Return] and Jazz returns to the Define Data form with he DEFINE statement displayed: -

4.      Continue the Jazz Definition: Fields and Groups

The first line had dealt with the level-1 definition, and this has become the file name and type.   Subsequent lines deal with fields and groups within this file.  With [P] Step checked, every time we click [Next] the highlighting will move down one line, and the next Jazz line will be displayed before it’s written into the definition.

4a.    Preamble

Before continuing with fields and groups have a look at the checkboxes towards the bottom of the form: -

 

Length.  Suppose the spreadsheet contains a repeating field, say

 

Is the length of 5 the total length of all 5 occurrences, or the length of each occurrence of FILLER?   If we check [  ] Table then Jazz used the first rule, generating a definition FILLER (5) CHAR(1).   If we check [  ] Field then Jazz uses the second rule, generating FILLER(5) CHAR(5).

 

[  ]  Step.   Uncheck this when you get tired of stepping through every line, and Jazz will then loop through each line without stopping unless there is something that needs your input.

 

The other checkboxes – Prompt for Type, Prompt on Redefines, Prompt on Index, and Prompt Occurs not Nbr – cover various situations where the spreadsheet doesn’t follow the strict rules that Jazz would like and so Jazz makes some guesses.  Leave them checked until you become familiar with this process, but like Step you can uncheck them when the dialogs become irritating.

4b.   The First Field

Line 4 contains

 

On clicking [Next] Jazz displays: -

 

Here Jazz has encountered type “9” for the first time.  It makes a guess that this is equivalent to the Jazz type “PIC ‘9(1)’” (in this case the field length is 1), meaning a single numeric character.   See below for more information on the way that Jazz interprets the Type column.  If Jazz has made a mistake then we can give a different value for Jazz Type.  We won’t see this message for other lines with type “9”, but unless we uncheck [P] Show message again we’ll see this when we encounter another type value.

 

On clicking [Close] we see: -

 

Note

1.                  The name is “NOM-ACH-AMT-PCT-FLAG”, not “AMBS-NOM-ACH-AMT-PCT-FLAG”.   Jazz remembers the prefix from the first line, and removes this from every following line, except in cases like “AMBS-1ST” where removing it would create an invalid name.  “1ST” is not a valid Jazz or COBOL field name because it starts with a number.

4c.    Jazz Formats

Continuing with [P] Step checked we click [Next].  On line #5 we encounter a new type code: -

 

Jazz responds: -

 

When we encounter a new Type code Jazz tries to guess the equivalent format, and it displays this form.  If it guesses incorrectly we can enter the correct result.  In this case the Jazz format “DECIMAL(17)” is correct: this format means that the value is a packed number stored as 2 digits per byte with a sign in the last half byte. 

 

As we continue down the spreadsheet, clicking [Next] for each spreadsheet row, Jazz will respond with the “JazzTypeFromXLS” form whenever it encounters a new code.  Here are some more examples: -

 

 

In both cases Jazz has made the correct guess, and we don’t need correct it.  You should check that the code is correct: see below for some situations where Jazz gets it wrong/

4d.    Spreadsheet Codes and Jazz Types – more detail

You can use any code you like, and assign whatever Jazz Type you like to that code, but you may find it easier if you use codes that Jazz is likely to guess correctly.  Codes are usually COBOL pictures, but they can be any unique string.  Jazz will trim blanks from the beginning and end of the spreadsheet value, but not from within the value, so “S999   COMP-3” with three blanks between “S999” and “COMP-3” is different to “S999 COMP-3” with one blank.  Simply “COMP-3” is also perfectly OK to Jazz.  You may end up with several codes meaning “DECIMAL(3).  The values do not have to be valid COBOL.

 

Jazz logic for guessing a code is: -

 

1.                  Case doesn’t matter: for example “IF the code starts with ‘GR’” could mean “GR”, “gr”, “Gr”, or “gR”.

2.                  IF the code starts with “GR” THEN value = GROUP.  This means that the name starts a group item

3.                  (ELSEIF) the code is “9” THEN value = PIC ‘9(@Lth)’.  This means that the field is a number in character format.

4.                  (ELSEIF) the code is “X” THEN value = CHAR(@Lth).

5.                  (ELSEIF) the code contains “COMP-3” THEN

a.      Value = “DECIMAL(N1[,N2])

b.      N1 is calculated from the field length, as N1 = Length * 2 – 1.  Note that Jazz uses the field length, not the COBOL picture.

c.      If the code contains “V” then “,N2” is added to Value.  N2 is calculated as the number of characters from “V” to the next blank.  For example, if the code is “S9(5)V99 COMP-3” and the field has length 4 bytes then Jazz will use format “DECIMAL(7,2)”

6.                  (ELSEIF) the code contains “COMP-1” THEN

a.      IF length = 4 THEN Value = FLOAT

b.      ELSE Value = FLOAT LENGTH INVALID

7.                  (ELSEIF) the code contains “COMP-2” THEN

a.      IF length = 8 THEN Value = LONG

b.      ELSE Value = LONG LENGTH INVALID

8.                  (ELSEIF) the code contains “COMP” then

a.      IF the Length = 2 THEN value = SMALLINT

b.      ELSEIF length = 4 THEN Value = INTEGER

c.      ELSEIF length = 8 THEN Value = BIGINT

d.      ELSE value = BINARY LENGTH INVALID

 

If an earlier line has used JazzTypeFromXLS to create a code/format equivalence and then another line uses the same code, but has a different length, then Jazz will add “LENGTH INVALID” to the format.  For example,

 

Jazz guessed  resulting in

        VI-SPENDING-CATEGORY SMALLINT,

The same code was encountered on line #36, but with a different length

 

Jazz added “INVALID LENGTH”, ensuring that this error is reported when the definition is checked in the Jazz workbench.

                MC-CAB-PT-ENTRY SMALLINT INVALID LENGTH,

4e.    Some situations where Jazz gets it wrong

1.                  Length entered was the whole array length when you meant individual field length, or vice versa.  The Jazz form includes: -

This tells Jazz how to interpret the length.

With Field interpretation Jazz thinks that a field of length 24 occurs 12 times, resulting in: -

In fact we should have set the Table option.  While we can correct this to

            DECIMAL(3,1)

in other situations we may have fields calculated as CHAR(@Lth) and there’ll be no message.   If you get something like this and you’re stepping through the spreadsheet you can change the Table/Field option and continue – but don’t forget to check the Jazz definition before this point.  If you’ve unchecked [  ] Step then stop, and restart with correct options.

 

2.                  Jazz may not be able to guess the format: -

 

 

 

This is numeric-character data, which in Jazz is format PIC.  Enter the format as PIC 'S9(11)V9999', or if you prefer PIC 'S9(11)V9(4)'.   Note that Jazz requires quotes around the picture value.

4f.     Other Issues

If various situations are encountered, then Jazz will handle the issues.  For some issue types the fix suggestion can be suppressed: like recognition of Excel Type values you can uncheck these when you have developed some experience with this tool and confidence that it is making the right decisions.

 

Line #6 is contains REDEFINES in the field name, and in Occurrences where Jazz is expecting a number it has the word “REDEFINES”.

 

When Jazz recognizes that the “Name” contains a blank then after sorting out that the name is “NOM-ACH-AMT it attempts to sort it out what follows by looking for “REDEFINES” and “OCCURS”.  In this case it finds “REDEFINES” and saves this as part of the field definition.  It would have put the redefinition object, NOM-ACH-PCT-AMT into the Redefines column had there been such a column.

 

Next Jazz discovers that the Occurs column doesn’t simply contain a number.  It responds with

 

Jazz applies this rule: -

·         If the text starts with “Redef” then it assumes is should be blank.

·         If the text contains “OCCURS nn TIMES” then it removes “Occurs” and “Times” to leave just the number.

 

Click [OK] and Jazz will remove it and continue.

 

Here’s another example.  In this row the Occurs column is not a number: -

 

Jazz responds with

 

As before, click [OK] to continue with the suggested value

 

Another situation where Jazz can recover correctly: in this row

spreadsheet the “Name” contains an INDEXED clause, which is COBOL, not Jazz, and would result in an error: -

 

Jazz responds with

 

Especially with a big spreadsheet replying [OK] to situations that Jazz has already corrected will become tedious, so you’ll probably uncheck

 

Even when these are all unchecked Jazz will continue to report other situations.  For example, here it can’t remove the name prefix from the Redefines object because, without a blank to separate “REDEFINES” and the next word it can’t tell where the redefinition name starts.  In this case we edit the line ourselves before clicking [OK].

 

Sometimes there may be no problem. 

The spreadsheet row contained: -

 

and Jazz has replaced the “.” with a blank.  This is correct, but Jazz cannot be sure, so it displays the amended field name for you to verify.

 

Jazz is also checking for unexpected level changes.  On Line #8 it encounters: -

If this has level value 5, then the preceding line should have defined a GROUP.  Jazz responds

 

and on clicking [Return] the spreadsheet is corrected: -

 

On Line #15 Jazz encounters the reverse error: -

Line #14 is a group, so line #15 should have a higher level number.  Jazz responds with

 

Although the spreadsheet is not changed in this case, CHGOFF-RSN1 is treated as if the level number were 5.

4g     Converting to Jazz Layout

If the checkbox [  ] Show Jazz on SprdSht is checked then as the spreadsheet is checked each COBOL-like line is replaced with a properly formatted Jazz definition.  This checkbox defaults to checked when the Define Data from Spreadsheet function is invoked from Import Conversion Spreadsheet, as it works best when there are all the fields that are standard in conversion spreadsheets.

 

The checkbox can only be changed before the Level 1 line is processed, as the whole spreadsheet should be processed in the same way.  Here are the results of processing spreadsheet VConv2a2.xls with this option: -

 

In the absence of explicit Level and Redefines columns this spreadsheet doesn’t show this information any more, although the information is present in the Jazz definition.  Extra lines have been inserted (10, 21, 21) for END GROUP, which is Jazz’s way of defining level.  Also, you can infer the level numbers from the name indentation.  Names are now shown without prefix.

5.      Review the Jazz Definition

On reaching the end of the spreadsheet it is closed, and Jazz opens a workbench session where the new definition is displayed and checked: -

 

Scroll down to check for messages: there may be spreadsheet errors that Jazz hasn’t repaired.  Here are some examples from various test spreadsheets: -

 

1.                  Spreadsheet line #278 contained “REDEFINESAMBS-NOM-ACH-PCT-AMT”. 

 

This would have been reported but we must have clicked [OK] without really reading the message, resulting in

    NOM-ACH-PCT-AMT DECIMAL(17),

    NOM-ACH-AMT  REDEFINESAMBS-NOM-ACH-PCT-AMT DECIMAL(17),

#002 W "REDEFINESAMBS-NOM-ACH-PCT-AMT" is longer than 25 characters

#021 E "REDEFINESAMBS-NOM-ACH-PCT-AMT" not recognized as a keyword

#029 E 'REDEFINESAMBS-NOM-ACH-PCT-AMT' is invalid here

We can correct it to here to

    NOM-ACH-AMT  REDEFINES NOM-ACH-PCT-AMT DECIMAL(17),

and on clicking [Check] this will become

    NOM-ACH-AMT  REDEFINES AMBS-RECORD.NOM-ACH-PCT-AMT DECIMAL(17),

 

2.                  Line #279 had the same error.  But there’s another error also: line #280 has a lower level number, so line #279 should have been a group, not another DECIMAL(17) field

 

Jazz has produced: -

    NOM-ACH-PCT-R  REDEFINESAMBS-NOM-ACH-PCT-AMT DECIMAL(17),

#002 W "REDEFINESAMBS-NOM-ACH-PCT-AMT" is longer than 25 characters

#021 E "REDEFINESAMBS-NOM-ACH-PCT-AMT" not recognized as a keyword

#029 E 'REDEFINESAMBS-NOM-ACH-PCT-AMT' is invalid here

    FILLER CHAR(5),

    NOM-ACH-PCT DECIMAL(7),

    END GROUP,

#205 E Negative indentation: excessive END statements or similar errors

 

We change line 279 to

    NOM-ACH-PCT-R GROUP REDEFINES NOM-ACH-PCT-AMT,

which, on clicking [Check], becomes

    NOM-ACH-PCT-R GROUP REDEFINES AMBS-RECORD.NOM-ACH-PCT-AMT,

        FILLER CHAR(5),

        NOM-ACH-PCT DECIMAL(7),

        END GROUP,

correcting all these errors.

 

3.                  A spreadsheet contained several fields called “DUMMY”.  Except for the first of these, Jazz reports the duplication error: -

        DUMMY PIC '9(1)',

    #203 S Invalid: Field name DUMMY is duplicated

        DUMMY DECIMAL(7),

    #203 S Invalid: Field name DUMMY is duplicated

        INS-SUSPENDED-PREM DECIMAL(17),

        INS-INTLZED-FLAG PIC '9(1)',

        INS-INTLZED-PROD PIC '9(1)',

        INS-RATE-METHOD CHAR(2),

        DUMMY CHAR(15),

    #203 S Invalid: Field name DUMMY is duplicated

        DUMMY CHAR(1),

    #203 S Invalid: Field name DUMMY is duplicated

These should probably be renamed to “FILLER”.  However [Replace all] may lead to further errors: in at least one case from this spreadsheet one of these DUMMY items is redefined, and a reference to a FILLER field would be an error.

 

4.                  At the end of the spreadsheet further errors may be reported: -

        USER-FILLER CHAR(2),);

    #339 S EX-DATA1 REDEFINES AMBS-RECORD.FI-RESERVED-DATA is invalid: base object is too short

    #339 S EX-EVENT-CODES-R REDEFINES AMBS-RECORD.EX-EVENT-CODES is invalid: base object is too short

    #491 E Too few END GROUP items

These should be corrected.  The incorrect comma after CHAR(2), and message #491, are a consequence of the #2 error where a GROUP definition was missing. 

 

5.                  Depending on the file type we may need to add some more information: -

·                     If this is a type WORK definition then we won’t be able to read and write to it. 

·                     If it has type XIO then we will need to add definitions of the I/O routines before we can invoke these routines.  Until this is done we won’t be able to read and write to it

·                     If we want Jazz to generate JCL accessing this file then as well as the file type we need to add a DSNAME option.

We can make these changes now or later.

 

Once we have corrected any errors in the Jazz definition we should validate the file layout with the option New/Data Conversion/Validate Definition.  This will generate a program that will read one record, print it in FIELDTABLE format, and validate the position of DECIMAL fields by attempting to add zero to them (forcing a data check if they are in the wrong position).