IBM i: The SQL Way · #20

Generate Excel, CSV, ODS, and Text Files from IBM i SQL

Use SYSTOOLS.GENERATE_SPREADSHEET to export an IBM i table or SQL query to CSV, XLSX, XLS, ODS, or text files in the IFS, including current update and positioning options.

Saurabh Arora

IBM i Solution Architect | Technical Lead | Modernization & Integration Specialist

Published July 25, 20265 min read

Related native optionACS Data Transfer or CLDownload

IBM i can generate a PC-compatible spreadsheet directly from a database file or SQL query. SYSTOOLS.GENERATE_SPREADSHEET writes CSV, text, ODS, XLS, or XLSX output to the IFS by using the CLDownload support shipped with IBM i Access Client Solutions.

The scalar function is:

SYSTOOLS.GENERATE_SPREADSHEET

A successful call returns:

A failed call returns:

-1

Generate a CSV file from a database file

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME      => '/home/reports/customers.csv',
    LIBRARY_NAME   => 'MYLIB',
    FILE_NAME      => 'CUSTOMER',
    SPREADSHEET_TYPE => 'csv',
    COLUMN_HEADINGS  => 'COLUMN'
);

Supported output types are:

csv
ods
txt
xls
xlsx

The type values must be supplied in lowercase.

Generate an XLSX file from a query

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/open_orders.xlsx',
    SPREADSHEET_QUERY =>
        'SELECT ORDER_NUMBER,
                CUSTOMER_NUMBER,
                ORDER_DATE,
                ORDER_TOTAL
           FROM MYLIB.ORDERS
          WHERE ORDER_STATUS = ''OPEN''
          ORDER BY ORDER_DATE',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'COLUMN',
    SHEET_NAME =>
        'Open Orders'
);

The query is passed as a character string.

Single quotes inside the SQL text must be doubled.

Column-heading options

NONE
COLUMN
LABEL

NONE

No heading row is generated.

This is the default.

COLUMN

SQL column names are used.

LABEL

Column labels are used when present. The column name is used when no label exists.

Example:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/customer_labels.xlsx',
    LIBRARY_NAME =>
        'MYLIB',
    FILE_NAME =>
        'CUSTOMER',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'LABEL'
);

Use a query stored in the IFS

For a long or reusable SQL statement, store the query in a UTF-8 IFS file:

/home/reports/sql/monthly_sales.sql

Then run:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/monthly_sales.xlsx',
    SPREADSHEET_QUERY_IFS =>
        '/home/reports/sql/monthly_sales.sql',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'COLUMN',
    SHEET_NAME =>
        'Monthly Sales'
);

Requirements for SPREADSHEET_QUERY_IFS include:

The file must use CCSID 1208 / UTF-8
The SQL statement cannot end with a semicolon
Referenced database objects must be fully qualified
QTEMP objects cannot be referenced

Storing the query separately is useful when:

it is longer than the inline query limit
it is source controlled
several jobs use the same report definition
the query needs independent review

Replace an existing output file

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/open_orders.xlsx',
    SPREADSHEET_QUERY =>
        'SELECT * FROM MYLIB.OPEN_ORDERS',
    SPREADSHEET_TYPE =>
        'xlsx',
    OVERWRITE =>
        'REPLACE'
);

REPLACE is the default.

It replaces the current file contents.

Update an existing workbook

For XLS or XLSX output, current support can write into an existing workbook without replacing its formatting:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/monthly_template.xlsx',
    SPREADSHEET_QUERY =>
        'SELECT REGION,
                SALES_AMOUNT
           FROM MYLIB.MONTHLY_SALES
          ORDER BY REGION',
    SPREADSHEET_TYPE =>
        'xlsx',
    OVERWRITE =>
        'UPDATE',
    STARTING_SHEET =>
        2,
    STARTING_ROW =>
        5,
    STARTING_COLUMN =>
        'B'
);

UPDATE is allowed for:

xls
xlsx

The starting-location parameters can be used with:

xls
xlsx
ods

but UPDATE itself is limited to XLS and XLSX.

Starting sheet, row, and column

STARTING_SHEET

An integer identifying the starting worksheet.

The default is the first sheet.

STARTING_ROW

An integer identifying the starting row.

The default is the first row.

STARTING_COLUMN

A spreadsheet column from:

A through ZZZ

The default is the first column.

These options are useful for:

existing report templates
cover sheets
titles and instructions above the data
multiple report sections in one workbook
formulas or charts already present in the file

Assign a sheet name

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/customer_export.ods',
    LIBRARY_NAME =>
        'MYLIB',
    FILE_NAME =>
        'CUSTOMER',
    SPREADSHEET_TYPE =>
        'ods',
    SHEET_NAME =>
        'Customer Data'
);

SHEET_NAME applies to:

xls
xlsx
ods

Generate a text file

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/inventory.txt',
    SPREADSHEET_QUERY =>
        'SELECT ITEM_NUMBER,
                DESCRIPTION,
                QUANTITY_ON_HAND
           FROM MYLIB.INVENTORY
          ORDER BY ITEM_NUMBER',
    SPREADSHEET_TYPE =>
        'txt',
    COLUMN_HEADINGS =>
        'COLUMN'
);

For machine-to-machine integration, verify whether the receiving system expects:

CSV
tab-delimited text
a specific character encoding
quoted values
a fixed schema
a line-ending convention

A spreadsheet-oriented export is not automatically an API contract.

LOB columns are not supported directly

The underlying CLDownload support does not directly support:

CLOB
BLOB
DBCLOB
XML

