INSFILGEN

INSFILGEN reads an Excel workbook and generates a file of stored-procedure calls to install data for pre-loaded tables. In the workbook you can specify that calls are to be executed or not depending on Preppis macros. Since Excel books are not apt for version control, INSFILGEN also stores the data in .srcdata files, and INSFILGEN can generate Excel books to work in from such a files.

INSFILGEN has a special mode for so-called name tables. This is very AbaSec-specific, and thus this mode is called LANGINSGEN, and is described on its own page. (This mode is not included in the public distribution of AbaPerls.)

Contents:
   Command-Line Syntax
   Background and Examples
   Format of the Excel Book
      Overview
      The Config Sheet
      The MAXLEN Section
      The Definition Sheet
      The Macros Sheet
      The Values Sheet
   SRCDATA and Excel Files
      Format of the SRCDATA file
   How to Retrieve the Example

Command-Line Syntax

To generate an INSERT file and a new .srcdata file

insfilgen file.xls|xlsx

file.xls should be an Excel workbook with layout as detailed in coming sections. From this file, INSFILGEN generates two files: file.ins and file.srcdata. They are always generated in your current folder. If there is an existing file.ins, INSFILGEN maintains any initial comments in that file, but discards any text that follows, be that comments or code. For new INSERT files, INSFILGEN generates a standard SourceSafe header. Any existing .srcdata file is overwritten without notice. Both files are generated with UTF-8 as the encoding.

To generate an Excel book from a .srcdata file:

insfilgen file.srcdata

file.srcdata should be a file previously generated by INSFILGEN from an Excel file. INSFILGEN generates file.xlsx in the same folder as the .srcdata file. If there is an existing file.xlsx, INSFILGEN renames this file to file.xlsx.bak, in case you mistakenly ran INSFILGEN on the .srcdata file when you had intended to run it on your newly modified Excel file to generate a new version of the INSERT file.

See the section SRCDATA and Excel files for more details on which files to work with. There is no provision to generate the INSERT file directly from the .srcdata file.

INSFILGEN accepts one option ‑batchmode. This option is intended for the situation when you run INSFILGEN over many files, maybe to verify that they are OK. When ‑batchmode is in effect, INSFILGEN does not print the regular AbaPerls blurb about version. On the other hand it prints a line with the file name when starting. When working on a .srcdata file in batch mode, INSFILGEN never makes the Excel book visible, and closes the Excel file before terminating. Instead it prints a message that the file was generated successfully.

Background and Examples

Before we proceed to describe the Excel workbook, an explanation of what this tool is good for may be in place. The description of the sheets in the workbook will refer back to the example in this section. (At the end of this document you find the instructions to retrieve to all files involved in this example.)

Assume that in your system that you have two tables functions and functionsettings:

CREATE TABLE functions (functionid   int          NOT NULL,
                        functionname varchar(50)  NOT NULL,
                        extfile      varchar(255) NULL,
                        CONSTRAINT pk_functions PRIMARY KEY (functionid))

CREATE TABLE functionsettings
             (functionid int         NOT NULL,
              setting    varchar(15) NOT NULL,
              intvalue   int         NULL,
              charvalue  char(6)     NULL,
              CONSTRAINT pk_settings PRIMARY KEY (functionid, setting))

ALTER TABLE functionsettings ADD
   CONSTRAINT fk_settings FOREIGN KEY (functionid)
      REFERENCES functions (functionid)

functions defines the GUI functions in your system, and a function will only be available if it is listed in functions. Whether a function is available is customer-specific, depending on what they have paid for. functionsettings defines optional behaviour within a function. A setting could for instance determine whether a certain checkbox is visible or not. Again, this is customer-specific. The setting can be an integer value, a character value, or a combination of both. The column functions.extfile specifies that this particular function is not included in the main executable, but in a separate DLL; this is a custom-independent attribute of the function.

