Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
Introduction
 
XLSCONVERT utility
XLSTABLE utility
 xlstable2 utility
Some CGI programs
 
 PDF of this tutorial
 
 
 Download
 
 

 
XLSTABLE2 utility
Convert an Excel XLS/XLSX spreadsheet to a db file (using CPYFRMIMPF command)
by Giovanni B. Perotti (Italy)
  1. Command XLSTABLE2
  2. Spreadsheets containing formulas
  3. How to upgrade the physical file record format

1- Command XLSTABLE2

Command XLPARSE2/XLSTABLE2 is apparently identical to command XLPARSE2/XLSTABLE:

  • it has the same objective (generate a physical file from an Excel spreadsheet) as command XLSTABLE
  • it has the same job requirements as command XLSTABLE
  • it has the same parameters as command XLSTABLE
  • it performs significantly faster than command XLSTABLE on small spreadsheets, about the same speed on large spreadsheets

The difference stays in the way it loads the data into the generated physical file:

  • Both commands rely on command XLPARSE2/XLSCONVERT to parse the spreadsheet and generate in library QTEMP a database file (XLSOUTF) containing the spreadsheet data, but
  • while XLSTABLE creates an ILE_RPG program in library QTEMP, then calls it to load the data into the physical file,
  • XLSTABLE uses system command CPYFRMIMPF to load the data into the physical file.

The advantages of using XLSTABLE2 instead of XLSTABLE are:

  1. You no longer need the ILE RPG IV compiler to run command XLSTABLE2. You may run XLSTABLE2 on a computer without compilers:
    1. Install XLPARSE2 on a computer with ILE RPG IV compiler
    2. Save library XLPARSE2
    3. Restore library XLPARSE2 on the computer without compilers
    4. Run command XLPARSE2/INSTALL
  2. You may very easily change the record format of the XLSTABLE2 generated physical file to better fit your requirements. See the next topic.


2- Spreadsheets containing formulas

Values resulting from formulas may have conversion problems. A simple way to bypass these problems is documented in this topic.

3- How to upgrade the physical file record format

When you originally create or re-create (parameter CRTFILE(*YES)) a physical file from a spreadsheet using command XLSTABLE2, the fields of its record format are defined in the same way used with command XLSTABLE, that is:

  • Field names are assigned as FLD1, FLD2, etc.
  • Spreadsheet columns containing only numeric data generate fields having Zoned Decimal data type, 30 digits and 6 decimal positions (30S 6)
  • Other columns generate fields with Character data type (A) and a length large enough to accomodate the largest case.
  • The DDS source member used to generate the physical (PHY) file has the same name of the PHY file and can be found in source file QDDSSRC in the library of the PHY file.

Once the PHY file has been generated, you may re-create it from its DDS after changing them according to the following rules:

  • You may assign the field names you like
  • You cannot change the field sequence
  • You cannot change the data type A of the character fields, but you can increase their lengths
  • With numeric (Z) fields you can:
    • change data type Zoned Decimal (S) to another numeric data type, change the number of digits and the number of decimal positions, OR
    • change the data type to Character (A) and provide its appropriate length
  • You can add key fields

Once the PHY file has been re-created, you may run again your XLSTABLE2 command against it by specifying CRTFILE(*NO).