Cast a LOB to a supported non-LOB string type when the value is small enough:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/messages.xlsx',
    SPREADSHEET_QUERY =>
        'SELECT MESSAGE_ID,
                CAST(MESSAGE_TEXT AS VARCHAR(32000))
           FROM MYLIB.MESSAGE_STORE',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'COLUMN'
);

The cast can truncate data if the selected type is too small.

For large documents or binary objects, export the object separately and place an IFS path or identifier in the spreadsheet.

Object qualification requirements

For SPREADSHEET_QUERY and SPREADSHEET_QUERY_IFS:

Objects must be fully qualified
QTEMP objects are not supported

For direct file export:

QTEMP
*LIBL
*CURLIB

are not supported as the library value.

The database file must be in *SYSBAS.

IFS authority

The execution profile needs authority to:

traverse every directory in the output path
create or replace the output file
read the IFS query file when one is used
read all referenced database objects
run the underlying ACS CLDownload support

Create report directories with deliberate ownership and authority.

Avoid writing sensitive reports into:

/tmp

or another broadly accessible directory.

The ACS dependency

The function uses:

/QIBM/proddata/Access/ACS/Base/acsbundle.jar

The ACS JAR is delivered through the IBM HTTP Server for i PTF Group.

A failure can result from an outdated or missing supporting ACS level even when the Db2 Group PTF is current.

Include the HTTP Group PTF in the maintenance review.

QJVAEXEC jobs and daemon behavior

The ACS support can start a Java service daemon job visible as:

QJVAEXEC

That job remaining active can be normal.

Use:

KILL_DAEMON => 'YES'

when the daemon threads should be ended after the request:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/home/reports/daily.csv',
    LIBRARY_NAME =>
        'MYLIB',
    FILE_NAME =>
        'DAILYDATA',
    SPREADSHEET_TYPE =>
        'csv',
    KILL_DAEMON =>
        'YES'
);

The default is:

NO

Keeping the daemon can improve repeated-use performance.

Ending it may be appropriate for isolated or controlled batch processing.

Diagnostic output

When no existing override for STDOUT is present, CLDownload output is redirected to:

QTEMP/QGENSPREAD

If the function returns -1, inspect that file in the same job.

An existing STDOUT override prevents this redirection.

Because QTEMP is job-specific, the diagnostic file must be reviewed from the job that ran GENERATE_SPREADSHEET.

MFA limitation

IBM documents two important restrictions:

The function cannot be invoked when a profile’s TOTP interval is expired.
A batch or scheduled job profile cannot use *TOTP as its authentication method.

This affects report jobs introduced into an IBM i 7.6 MFA environment.

Do not weaken MFA controls to make a batch report work. Use an approved non-interactive service profile and the organization’s security design.

Security and privacy

A spreadsheet can move database content into a location that is easier to copy, email, download, or share.

Before generating a report, review:

row and column access control
masked columns
personally identifiable information
payment or health information
output-directory authority
retention
encryption
file-transfer destination
deletion after use
audit requirements

A successful export can still be a data-loss incident when the output is written to the wrong directory.

Automation example

BEGIN
    DECLARE EXPORT_RESULT INTEGER;

    SET EXPORT_RESULT =
        SYSTOOLS.GENERATE_SPREADSHEET(
            PATH_NAME =>
                '/home/reports/daily_orders.xlsx',
            SPREADSHEET_QUERY =>
                'SELECT ORDER_NUMBER,
                        CUSTOMER_NUMBER,
                        ORDER_DATE,
                        ORDER_TOTAL
                   FROM MYLIB.ORDERS
                  WHERE ORDER_DATE = CURRENT_DATE',
            SPREADSHEET_TYPE =>
                'xlsx',
            COLUMN_HEADINGS =>
                'COLUMN',
            SHEET_NAME =>
                'Daily Orders',
            KILL_DAEMON =>
                'YES'
        );

    IF EXPORT_RESULT <> 1 THEN
        SIGNAL SQLSTATE '75001'
           SET MESSAGE_TEXT =
               'Daily order spreadsheet was not generated';
    END IF;
END;

A production process should also capture:

output path
row count
start and end timestamp
execution job
report definition version
delivery outcome
cleanup outcome

Release and PTF requirement

GENERATE_SPREADSHEET was introduced and enhanced across several IBM i releases and PTF levels.

The current service matrix shows additional enhancements at:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

Some major positioning and IFS-query capabilities were introduced at the immediately preceding levels:

IBM i 7.6 — SF99960 Level 2
IBM i 7.5 — SF99950 Level 11

Use the documentation that matches the installed PTF level before relying on a particular parameter.

Native tools versus SQL

Use ACS Data Transfer when:

a person is interactively building an export
the result needs immediate review
filters and formats are selected graphically

Use CLDownload when:

a command-line process already exists
ACS plug-in options are managed directly

Use GENERATE_SPREADSHEET when:

a stored procedure creates the report
the query is controlled in SQL
a scheduler runs the export
report creation belongs in an SQL workflow
return status must be handled in SQL

A practical production workflow

1. Define and review the fully qualified SQL query.
2. Test the expected row count.
3. Choose the output format.
4. Create a protected IFS report directory.
5. Decide whether the file is replaced or updated.
6. Choose sheet, row, column, and sheet name.
7. Run under an approved service profile.
8. Capture the integer return value.
9. Inspect QTEMP/QGENSPREAD after failures.
10. Deliver, retain, and delete the file according to policy.

Final takeaway

SYSTOOLS.GENERATE_SPREADSHEET turns an IBM i table or query into a familiar file format without requiring a separate desktop export step.

The current options make it practical for reusable templates and scheduled reporting—but the output file must be treated with the same care as the database data from which it was created.

References

IBM documentation and support references used for this entry.

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.