To load these two tables you one would have two INSERT-files, functions.ins and functionsettings.ins, so that they would be loaded when you build an empty database with DBBUILD. To control at which customer sites a function is loaded, you would use Preppis macros.

Your first idea of such a file may be just a bunch of INSERT statements. That works the first time you build the database, but when you run it later on to add new function, you will get a lot of primary-key violations. Of course you could use IF EXISTS to prevent this, but there is still the issue how you propagate changes in functionname or extfile. Or how you delete a function, when it is no longer in use.

Thus, in the end it is better to have a stored procedure do to the job. This SP would look something like this:

CREATE FUNCTION fun_install_sp @functionid  int,
                               @functioname varchar(50),
                               @extsys      varchar(255) = NULL AS
IF NOT EXISTS (SELECT * FROM functions ...)
   INSERT functions ...
ELSE
   UPDATE functions ...

(Follow the link if you wish to see the full text of the procedure.)

functions.ins would contain a number of stored-procedure calls like:

$IFDEF &all_customers
   EXEC fun_install_sp @functionid   = 1,
                       @functionname = 'Add user'
$ENDIF

$IFDEF &cust_HP or &cust_IBM
   EXEC fun_install_sp @functionid   = 191,
                       @functionname = 'Kitchen sink',
                       @extfile      = 'KITCHEN.DLL'
$ENDIF

By using $IFDEF you control which customers that get which functions.

For functionsettings.ins it is a good idea to have an SP to install the setting itself, and this SP is smart enough to not attempt to install a setting if the function is not present in the database. Then you use another SP to set the customer-specific values. The file could look like this:

$REQUIRE functions.ins

EXEC funset_install_sp @functionid = 1,
                       @setting    = 'SHOW_ADDGROUP'

EXEC funset_install_sp @functionid = 191,
                       @setting    = 'MATERIAL',
                       @charvalue  = 'STAINLESS'

$IFDEF &cust_Dell
    funset_set_value_sp @functionid = 1,
                        @setting    = 'SHOW_ADDGROUP',
                        @intvalue   = 1
$ENDIF

$IFDEF &cust_HP
    funset_set_value_sp @functionid = 191,
                        @setting    = 'MATERIAL',
                        @intvalue   = 4711,
                        @charvalue  = 'CHROME'
$ENDIF

The purpose of the $REQUIRE directive, is ensure that we have correct contents in functions before we start. As a consequence of this, functions.ins needs to include a $USEDBY directive for functionsettings.ins.

Let's add one more dimension to this. HP is interested in function 165, Bill of materials, but they want to evaluate it before they buy it. So you agree to install the function in their test environment. One way to address would be to have a macro &cust_HP_test, but this doubles the number of macros to keep track of, or even worse if a customer has more than one test environment. So rather you have a general evaluation macro &EVAL which you use like this:

$IFDEF &cust_Dell or (&cust_HP and &EVAL) or &cust_IBM
   EXEC fun_install_sp @functionid   = 165,
                       @functionname = 'Bill of materials'
$ENDIF

Likewise, if it is only a setting of a certain function they are evaluating:

$IFDEF &cust_HP
    funset_set_value_sp @functionid = 1,
                        @setting    = 'SHOW_ADDGROUP',
         $IFDEF &EVAL
                        @intvalue   = 1
         $ELSE
                        @intvalue   = 0
         $ENDIF
$ENDIF

Here HP gets the SHOW_ADDGROUP setting for evaluation only.

There is one final thing that you need to account for: a function may be taken out of service, either completely or at a certain customer site, for instance because the customer declined after evaluation. Thus your functions.ins would also include these lines.

$IFDEF not (&cust_Dell or (&cust_HP and &EVAL) or &cust_IBM)
   EXEC fun_delete_sp @functionid = 165
$ENDIF

EXEC fun_delete_sp @functionid = 90000

fun_delete_sp would be intelligent to also delete the function from functionsettings. A twist of this is to have a @delflag parameter to fun_install_sp and have the DELETE logic within this SP, and you can see this in funset_install_sp.

You have now seen the framework to handle this situation. But as you may imagine, it's getting difficult to maintain these files manually if there are a lot of functions and/or customers. An Excel workbook gives better overview over which customers that have which functions and which settings. Then you generate the INSERT-files with the calls to the stored procedures with all the $IFDEFs and macros from the Excel sheet with INSFILGEN.

There is one problem with the Excel book though: because it is a binary file is not suitable for version control and even less for merging. (You can save Excel books as XML files, but experience has proven that this format is still very problematic for merging.) For this reason, INSFILGEN generates .srcdata files from the Excel books that you can put under version control, and then you can use INSFILGEN to generate Excel files from the .srcdata files when needed. In the following we first describe the Excel file. In the section SRCDATA and Excel files, we give the details on the .srcdata files, and why you would want to use (or not use) them. No matter if you use .srcdata files or not, you always start with authoring an Excel book.

Format of the Excel Book

Overview

When INSFILGEN reads an Excel book, it looks for these four sheets:

Config	Defines the names of the various stored procedures, key parameters etc. This sheet is mandatory.
Definition	In this mandatory sheet you enter all rows that are to be loaded for the table in question. From this data INSFILGEN generates procedure calls to install and delete the rows.
Macros	In this sheet you define which macros that are to apply to which procedure calls. This sheet is optional. If there is no Macros sheet, INSFILGEN will generate the procedure calls without $IFDEFs.
Values	In this sheet you define the settings that are dependent on macros. This data results in calls similar to funset_set_value_sp in the example in the previous section. This sheet is optional.

You may have other sheets in the book beside these four, but in this case INSFILGEN will warn you that these sheets will not be saved in the .srcdata file.

The sheet names are case-insensitive. The order of the sheets has no importance.

In all four sheets there should be a cell with the word START (that and only that, and all uppercase). The actual data for the sheet starts in the cell to the right of START. This permits you to add comments in the rows above the START row and in the columns to the left of START, including the START column itself. Note, however, note that only text on rows above the START row is saved to the .srcdata file with the exception of the Config sheet. INSFILGEN emits a warning for all extraneous text it finds that it does not save to the .srcdata file.

The Config Sheet

In the Config sheet you define a number of properties as well as maximum lengths for character columns. The sheet has two sections identified by START and MAXLEN. In the START section, the properties appear in the column to the right of the START column, and the values of the properties follow in the columns further to the right. Some properties are scalar, for which INSFILGEN only looks in the first column to the right of the property name. Other properties are lists, for which INSFILGEN reads all columns until it finds an empty cell. The START section cannot have any blank lines; as soon as INSFILGEN sees a blank cell in the properties column, it stops reading properties. The MAXLEN section starts with the word MAXLEN in the START column, and the first column is the name of a character parameter and in the second the maximum permitted length for that parameter. As for START there cannot be any blank lines.

Here is how the Config sheet for functions.xls looks like:

In this example, START, MAXLEN and the property and column names are in bold face. However, this has no importance for INSFILGEN.

Property names are case-insensitive. For all properties that are parameter names, you can omit the leading @.

Here are the properties you can define. All properties are optional, unless otherwise noted.

Scalar properties
Install_sp	Name of the SP that inserts/updates one row into the table. This property is mandatory.
Delete_sp	Name of the SP that deletes one row from the table. If you do not specify Delete_sp, INSFILGEN uses Install_sp also to delete a row. In this case the Install_sp must take a parameter of which the name agrees with the value of the property Delflag.
Value_sp	Name of the SP that sets the value for a row. This property is mandatory if the book contains a Values sheet.
Delflag	This property holds the name of the parameter that controls whether Install_sp inserts/updates a row or deletes it. Even if the Install_sp does not have such a parameter, it must still appear in the Definition sheet. The default value for this parameter is @delflag.
Optionalmacros	When this property is not set, all rows in the Definition sheet, save those that are marked as deleted, must have a corresponding entry in the Macros sheet, if this sheet exists. When set, this requirement is waived. By default, this property is not set, and all entries in Definition must appear in Macros.
Isnametable	If this property is set, the table is a name table, which is handled by LANGINSGEN, a sub-function of INSFILGEN.
List properties
Keys	This mandatory property defines the key parameters for Install_sp, Delete_sp and Value_sp. There is no way to specify that a parameter for the Install_sp is mandatory but not a key, so it might be a good idea to define all mandatory parameters as keys, so that INSFILGEN can alert you if you mistakenly do not provide a value for one of them.
Sortorder	This property defines in which order INSFILGEN should sort the procedures calls. You use this property when a table has a foreign-key relation with itself, so that rows that are referred to by other rows that are loaded first or deleted last.
Sortfactor	This property is mandatory if you have specified Sortorder. There should be one cell in Sortfactor for each cell in Sortorder. A sort factor should be 1 or ‑1 to specify whether sorting is ascending or descending.
Depends_on	List of INSERT-files that this INSERT-file needs to $REQUIRE.
Referred_by	List of INSERT-files that $REQUIRE this file. This property results in one or more $USEDBY directives.
Prelude	List of lines of SQL code to be added first in the INSERT-file (after any $REQUIRE). For instance, in some INSERT-files, you may prefer to delete all existing rows before you load the data. In this case you add a DELETE statement to the Prelude property.
Postlude	List of lines of SQL code to be added last in the INSERT-file.

The MAXLEN Section

The MAXLEN section is not mandatory, however it is strongly recommended that you include one and you will get a warning if it is missing. Once you have a MAXLEN section, all parameters in the Definition sheet with character data must appear in this section, else INSFILGEN will fail.

You can leave out the @ in parameter names.

When INSFILGEN generates the INSERT-file, it checks the string values against the declare string lengths, and if any string exceeds the length, the generation fails.

Obviously the maximum lengths you declare should correspond to those of the parameters in the stored procedures in Install_sp. The purpose is to avoid that you enter data that is truncated when you run the INSERT-file.

The Definition Sheet

On the START row you define all parameters that the Install_sp takes. The parameters defined by the Keys property must appear first (except for comments, see below), and in that order. If you have defined a Sortorder, INSFILGEN checks that these parameters appear, but there is no requirement on their order. Finally, the parameter defined by the Delflag property (default: @delflag) must be present, even if you use a separate Delete_sp and your Install_sp does not have such a parameter. The Delflag parameter can appear anywhere after the keys, but conventionally this is the last parameter. INSFILGEN stops reading the START row as soon as it finds an empty cell. If INSFILGEN encounters a duplicate parameter name, it issues an error message and stops processing the file.

You can omit the @ in parameter names. (To enter a name starting with @ in an Excel cell, you will have to insert a single quote (') as an escape character, to prevent Excel from applying its own meaning of @.) Parameter names are case-sensitive. That is, if a parameter appears in several places in the Excel book it cannot be called @name in one place and @Name in another.

If a cell on the START row has a name starting with #, this column is a comment column. The comments that appear in this column will be included in the generated file. The comment columns may be mixed with the columns for the Keys. However, the first column after START cannot be a comment column. Data in comment columns are saved to the .srcdata file and will thus reappear when the Excel book is regenerated.

On the rows following the START row, you define the data to be loaded to the table. If a cell in the first column right of the START column is blank, that row is skipped. INSFILGEN stops reading rows when it has found five consecutive rows with empty cells in the key column. This permits you to insert dividers in the Excel sheet, as shown in this example, which is the Definition sheet for the functions table.

However, INSFILGEN does not save information about blank lines to the .srcdata file, and nor is any text in other cell on a row with an empty cell in the key column. Any such text causes INSFILGEN to generate a warning to alert you. (With one exception: if there is a text in the START column and the Delflag column changes to 1 on the next row, like in the example above, this does not result in a warning, since INSFILGEN adds text in this place when generating an Excel book from the .srcdata file.)

If you leave a cell blank, INSFILGEN will not include that parameter in the call to the Install_sp, but assume that there is a default value. For string data, you can surround the data with '', but this is optional in most cases. To wit, if INSFILGEN finds a non-numeric value in a column, it will assume that all values for that column are strings. I recommend that you always surround date and time values in single quotes, since else the interplay between INSFILGEN and Excel can produce unexpected results. Since ' first in an Excel cell is an escape character, you actually have to enter two single quotes to get the opening string delimiter.

Restrictions:

INSFILGEN has no understanding of literals of the type binary, money, decimal or float, but will assume that this is string data.
You cannot explicitly specify that a parameter should be set to NULL. INSFILGEN interprets NULL as the string literal 'NULL'.
If you use double quotes to delimit strings. INSFILGEN will surround such strings in single quotes.

The Macros Sheet

On the START row you first list the parameters defined by the Keys property. As in the Definition sheet, these columns must appear first and in the order as they are defined in the property. You can leave out the leading @ in the names. Columns that are not part of Keys must not appear in Macros.

After the parameters in Keys, macro names follow. You can leave out leading & in the macro names; INSFILGEN will add them. We call these macros column macros. If the same macro appears twice, this is an error, and INSFILGEN terminates without generating any files.

Comment columns with a leading # can be added anywhere, except in the column directly following START and they can be interspersed with the macro columns if you are so inclined. INSFILGEN saves data in these columns to the .srcdata file, but the comments will not appear in the INSERT file.

Here is how the Macros sheet for the functions table looks like:

In the rows below the START row follow the same values for the Keys as in the Definition sheet. (It may be a good idea to have formulas that refers to the Definition sheet, e.g. =Definition!C5.) As in Definition, you can leave the first cell blank on a row to insert a divider. When INSFILGEN has found five consecutive blank rows, it stops reading rows. If INSFILGEN finds a duplicate key value, INSFILGEN stops processing the file. As in the Definition sheet, INSFILGEN will warn you when it finds text it will not save to the .srcdata file.

In the cells in columns headed by column macros, three are three possibilities:

The cell is blank or 0. In this case the macro does not apply to the row and the cell does not yield any expression in the .ins file.
The cell is non-blank, non-zero and neither starts with an & followed by a letter. In this case the column macro applies unconditionally to this row and the resulting expression from this cell is the macro itself. Conventionally, the character X is used for this purpose.
The cell starts with an & followed by a letter. In this case, the cell is assumed to contain a macro expression that will be and:ed with the column macro. We call this a cell macro expression (or just cell macro for short). If you go back to the example above, you see the effect of the &EVAL macro.

The call to insert a row will be within an $IFDEF of which the argument is an expression with all the cell expressions or:ed together. There will be a complementary call to delete the row with an $IFDEF that performs a not on the same expression.

All values that appears in Keys columns on the Macros sheet must also appear in the Definition sheet. Unless the property Optionalmacros is set, the reverse is also true. That is, all Keys values in the Definition sheet, must also appear in the Macros sheet with the exception of those with a 1 in Delflag.

When would you use Optionalmacros and when would you not? For a table like functions it is probably a bad idea to enable Optionalmacros. Say that someone adds a new function designed for Dell to the Definition sheet, but forgets to add it to Macros. The result is that all customers get the function, although only Dell pays for it. On the other hand for a table where 95% of the rows are installed at all customers, it might more palatable to only add the odd customer-specific rows to the Macros sheet.

The Values Sheet

The START row of the Values sheet is similar to the START row for the Macros sheet. First you list the parameters of the Keys property. Next comes a column with the name %param and nothing else. After %param, the macro names follow. You can leave out @ in the Keys, and the & in the macros, but you cannot leave out the % in %param. You can insert a comment column with a name starting with # anywhere save for column following the START column. The comments are not included in the INSERT-file, but they are saved to the .srcdata file. As in the Macros sheet, it is an error if the same macro appears twice on the START row.

In the data rows, the values for the Keys in Definition reappear. In %param you specify the name of a non-key parameter defined in the Definition sheet. If you want the generated call to the Value_sp include more than one parameter, you enter the key values for that row twice, with different values in the %param column.

In the macro columns, you specify which value the parameter in the %param column is to have when that macro is set. As in the Definition sheet, a blank cell means that the parameter is omitted. In the INSERT-file there is one call to the Value_sp for the key-value for each macro that has at least one value defined for it.

All values that appear in Keys columns on the Values sheet must also appear in the Definition sheet, but in difference to the Macros sheet, there is no reciprocal requirement. For a table like functionsettings the default value for all settings would be "unavailable" or "standard" and then you specify the luxury stuff on the Values sheet. The combination of Keys and %param must be unique within the sheet.

Here is how the Values sheet for functionsettings look like:

(See these links for the Config and Definition sheet for functionsettings.)

As in Macros, you can use cell-macro expressions, as you can see in the &cust_HP cell for function 1. The format for this is:

&macro # value [# &macro2 # value2 ...] [ # else-value ]

That is, macro expressions interleaved with values, closed with a final value to apply when none of the macro-expressions are true. # separates the different parts. If you leave a value empty or leave it out completely, INSFILGEN will generate DEFAULT as the parameter value. Thus if the parameter does not have a default value, the INSERT-file will fail when run with this combination of macros. Note that if you would like to include # in one of the values, you can't. There is currently not any escape mechanism.

SRCDATA and Excel Files

INSFILGEN offers you two options what is the definitive source for the data in the INSERT files: the .srcdata file or the Excel file,

That is, you can opt to give the .srcdata files a blind eye and use only the Excel files and put these under version control. This has the advantage that you can use the full powers of Excel to annotate the data: free text in cells not read by INSFILGEN, the comment facility in Excel, formatting etc. However, as noted the Excel files are difficult when it comes to version control and not the least for merging. For this reason, INSFILGEN prefers the .srcdata format and it will always generate a .srcdata file, and it will nag you with warnings about text in cells that it does not save to the .srcdata file and the same is true for extraneous sheets in the Excel book. It will also warn you if you attach a comment to a cell anywhere in the Excel book. (That is, you right-click a cell and select "Insert comment".) Other "fancy features" in Excel like borders and shading are entirely ignored by INSFILGEN. That is, it will not check for them, and even less save them to the .srcdata file.

The .srcdata files are XML files. You would normally not edit these files directly with one exception: you are performing a merge operation in your version-control system and there is a merge conflict. Currently, INSFILGEN naïvely assumes that it has generated the XML, and therefore performs no check that's the XML schema is correct. (However, if the file is not valid XML, the standard Perl module that INSFILGEN uses to shred the XML will abort with an error.) In order to help you resolve any merge conflicts manually, an overview of the format is given the below.

When INSFILGEN generates an Excel book from the .srcdata file, the following applies:

The positions of the START and MAXLEN cells are retained.
The font face and font size of the START cell is saved in the .srcdata file and they are applied to the entire sheet. No other font information is saved.
The following items are put in bold face:
- The START and MAXLEN cells.
- The property names in the START section on the Config sheet.
- The parameter names in the MAXLEN section on the Config sheet.
- All header columns on the Definition, Macros and Values sheets.
All other cells are put in plain font.
The column widths are retained. (Row heights are not.) The exception is if you have hidden a column or made it very narrow. In this case INSFILGEN will apply a default width,
All cells are formatted as text, why all values appear left-adjusted even if they are numbers. INSFILGEN also tells Excel not to display warnings for numbers being stored as text. (Since the purpose of the Excel book is to generate an SQL file, all data from this perspective is text.)
On the Config sheet, the properties in the START section will come an order defined by INSFILGEN.
The entries in the MAXLEN section are always in alphabetic order.
On the Definition, Macros and Values sheets the rows are sorted in the order of the key values in alphabetic order. That is, not the order defined by the Sortorder property; this order applies to the INSERT file only.
On the Definition sheet, rows with a value in the Delflag column are sorted in a section after the other rows, separated by a blank line from the rest of the rows, with a informational text in the START column.
Keys with a value in Delflag are not included on the Macros and Values sheet.
On the Definition sheet, the order of the columns is respected; they are not rearranged. The leading @ is always included, even if they were not there originally.
On the Macros and Values sheet, the macros are sorted alphabetic order and the original order is not retained. Any comment column will always be moved to come before the macro columns. The order of the key columns is retained. Leading @ and & are always included.
On the Values sheet, there is a blank row for each new key value to make the sheet easier to work with.
INSFILGEN applies "Freeze Panes" on the Definition, Macros and Values sheets. For the rows, the divider is just below the row with the START cell. For the columns, the split is applied as many columns from the START cell as there are keys defined on the Config sheet for the Definition and Macros sheets, and one more column further afield for the Values sheet, to also include the %param column in the left pane.

Format of the SRCDATA file

This section gives some information about the format of the .srcdata files. You should never try to author such a file on your own, nor try to make changes directly to the file. However, you may need to merge them and in this case, it may help to have a knowledge of the format.

The format is XML, and it could surely be described in a formal way with an XSD, but both the reader and the author is likely to be more helped by an informal description. Here is an outline of the outermost elements:

<INSFILGEN SRCDATA_version="1.0">
  <Config Sort="1">
  </Config>
  <Definition Sort="2">
  </Definition>
  <Macros Sort="3">
  </Macros>
  <Values Sort="4">
  </Values>
</INSFILGEN>

That is, the root tag is always INSFILGEN, and it has one attribute which is the version number of the format. (Currently 1.0 is the only format there is and has ever been.) Next there are one tag per sheet in the book, and the Sort attribute specifies the order the sheets are to appear in; the order is always as given above.

Config

The Config element has for subelements as in this example (from functionsettings.srcdata):

 <ColWidths>
   <Entry Sort="1" Width="8.43" />
   <Entry Sort="2" Width="11.29" />
   ...
</ColWidths>
<Freetext>
   <Entry Sort="   1!   1" col="1" row="1">Configuration for functionssettings.xls</Entry>
</Freetext>
<MAXLEN col="1" row="8">
   <Entry Sort="   1" Colhead="@charvalue" Value="6" />
   <Entry Sort="   2" Colhead="@setting" Value="15" />
</MAXLEN>
<START Font="Arial" FontSize="10" Width="8.43" col="1" 
       install_sp="funset_install_sp" row="3" value_sp="funset_set_value_sp">
   <depends_on Sort="   1" Value="functions.ins" />
   <keys Sort="   1" Value="@functionid" />
   <keys Sort="   2" Value="@setting" />
</START>

First there is a ColWidth element. This element defines the column width for the first 15 columns on the sheet. Next comes the Freetext element. This element holds free text that is not part of the START and MAXLEN sections and which does not affect how the INSERT file is generated. Each entry holds which cell the text goes into and the text itself. The Sort attribute, which reappears all through the document, defines how the value is sorted in the XML file, to make sure that rows don't change place without reason, as this would make merging more difficult.

Next element is MAXLEN and the attributes col and row defines the cell where to write MAXLEN. The entries are the entries in the MAXLEN section in alphabetic order. The START element has all information that is in the START section in the Config sheet. The scalar properties appear as attributes together with row and col which gives the position for the START element and the Font and FontSize attributes which specifies the type face for the sheet. The attributes always come in alphabetic order. Only properties explicitly listed in the Excel book are present. The list properties follow as sub-elements, with one element for each cell. The order is always alphabetic. Again, only the explicitly listed attributes are present.

Definition

Here is an example of a Definition element, again from functionsettings.srcdata:

<Definition Sort="2">
    <Datarows>
      <Entry Sort="0!1!SHOW_ADDGROUP">
        <Cell Sort="   2" Colhead="@functionid">1</Cell>
        <Cell Sort="   3" Colhead="#name">Add customers</Cell>
        <Cell Sort="   4" Colhead="@setting">SHOW_ADDGROUP</Cell>
      </Entry>
      <Entry Sort="0!191!MATERIAL">
        <Cell Sort="   2" Colhead="@functionid">191</Cell>
        <Cell Sort="   3" Colhead="#name">Kitchen sink</Cell>
        <Cell Sort="   4" Colhead="@setting">MATERIAL</Cell>
      </Entry>
    </Datarows>
    <Freetext>
      <Entry Sort="   1!   1" col="1" row="1">Here we define the settings for the various functions</Entry>
    </Freetext>
    <Headers>
      <Entry Sort="   2" Colhead="@functionid" Width="11.14" />
      <Entry Sort="   3" Colhead="#name" Width="13" />
      <Entry Sort="   4" Colhead="@setting" Str="1" Width="18" />
      <Entry Sort="   5" Colhead="@intvalue" Width="8.43" />
      <Entry Sort="   6" Colhead="@charvalue" Width="8.43" />
      <Entry Sort="   7" Colhead="@delflag" Width="8.43" />
    </Headers>
    <START Font="Arial" FontSize="10" Width="8.43" col="1" row="3" />
</Definition>

The Datarows element has a number of Entry elements, one for each call to the Install_sp. The Sort attribute is a concatenated string of the key values, with the value of Delflag parameter first in the string. Each Entry element has as number of Cell elements, one for each cell on the row that actually has a value. The Colhead attribute specifies which column the value goes into. (This is always a value define in the Headers element.)

Freetext is the same as in the Config element.

Headers define the text on the START row, that is the column headers. They are written in the order defined in the file. Colhead is the string to write to the cell and this is the value referred to from the Datarows/Entry/Cell elements. Width is the width for the column in Excel. Str relates whether INSFILGEN has observed any non-numeric value for the parameter in the sheet.

The START element holds the position for the START cell, the width of the START column and font information for the entire sheet.

Macros and Values

These elements have the same schema as the Definition element. Only a few things are worth noting. The Sort attribute for entries in the Headers element looks like in this example (from functions.srcdata):

<Headers>
  <Entry Sort="   2" Colhead="@functionid" Width="11.14" />
  <Entry Sort="   3" Colhead="@functionname" Str="1" Width="13.57" />
  <Entry Sort="9999!&amp;all_customers" Colhead="&amp;all_customers" Width="14.14" />
  <Entry Sort="9999!&amp;cust_Dell" Colhead="&amp;cust_Dell" Width="9.57" />
  <Entry Sort="9999!&amp;cust_HP" Colhead="&amp;cust_HP" Width="8.71" />
  <Entry Sort="9999!&amp;cust_IBM" Colhead="&amp;cust_IBM" Width="9.57" />
</Headers>

That is, the sort value always start with 9999 to ensure that they macros are sorted last after key values, comment and %param columns.

In the Values element, the name of the parameter in the %param column is added to the Sort attribute to get a unique sort key.

How to Retrieve the Example

If you wish to study the example closer, you can look in this directory where you find an AbaPerls SQL directory structure with the two tables functions and functionsettings, the stored procedures fun_install_sp, fun_install_sp, funset_install_sp and funset_set_value_sp as well as the complete Excel books for the two tables and the generated INSERT files and .srcdata files. You can also get the files from this zip archive.

If you wish to build the database, extract the zip archive, and in the directory which you extracted, run

dbbuild -subsys -d testdb -Macro="&all_customers=1"

Add ‑S and/or ‑P to specify server and password if necessary. You can replace &all_customers with for instance &cust_HP, and compare the contents of the two tables in the two cases.